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
Paste The Plan - Brent Ozar Unlimited®[
^]
EDIT: OPs query copied from link above
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 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
What I have tried:
CREATE NONCLUSTERED INDEX index1
ON [dbo].[Excel_DK] ([PartNumber]) INCLUDE ([ZPartId],[FeatureName],[Value])