How to improve performance on query

Transact-SQL
,
1

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

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 :

display execution plan

2

1 Cluster FM on ( DK_Feature, Z_FeatureID )
For best performance, be sure to create the clus index before loading the table.

2 Add an index on DUFP keyed by ( PartNumber ) INCLUDE ( FeatureName, Value, ZpartID )

Then try the query again.

3

I do above it take 25 second it still more time for only one part and 10 row returned
first take 50 second and now 25 second
but I think there are some thing can do to get less time

4

hi

one way is to keep eliminating joins ..
which join when you eliminate .. the query runs really fast .. that join ..do it seperately

+++++++++++++++++++++++++++++++++++++
example
select * from a join b join c join d

select * into #temp from b join c ... here b join c is the problem join

then .. select * from #temp join a join d
++++++++++++++++++++++++++++++++++++

another thing is to filter the data

example
select * from a join b join c where b.name = 'SAM'

do this into temp table
select * into #temp from b where b.name = SAM'

then join
select * from #temp join a join c

Reason ..
in this select * from a join b join c where b.name = 'SAM' ... there will be 100000 rows to join

but in this
select * from #temp join a join c there will be 100 rows to join

5

I'm sorry for misunderstand
can you clear above answer please
I need to understand above post please
so can you write more details please

6

this is the statement ..

select * from a
join b
join c
join d

commenting join d now try to run
select * from a
join b
join c
-- join d

commenting join c now try to run .. if it runs very fast ,, then join c is the problem join
select * from a
join b
-- join c
-- join d

7

thank you very much