SQLTeam.com | Weblogs | Forums

How to improve performance on query

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
,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

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.

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


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

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

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

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

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

thank you very much