-1

I work with SQL Server and face an issue that a query is very slow.

INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID,
                                                       Code,
                                                       CodeTypeID,
                                                       SourceTypeID,
                                                       RevisionID,
                                                       ZPLID,
                                                       PartLevel,
                                                       CreatedDate,
                                                       FlagDelete)

----select count(1) from [ExtractReports].[dbo].[TradeCodesDelete]

---update [ExtractReports].[dbo].[TradeCodesDelete] set FlagDelete=2 where FlagDelete=1

SELECT DISTINCT
       FT.PartId,
       TN.Code,
       FT.CodeTypeID,
       FT.SourceTypeID,
       FT.RevisionID,
       fm.[Value],
       FT.PartLevel,
       GETDATE(),
       1
FROM Parts.Nop_Part pt WITH (NOLOCK)
     INNER JOIN Parts.Nop_PartsFamilyAttribute fm WITH (NOLOCK) ON pt.PartsFamilyID = fm.PartFamilyID
                                                               AND fm.[Key] = 20281007
     INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value
     INNER JOIN Parts.TradeCodes FT WITH (NOLOCK) ON pt.PartID = FT.PartID
                                                 AND FT.PartLevel = 0
     INNER JOIN ExtractReports.dbo.TPLNewData TN WITH (NOLOCK) ON TN.PartID = FT.PartID
                                                              AND TN.CodeTypeID = FT.CodeTypeID
     LEFT JOIN [ExtractReports].[dbo].[TradeCodesDelete] d ON d.partid = FT.partid
                                                          AND d.codetypeid = FT.codetypeid
                                                          AND d.partlevel = 0
WHERE TN.Code <> FT.Code
  AND FT.MappingDoneFlag = 0
  AND d.partid IS NULL;

I check the execution plan for a reason of slow query performance.

This is my execution plan: https://www.brentozar.com/pastetheplan/?id=SJCzRrmht

  • Index seek is high at 57% - how to reduce it?
  • Hash match inner join is high at 40% - how to reduce it?
  • Compute scalar is at 1% - how to reduce it?

Can anyone help me?

Table script as below :

CREATE TABLE [Parts].[Nop_PartsFamilyAttribute]
(
     [PartFamilyAttributeID] [int] IDENTITY(1,1) 
          NOT FOR REPLICATION NOT NULL,
     [PartFamilyID] [int] NOT NULL,
     [Key] [int] NOT NULL,
     [Value] [nvarchar](2200) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,

     CONSTRAINT [PK_Nop_PartsFamilyAttribute30] 
         PRIMARY KEY CLUSTERED ([PartFamilyAttributeID] ASC)
                 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                       ALLOW_PAGE_LOCKS = ON) ON [Customer],
     CONSTRAINT [UK_PartFamilyID_Key30] 
         UNIQUE NONCLUSTERED ([PartFamilyID] ASC, [Key] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer]

what I try :

create index pf_idx on Parts.Nop_PartsFamilyAttribute([Key]) include ([Value])

but nothing changed still index seek 57

and hash match 40

and compute scalar cost 1

so what I do to solve issue of Execution plan ?

10
  • 4
  • 2
    I would say start with removing NOLOCK and look at how the plan changes after that.
    – squillman
    Jan 5 at 18:56
  • 3
    Why is [Value] a [nvarchar](2200)? It gets implicitly converted to an int before the hash join you are asking about. (ON pl.ZPLID = fm.Value) - potentially you could get rid of the implicit cast and add a suitable index to allow that to be seeked on, Jan 5 at 19:34
  • 1
    The estimates you're concerned with are just estimates and even more so this is in your estimated execution plan not the actual execution plan, so they may even be misleading or the actual execution plan could be completely different. 1. How long does the query take to run / is it slower than what's acceptable? 2. Can you provide the actual execution plan in your question instead (which would be more helpful to advise you on what to do)? 3. Stop worrying so much about the estimated %'s. 4. Stop using NOLOCK hints everywhere, it's most likely not helping your performance...
    – J.D.
    Jan 5 at 23:45
  • 1
    I'd suggest: Removing DISTINCT, it is a code smell indicative of poorly thought joins, you should find where the duplicates are coming from rather than blindly using DISTINCT. Add SourceTypeID and RevisionID to the TradeCodes index as INCLUDE columns, avoiding the key lookup. And swapping round the two columns on UK_PartFamilyID_Key30 so you get CONSTRAINT [UK_PartFamilyID_Key30] UNIQUE NONCLUSTERED ([PartFamilyID] ASC, [Key] ASC) or at least adding a secondary index with that. Also remove the type mismatch, and convert the LEFT JOIN IS NULL to a WHERE NOT EXISTS Jan 6 at 9:31

2 Answers 2

1

anti

Your query has a common anti-pattern in it, using a left join to find rows that don't exist.

Your query currently looks like this:

<snip>
     LEFT JOIN [ExtractReports].[dbo].[TradeCodesDelete] d ON d.partid = FT.partid
                                                          AND d.codetypeid = FT.codetypeid
                                                          AND d.partlevel = 0
WHERE 
<snip>
  AND d.partid IS NULL;

You're not selecting any rows from d., which is sensible because assuming d.partid is a non-nullable/key column, all the resulting columns would be NULL.

This results in a "late filter" in the query plan, meaning all rows are fully joined together and then the predicate is applied later:

NUTS

A useful rewrite is to use the NOT EXISTS syntax for its intended purpose.

  SELECT DISTINCT
       FT.PartId,
       TN.Code,
       FT.CodeTypeID,
       FT.SourceTypeID,
       FT.RevisionID,
       fm.[Value],
       FT.PartLevel,
       GETDATE(),
       1
FROM Parts.Nop_Part pt WITH (NOLOCK)
     INNER JOIN Parts.Nop_PartsFamilyAttribute fm WITH (NOLOCK) ON pt.PartsFamilyID = fm.PartFamilyID
                                                               AND fm.[Key] = 20281007
     INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value
     INNER JOIN Parts.TradeCodes FT WITH (NOLOCK) ON pt.PartID = FT.PartID
                                                 AND FT.PartLevel = 0
     INNER JOIN ExtractReports.dbo.TPLNewData TN WITH (NOLOCK) ON TN.PartID = FT.PartID
                                                              AND TN.CodeTypeID = FT.CodeTypeID
WHERE TN.Code <> FT.Code
  AND FT.MappingDoneFlag = 0
  AND NOT EXISTS
      (
          SELECT
              1/0
          FROM [ExtractReports].[dbo].[TradeCodesDelete] d 
          WHERE d.partid = FT.partid
          AND   d.codetypeid = FT.codetypeid
          AND   d.partlevel = 0
      );

aside

I see you talking a lot about costs, and reducing them. Please don't pay attention to cost. It's a unit-less, estimated metric that has nothing to do with which parts of your query are slow and inefficient.

Note that when you get an actual execution plan, there are no "actual" costs. They remain estimates, even after SQL Server measures and reports on many other runtime (actual) metrics.

The optimizer uses costs to choose a good enough plan quickly. Reducing them won't necessarily get you a faster query. You can have expensive queries that run quickly, and cheap queries that run slowly.

Since you have SQL Server 2017 tagged here, I would suggest looking at operator times to figure out which parts are most inefficient.

1

Create the following index:

create index pf_idx 
    on Parts.Nop_PartsFamilyAttribute([Key],PartFamilyID) 
    include ([Value])

The problem currently is that even though it is "seeking", it's not doing so efficiently, plus it is estimating +4MM rows returned from those seeks into the hash-join, which basically is where all of the time is going. It appears that the Seek is only matching the [Key] value and then relying on the hash-join to do the PartFamilyID filtering, but at 4MM rows it's a lot of overhead. The index that I suggested above should alleviate much of that by enabling it to more efficiently seek (or maybe even scan) the rows that it needs, matching both [Key] and PartFamilyID.

----

As @MartinSmith pointed out, you have a much bigger problem with your second join which I missed:

 INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value

The problem here is that while pl.ZIPID is an INT, fm.Value is an NVARCHAR which makes the whole JOIN non-Sargeable.

The solution to this is as follows:

  1. Assuming that you cannot just change the Nop_PartsFamilyAttribute.Value column to INT, then add a persisted calculated column:

->

`ALTER Parts.Nop_PartsFamilyAttribute
  ADD IntValue AS (Try_Parse([Value] AS INT)) PERSISTED;`
  1. Create the following index (instead of the one I proposed above).

->

create index pf_idx2 
    on Parts.Nop_PartsFamilyAttribute([Key],IntValue,PartFamilyID) 
  1. Change your second join to:

    INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.IntValue

  2. Finally, change the column list of your query to this:

->

SELECT DISTINCT
       FT.PartId,
       TN.Code,
       FT.CodeTypeID,
       FT.SourceTypeID,
       FT.RevisionID,
       CAST(fm.IntValue AS Nvarchar(2000)) AS [Value],
       FT.PartLevel,
       GETDATE(),
       1
6
  • issue still exist still index seek 57 and hash match 40 and compute scalar cost 1 Jan 5 at 19:25
  • @abeershlby Is that the actual or estimated execution plan? If you have an actual execution plan, could you post that?? Also, what's the total row count for the Nop_PartsFamilyAttribute table? Jan 5 at 19:29
  • The expensive hash join is ExtractReports.dbo.TPls pl HASH JOIN (Parts.Nop_PartsFamilyAttribute WHERE [Key] = 20281007) fm ON pl.ZPLID = CAST(fm.Value AS INT) so it would need an index on Key, Value and a removal of the implicit cast to get a seek there Jan 5 at 19:42
  • @MartinSmith Wow, I completely missed that. thx Jan 5 at 19:48
  • can you please show me what you mean by The expensive hash join is ExtractReports.dbo.TPls pl Jan 5 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.