在 SQL Server 上執行查詢時，速度非常慢。如何解決執行計劃問題？

我使用 SQL Server 並面臨查詢非常慢的問題。

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;

由於查詢性能緩慢，我檢查了執行計劃。

這是我的執行計劃：https ://www.brentozar.com/pastetheplan/?id=SJCzRrmht

• 索引搜尋高達 57% - 如何降低它？
• 雜湊匹配內部連接高達 40% - 如何減少它？
• 計算標量為 1% - 如何減少它？

誰能幫我？

表格腳本如下：

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]

我嘗試什麼：

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

但沒有任何改變仍然索引搜尋 57

和雜湊匹配 40

併計算標量成本 1

那麼我該怎麼做才能解決執行計劃的問題？

反對

您的查詢中有一個常見的反模式，使用左連接來查找不存在的行。

您的查詢目前如下所示：

<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;

您沒有從中選擇任何行d.，這是明智的，因為假設d.partid是不可為空/鍵的列，則所有結果列都是NULL.

這會在查詢計劃中產生“後期過濾器”，這意味著所有行都完全連接在一起，然後稍後應用謂詞：

一個有用的重寫是將NOT EXISTS語法​​用於其預期目的。

 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
     );

在旁邊

我看到你經常談論成本，並降低成本。請不要關注成本。這是一個無單位的估計指標，與查詢的哪些部分緩慢且效率低下無關。

請注意，當您獲得實際執行計劃時，沒有“實際”成本。即使在 SQL Server 測量和報告許多其他執行時（實際）指標之後，它們仍然是估計值。

• 為什麼你應該停止查看查詢成本
• 是時候從查詢計劃中刪除成本了嗎？
• 執行計劃對您撒謊的跡象：運營商成本

優化器使用成本快速選擇一個足夠好的計劃。減少它們不一定會讓您獲得更快的查詢。您可以有快速執行的昂貴查詢和執行緩慢的廉價查詢。

由於您在此處標記了 SQL Server 2017，因此我建議查看操作員時間以確定哪些部分效率最低。

引用自：https://dba.stackexchange.com/questions/305676