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

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar6y ago
I suggest you to use cte
Let me give you small example

Suppose there're two tables Purchase and users if we try to do this then it takes some time
SELECT "purchase"."id"
FROM "purchase"
INNER JOIN "user" ON ("purchase"."user_id" = "user"."id")
WHERE "user"."account_id" IN
    (SELECT generate_series(1,1000));
but in place of this if we try
WITH user_ids AS
  (SELECT id
   FROM user
   WHERE account_id IN
       (SELECT generate_series(1,1000)))
SELECT purchase.id
FROM purchase
WHERE user_id IN
    (SELECT id
     FROM user_ids);
It will take very less amount of time
Thank you
Daniel Zhou6y ago
Bro,maybe too much left join,try using more #table to split the relation,then you can find part which cause the trouble more quickly than now.