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

    see execution plan below :

    https://www.brentozar.com/pastetheplan/?id=HyclwfSev

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • In addition to everything Grant said, and he literally wrote the book on execution plans:

    https://www.red-gate.com/simple-talk/books/sql-server-execution-plans-third-edition-by-grant-fritchey/

    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.

  • Chris Harshman wrote:

    In addition to everything Grant said, and he literally wrote the book on execution plans:

    https://www.red-gate.com/simple-talk/books/sql-server-execution-plans-third-edition-by-grant-fritchey/

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

  • Chris Harshman wrote:

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

    • #getdelivery - table scan. add clustered index on DK_Feature
    • Nop_partparamtricattribute - Key lookup - can a new index be added or existing one changed to include the other columns?

      local_url - key lookup (twice) - add column local_url to index Key2

    • nop_partsFamilyAttribute - key lookup (twice) can a new index be added or existing one changed to include the other columns?
    • core_datadefinition - tablescan for each execution - see how an index can be created/used

      normalizationValue - key lookup - get a good index created

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply