2
Answers

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

ahmed elbarbary

ahmed elbarbary
4y
530
1
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

  1. if object_id('tempdb..#getDeliveryConfiguration') is not null drop table #getDeliveryConfiguration  
  2. if object_id('tempdb..#finalTable') is not null drop table #finalTable    
  3.  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)       
  4. INNER join Nop_AcceptedValuesOption AVO  with(nolock) on AVO.AcceptedValuesOptionID=DkFeatureId      
  5. where dvc.ModuleId is not null    
  6. Declare @companyName nvarchar(200) =      
  7. N'AVX Corporation'    
  8. declare @partNumber nvarchar(200)=      
  9. N'CDR31BP101BJUM'    
  10. 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       
  11. ,DDD.ColumnName [Flat Feature],DUFP.FeatureName [Sourcing Feature],NAVO.Name [Flat Value],DUFP.Value [Sourcing value]      
  12. ,C.CompanyName,p.PartNumber,dd.DataDefinition [PL]      
  13. , isnull(AVOS.Name,'') ApprovalStatus       
  14. ,isnull(rl.local_url,'') [local url],isnull( l.local_url,'') [DataSheet] ,isnull(NAVOUrl.Name ,'') [Data Sheet Type]      
  15. ,isnull(NV.DKValue,'') [DK Value],FM.StatusId,FM.SplitFlag,FM.DkFeatureId,FM.separator,FM.separatororder      
  16. --test      
  17. ,NPP.PartID , DUFP.ZpartID,starformat,Endformat into #finalTable    
  18. from #getDeliveryConfiguration FM   with(nolock)           
  19. join [Excel_DK].dbo.Excel_DK DUFP with(nolock)  on DUFP.FeatureName =FM.DK_Feature       
  20. join [Z2DataCore].[Parts].[Nop_Part] p with(nolock) on DUFP.ZpartID =p.PartID      
  21. join [Z2DataCompanyManagement].[CompanyManagers].[Company] C with(nolock) on p.CompanyID = C.CompanyID     
  22. left join [Z2DataCore].[parts].[Nop_PartParamtric] NPP with(nolock) on NPP.PartID = DUFP.ZpartID       
  23. left join [Z2DataCore].[dbo].[Core_DataDefinitionDetails] DDD with(nolock) on FM.Z_FeatureID=DDD.ColumnNumber    
  24.  join [Z2DataCore].[Parts].[Nop_PartParamtricAttribute] NPPA    with(nolock) on NPP.[PartParamtricID] =NPPA. [PartParamtricID]  and DDD.ColumnNumber=   NPPA.[Key]      
  25. left join [Z2DataCore].[dbo].[Nop_AcceptedValuesOption] NAVO    with(nolock) on NPPA.Value =NAVO. AcceptedValuesOptionID       
  26. left join [Z2DataCore].[dbo].Nop_AcceptedValuesOption AVOS with(nolock) on AVOs.AcceptedValuesOptionID= NPPA.ApprovalID/*[ApprovalStatus]*/ and AVOS.AcceptedValuesID=2941      
  27. LEFT JOIN Z2URLSystem.zsrc.Local_URL l with(nolock) ON l.rec_id = NPPA.SourceURLID    
  28. left JOIN [Z2DataCore].Parts.Nop_PartsFamilyAttribute fa with(nolock) ON fa.PartFamilyID = p.PartsFamilyID AND fa.[Key] = 20281007    
  29. left JOIN [Z2DataCore].dbo.Core_DataDefinition dd with(nolock) ON dd.ZproductCategoryID = fa.Value
  30. LEFT JOIN [Z2DataCore].Parts.Nop_PartsFamilyAttribute Nfa with(nolock) ON Nfa.PartFamilyID = p.PartsFamilyID AND Nfa.[Key]=1400040081    
  31. LEFT JOIN Z2URLSystem.zsrc.Revision r with(nolock) ON r.rec_id = Nfa.Value      
  32. LEFT JOIN Z2URLSystem.zsrc.Local_URL rl with(nolock) ON rl.rec_id = r.local_id      
  33. Left join [Z2DataCore].[dbo].[Nop_AcceptedValuesOption] NAVOUrl with(nolock) on NPPA.SourceURLType =NAVOUrl. AcceptedValuesOptionID      
  34.  left join [Z2DataCore].dbo.Core_DataDefinitiondeTails CDD with(nolock) on CDD.ColumnNumber=NPPA.[Key] and CDD.FeatureType in(2044,2043)      
  35. left outer join [Z2DataCore].dbo.Core_DataDefinition CD with(nolock) on CD.ID=CDD.DataDefinitionID
  36. left join [Z2DataCore].[dbo].[NormalizationValue] NV with(nolock) on NAVO.AcceptedValuesOptionID=NV.AcceptedValuesOptionId and NV.ProductID=CD.ZNumber      
  37.    where C.CompanyName=@companyName And DUFP.PartNumber=@partNumber     
  38. order by DUFP.FeatureName 
  1. and execution plan as below :  
  2.   
  3. https://www.brentozar.com/pastetheplan/?id=HyclwfSev
Answers (2)
Next Recommended Forum