When run query for only part it take too much time so How to solve thi

Question

When run query for only part it take too much time so How to solve this issue ?

I work on sql server 2012 query I face issue : when run query return 10 rows for only one part

it take 50 second I try to run it in another time may be pc have more load

but it take same time 50 second to return 10 rows for only one part .

I have may be 10000 part and records may be 15 million so I need to enhance performance to be best

so what I do to make query run in small time

this is my query

if object_id('tempdb..#getDeliveryConfiguration') is not null drop table #getDeliveryConfiguration
if object_id('tempdb..#finalTable') is not null drop table #finalTable
 select dvc.Z2FeatureID as Z_FeatureID, AVO.acceptedvaluesoptionid as DkFeatureId,AVO.Name as DK_Feature,dvc.ModuleId,dvc.SplitFlag,dvc.separator,dvc.separatororder,dvc.starFormat,EndFormat,dvc.statusid,dvc.DkFeatureName2 into #getDeliveryConfiguration  from      [Parts].[DeliveryModuleConfiguration] dvc with(nolock)
INNER join Nop_AcceptedValuesOption AVO  with(nolock) on AVO.AcceptedValuesOptionID=DkFeatureId
where dvc.ModuleId is not null
Declare @companyName nvarchar(200) =
N'AVX Corporation'
declare @partNumber nvarchar(200)=
N'CDR31BP101BJUM'
Select  distinct top 100 [Is Match]= case when isnull(NAVO.Name ,'') = DUFP.Value or (isnull(NAVO.Name ,'') = N'N/A' And DUFP.Value =N'-') then cast(1 as bit) else cast(0 as bit) end
,DDD.ColumnName [Flat Feature],DUFP.FeatureName [Sourcing Feature],NAVO.Name [Flat Value],DUFP.Value [Sourcing value]
,C.CompanyName,p.PartNumber,dd.DataDefinition [PL]
, isnull(AVOS.Name,'') ApprovalStatus
,isnull(rl.local_url,'') [local url],isnull( l.local_url,'') [DataSheet] ,isnull(NAVOUrl.Name ,'') [Data Sheet Type]
,isnull(NV.DKValue,'') [DK Value],FM.StatusId,FM.SplitFlag,FM.DkFeatureId,FM.separator,FM.separatororder
--test
,NPP.PartID , DUFP.ZpartID,starformat,Endformat into #finalTable
from #getDeliveryConfiguration FM   with(nolock)
join [Excel_DK].dbo.Excel_DK DUFP with(nolock)  on DUFP.FeatureName =FM.DK_Feature
join [Z2DataCore].[Parts].[Nop_Part] p with(nolock) on DUFP.ZpartID =p.PartID
join [Z2DataCompanyManagement].[CompanyManagers].[Company] C with(nolock) on p.CompanyID = C.CompanyID
left join [Z2DataCore].[parts].[Nop_PartParamtric] NPP with(nolock) on NPP.PartID = DUFP.ZpartID
left join [Z2DataCore].[dbo].[Core_DataDefinitionDetails] DDD with(nolock) on FM.Z_FeatureID=DDD.ColumnNumber
 join [Z2DataCore].[Parts].[Nop_PartParamtricAttribute] NPPA    with(nolock) on NPP.[PartParamtricID] =NPPA. [PartParamtricID]  and DDD.ColumnNumber=   NPPA.[Key]
left join [Z2DataCore].[dbo].[Nop_AcceptedValuesOption] NAVO    with(nolock) on NPPA.Value =NAVO. AcceptedValuesOptionID
left join [Z2DataCore].[dbo].Nop_AcceptedValuesOption AVOS with(nolock) on AVOs.AcceptedValuesOptionID= NPPA.ApprovalID/*[ApprovalStatus]*/ and AVOS.AcceptedValuesID=2941
LEFT JOIN Z2URLSystem.zsrc.Local_URL l with(nolock) ON l.rec_id = NPPA.SourceURLID
left JOIN [Z2DataCore].Parts.Nop_PartsFamilyAttribute fa with(nolock) ON fa.PartFamilyID = p.PartsFamilyID AND fa.[Key] = 20281007
left JOIN [Z2DataCore].dbo.Core_DataDefinition dd with(nolock) ON dd.ZproductCategoryID = fa.Value
LEFT JOIN [Z2DataCore].Parts.Nop_PartsFamilyAttribute Nfa with(nolock) ON Nfa.PartFamilyID = p.PartsFamilyID AND Nfa.[Key]=1400040081
LEFT JOIN Z2URLSystem.zsrc.Revision r with(nolock) ON r.rec_id = Nfa.Value
LEFT JOIN Z2URLSystem.zsrc.Local_URL rl with(nolock) ON rl.rec_id = r.local_id
Left join [Z2DataCore].[dbo].[Nop_AcceptedValuesOption] NAVOUrl with(nolock) on NPPA.SourceURLType =NAVOUrl. AcceptedValuesOptionID
 left join [Z2DataCore].dbo.Core_DataDefinitiondeTails CDD with(nolock) on CDD.ColumnNumber=NPPA.[Key] and CDD.FeatureType in(2044,2043)
left outer join [Z2DataCore].dbo.Core_DataDefinition CD with(nolock) on CD.ID=CDD.DataDefinitionID
left join [Z2DataCore].[dbo].[NormalizationValue] NV with(nolock) on NAVO.AcceptedValuesOptionID=NV.AcceptedValuesOptionId and NV.ProductID=CD.ZNumber
   where C.CompanyName=@companyName And DUFP.PartNumber=@partNumber
order by DUFP.FeatureName

and execution plan as below :

https://www.brentozar.com/pastetheplan/?id=HyclwfSev