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 FMwith(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] NPPAwith(nolock) on NPP.[PartParamtricID] =NPPA. [PartParamtricID]and DDD.ColumnNumber=NPPA.[Key]
left join [Z2DataCore].[dbo].[Nop_AcceptedValuesOption] NAVOwith(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] NAVOUrlwith(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.FeatureNamesee execution plan below :
July 22, 2020 at 12:53 pm
All the cost is located here: [Excel_DK].[dbo].[Excel_DK].[pk_ID] [DUFP]
The predicate is: [Excel_DK].[dbo].[Excel_DK].[PartNumber] as [DUFP].[PartNumber]=[@partNumber]
Probably need an index on that with some INCLUDE for the other returned columns.
Also, you have three different implicit converts going on. Look at the warnings in the properties of the first operator.
I'm obliged to point out that using NOLOCK everywhere could lead to very bad data being returned. Further, by putting NOLOCK everywhere, you're hurting yourself in the future. When you have to remove all that, it's a ton of work. Just set the connection to be READ_UNCOMMITTED instead. That way you only have one single point to change when you realize just how bad dirty reads are.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2020 at 8:46 pm
In addition to everything Grant said, and he literally wrote the book on execution plans:
I'd also point out that the ORDER BY dufp.FeatureName will cause a sort to happen, so even though you may be trying to do a SELECT TOP 10 or SELECT TOP 100, the entire query needs to run and the sort needs to happen in order for your TOP rows to be returned. There isn't a way around this as long as you keep the ORDER BY.
July 23, 2020 at 12:24 pm
In addition to everything Grant said, and he literally wrote the book on execution plans:
I'd also point out that the ORDER BY dufp.FeatureName will cause a sort to happen, so even though you may be trying to do a SELECT TOP 10 or SELECT TOP 100, the entire query needs to run and the sort needs to happen in order for your TOP rows to be returned. There isn't a way around this as long as you keep the ORDER BY.
True. But, a top without an order you can't guarantee what's being returned. Also, you can get indexes to establish the order in an order by. I mean, it won't happen here with this query, but it can happen. Just sayin'.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2020 at 1:14 pm
Okay, maybe I misunderstood the OP, but I thought from their explanation of the problem that they were trying to use the TOP clause to only get part of the results and hoping it would run faster, which in this case it won't.
July 23, 2020 at 2:03 pm
Okay, maybe I misunderstood the OP, but I thought from their explanation of the problem that they were trying to use the TOP clause to only get part of the results and hoping it would run faster, which in this case it won't.
Or I misunderstood. Entirely possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
the plan for a single part is most likely not going to be the same when executed for more parts (and more companies potentially).
just based on the plan supplied and if parts of the plan remain as they are then there are a few things that are most likely the reason why it goes slower when criteria requires more records - mostly some RID lookups that should be avoided.
suggestions below - I think I'm missing 1 or 2 but no point until we have a plan based on a run with a lot more parts being selected. and I would expect these to be done before this new plan is supplied.
local_url - key lookup (twice) - add column local_url to index Key2
normalizationValue - key lookup - get a good index created
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply