Sql-Server

LEFT OUTER JOIN 中嵌套“OR”條件的 SQL Server 性能不佳

  • May 7, 2020

我有一些棘手的 SQL，這給我帶來了一些麻煩。我想向一個鍵表引入幾個新列，根據使用者配置（維度 1-10），可能會或可能不會使用其組件。出於架構原因，這些儲存為“UNIQUEIDENTIFIER”。

但是，在我的數據庫中引入這些新維度列後，我現在在其他幾個操作此鍵表中的數據的儲存過程中面臨性能問題，我將在下面介紹其中一個。似乎我為支持這些新列而實施的方法效率低下，我不確定為什麼會出現這種情況以及解決它的最佳方法。

（包括 SQL）

--In real usage this would be set by user configuration
DECLARE @Dimension1Enabled BIT = 1
DECLARE @Dimension2Enabled BIT = 1
DECLARE @Dimension3Enabled BIT = 1
DECLARE @Dimension4Enabled BIT = 1
DECLARE @Dimension5Enabled BIT = 1
DECLARE @Dimension6Enabled BIT = 1
DECLARE @Dimension7Enabled BIT = 1
DECLARE @Dimension8Enabled BIT = 1
DECLARE @Dimension9Enabled BIT = 1
DECLARE @Dimension10Enabled BIT = 1

/*
--Here's the table definition for reference
CREATE TABLE [dbo].[Table1](
   [Column1] [int] NOT NULL,
   [Column2] [uniqueidentifier] NOT NULL,
   [Column3] [uniqueidentifier] NOT NULL,
   [Column4] [char](3) NOT NULL,
   [Dimension1] [uniqueidentifier] NULL,
   [Dimension2] [uniqueidentifier] NULL,
   [Dimension3] [uniqueidentifier] NULL,
   [Dimension4] [uniqueidentifier] NULL,
   [Dimension5] [uniqueidentifier] NULL,
   [Dimension6] [uniqueidentifier] NULL,
   [Dimension7] [uniqueidentifier] NULL,
   [Dimension8] [uniqueidentifier] NULL,
   [Dimension9] [uniqueidentifier] NULL,
   [Dimension10] [uniqueidentifier] NULL,
   [Period] [int] NOT NULL,
   [Amt] [numeric](28, 12) NOT NULL,
   [EndingBal] [numeric](28, 12) NOT NULL,
   [PlanningSourceID] [int] NOT NULL,
   [Column5] [uniqueidentifier] NULL
) ON [PRIMARY]

--Simply contains 0-12
CREATE TABLE [dbo].[Table2] (
   [FiscalPeriod] [int] NOT NULL
   ,[FiscalPeriodDescription] [varchar](50) NOT NULL
   ,[FiscalPeriodDescriptionMonthName] [varchar](20) NULL
   ,[FiscalPeriodName] [varchar](255) NULL
)
*/

SELECT pfd.*
FROM (
   SELECT PFDE1.Column1
       ,PFDE1.Column2
       ,PFDE1.Column3
       ,PFDE1.Column4
       ,PFDE1.Dimension1
       ,PFDE1.Dimension2
       ,PFDE1.[Dimension3]
       ,PFDE1.[Dimension4]
       ,PFDE1.[Dimension5]
       ,PFDE1.[Dimension6]
       ,PFDE1.[Dimension7]
       ,PFDE1.[Dimension8]
       ,PFDE1.[Dimension9]
       ,PFDE1.[Dimension10]
       ,p.FiscalPeriod AS Period
       ,0 AS Amt
       ,0 AS EndingBal
       ,PFDE1.Column5
   FROM (
       SELECT DISTINCT PFDE1.Column1
           ,PFDE1.Column2
           ,PFDE1.Column3
           ,PFDE1.Column4
           ,PFDE1.Dimension1
           ,PFDE1.Dimension2
           ,PFDE1.[Dimension3]
           ,PFDE1.[Dimension4]
           ,PFDE1.[Dimension5]
           ,PFDE1.[Dimension6]
           ,PFDE1.[Dimension7]
           ,PFDE1.[Dimension8]
           ,PFDE1.[Dimension9]
           ,PFDE1.[Dimension10]
           ,PFDE1.Column5
       FROM [dbo].[Table1] PFDE1
       ) PFDE1
   CROSS JOIN [dbo].[Table2] p
   ) pfd
LEFT JOIN [dbo].[Table1] pfde ON PFDE.Column1 = pfd.Column1
   AND PFDE.Column2 = pfd.Column2
   AND PFDE.Column3 = pfd.Column3
   AND PFDE.Column4 = pfd.Column4
   --This section causes great slowness
   AND (@Dimension1Enabled = 0 OR PFDE.Dimension1 = pfd.Dimension1)
   AND (@Dimension2Enabled = 0 OR PFDE.Dimension2 = pfd.Dimension2)
   AND (@Dimension3Enabled = 0 OR PFDE.Dimension3 = pfd.Dimension3)
   AND (@Dimension4Enabled = 0 OR PFDE.Dimension4 = pfd.Dimension4)
   AND (@Dimension5Enabled = 0 OR PFDE.Dimension5 = pfd.Dimension5)
   AND (@Dimension6Enabled = 0 OR PFDE.Dimension6 = pfd.Dimension6)
   AND (@Dimension7Enabled = 0 OR PFDE.Dimension7 = pfd.Dimension7)
   AND (@Dimension8Enabled = 0 OR PFDE.Dimension8 = pfd.Dimension8)
   AND (@Dimension9Enabled = 0 OR PFDE.Dimension9 = pfd.Dimension9)
   AND (@Dimension10Enabled = 0 OR PFDE.Dimension10 = pfd.Dimension10) 
   AND PFDE.Period = pfd.Period
   AND PFDE.Column5 = pfd.Column5
WHERE pfde.Column1 IS NULL

Table1 中有 50,000 條記錄，此查詢的性能很差，至少幾分鐘。刪除“@DimensionXEnabled = 0 OR”條件，查詢將在幾秒鐘內執行。

我覺得我有幾個選項可以優化它，我可以執行以下操作之一：

  1. 將“維度”列更改為 NOT NULL 並填充 0 Guid 值 - 我相信這會對性能產生影響，因為現在我要處理每次插入的大量數據。我已經測試了這個選項，它確實工作得更好，但有一個明確的性能影響，如果可能的話我想避免它。
  2. 將查詢轉換為動態 SQL 並動態生成連接條件 - 需要在 100 多個其他地方執行此操作，並且有一個充滿動態 SQL 的數據庫，也不期待這個選項。
  3. 在 ISNULL 語句中包裝“Dimension1 = Dimension1”，而不是基於變數的連接條件 - 我相信這會導致連接語句效率低下，因為查詢將不再能夠使用索引。

我覺得 SQL 優化器對這個查詢做出了錯誤的決定，如果我編寫沒有 OR 條件的查詢，它執行得很快，即使結果完全相同。除了長時間執行的連接之外，查詢計劃看起來幾乎相同，大部分處理時間用於 LEFT OUTER JOIN 語句合併：

（帶有“或”條件）

在此處輸入圖像描述

（沒有“或”條件）

在此處輸入圖像描述

我不確定如何繼續，我的問題是，有更好的選擇嗎？

更新 1

我一直在對此進行更多研究，嘗試各種方法來解決性能不佳的問題，到目前為止，我還沒有找到任何好的解決方案，只是提供了更多資訊：

  1. 嘗試列“IS NULL”但沒有成功（PFDE.Dimension1 IS NULL OR PFDE.Dimension1 = pfd.Dimension1），執行速度更慢，這似乎沒有做到。
  2. 累加入“Hints”（HASH、MERGE、LOOP），這裡沒有性能影響，實際上在某些情況下它們只是執行速度較慢。
  3. 嘗試了選項 3，實際上這似乎執行得非常快，我認為因為 Table2 沒有任何索引，這種方法在未來可能會成為一個問題……
  4. 我嘗試過動態 SQL（@AaronBertrand 推薦），實際上結果有點令人印象深刻（30 秒+），不知道為什麼它表現如此糟糕，但這可能是我可以通過進一步優化來改進的東西，我正在研究這更..
  5. 我嘗試了“選項（重新編譯）；” 在這篇文章中提到。看到了明顯的改進（約 15 秒），仍然沒有尺寸那麼快，但也許我可以迭代其他一些東西。

聽起來像廚房水槽查詢。對於大多數使用動態 SQL 的情況，您可以使其很好地工作，因為您可以為每個參數組合生成不同的執行計劃。

DECLARE @sql nvarchar(max) = N'
   SELECT DISTINCT PFDE1.Column1
       ,PFDE1.Column2
       ,PFDE1.Column3
       ,PFDE1.Column4
       ,PFDE1.Dimension1
       ,PFDE1.Dimension2
       ...
       ,PFDE1.[Dimension10]
       ,PFDE1.Column5
   FROM [dbo].[Table1] PFDE1
   ) PFDE1
CROSS JOIN [dbo].[Table2] p
) pfd
LEFT JOIN [dbo].[Table1] pfde 
ON PFDE.Column1 = pfd.Column1
AND PFDE.Column2 = pfd.Column2
AND PFDE.Column3 = pfd.Column3
AND PFDE.Column4 = pfd.Column4'

+ CASE WHEN @Dimension1Enabled = 1 THEN 
 N' AND PFDE.Dimension1 = pfd.Dimension1' ELSE N'' END
+ CASE WHEN @Dimension2Enabled = 1 THEN 
 N' AND PFDE.Dimension2 = pfd.Dimension2' ELSE N'' END

+ ...

+ CASE WHEN @Dimension10Enabled = 1 THEN 
 N' AND PFDE.Dimension10 = pfd.Dimension10' ELSE '' END

+ N'    AND PFDE.Period = pfd.Period
 AND PFDE.Column5 = pfd.Column5
 WHERE pfde.Column1 IS NULL;';

EXEC sys.sp_executesql @sql;

我也會放棄DISTINCT- 如果由於重複而確實有必要，那麼還有一個潛在的問題需要解決。

IMO，最大的錯誤是您使用的地方CROSS JOIN。

首先自我加入[dbo].[Table1]並過濾結果集，然後使用此結果集CROSS JOIN。它將帶來巨大的推動作用。

SELECT pfd.*
FROM (
   SELECT PFDE1.Column1
       ,PFDE1.Column2
       ,PFDE1.Column3
       ,PFDE1.Column4
       ,PFDE1.Dimension1
       ,PFDE1.Dimension2
       ,PFDE1.[Dimension3]
       ,PFDE1.[Dimension4]
       ,PFDE1.[Dimension5]
       ,PFDE1.[Dimension6]
       ,PFDE1.[Dimension7]
       ,PFDE1.[Dimension8]
       ,PFDE1.[Dimension9]
       ,PFDE1.[Dimension10]
       ,p.FiscalPeriod AS Period
       ,0 AS Amt
       ,0 AS EndingBal
       ,PFDE1.Column5
   FROM (
       SELECT DISTINCT PFDE1.Column1
           ,PFDE1.Column2
           ,PFDE1.Column3
           ,PFDE1.Column4
           ,PFDE1.Dimension1
           ,PFDE1.Dimension2
           ,PFDE1.[Dimension3]
           ,PFDE1.[Dimension4]
           ,PFDE1.[Dimension5]
           ,PFDE1.[Dimension6]
           ,PFDE1.[Dimension7]
           ,PFDE1.[Dimension8]
           ,PFDE1.[Dimension9]
           ,PFDE1.[Dimension10]
           ,PFDE1.Column5
       FROM [dbo].[Table1] PFDE1
   LEFT JOIN [dbo].[Table1] pfde ON PFDE.Column1 = PFDE1.Column1
   AND PFDE.Column2 = PFDE1.Column2
   AND PFDE.Column3 = PFDE1.Column3
   AND PFDE.Column4 = PFDE1.Column4
   AND PFDE.Period = PFDE1.Period
   AND PFDE.Column5 = PFDE1.Column5
   AND (@Dimension1Enabled = 0 OR PFDE.Dimension1 = PFDE1.Dimension1)
   AND (@Dimension2Enabled = 0 OR PFDE.Dimension2 = PFDE1.Dimension2)
   AND (@Dimension3Enabled = 0 OR PFDE.Dimension3 = PFDE1.Dimension3)
   AND (@Dimension4Enabled = 0 OR PFDE.Dimension4 = PFDE1.Dimension4)
   AND (@Dimension5Enabled = 0 OR PFDE.Dimension5 = PFDE1.Dimension5)
   AND (@Dimension6Enabled = 0 OR PFDE.Dimension6 = PFDE1.Dimension6)
   AND (@Dimension7Enabled = 0 OR PFDE.Dimension7 = PFDE1.Dimension7)
   AND (@Dimension8Enabled = 0 OR PFDE.Dimension8 = PFDE1.Dimension8)
   AND (@Dimension9Enabled = 0 OR PFDE.Dimension9 = PFDE1.Dimension9)
   AND (@Dimension10Enabled = 0 OR PFDE.Dimension10 = PFDE1.Dimension10) 

WHERE pfde.Column1 IS NULL
       ) PFDE1
   CROSS JOIN [dbo].[Table2] p
   ) pfd

希望我的觀點很清楚，它未經測試。

毫無疑問 Dynamic Sql 會進一步改進它， EXEC sys.sp_executesql @sql

我也會放棄 DISTINCT - 如果由於重複而確實有必要，那麼還有一個潛在的問題需要解決。

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

相關問答

Sql-Server

您是否認為有必要為只有兩個元素（id 和 name）的表創建一個輔助翻譯表

  • April 1, 2020
檢視問答
Sql-Server

OLTP 索引視圖或 SQL 計算

  • November 12, 2017
檢視問答
Sql-Server

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答
Sql-Server

OPTION FORCE ORDER 提高性能直到刪除行

  • June 30, 2021
檢視問答
Sql-Server

有沒有更好的方法來處理多級 ParentId 表結構？

  • May 25, 2021
檢視問答
Sql-Server

查詢計劃中有關記憶體“過度授予”的警告 - 如何找出導致它的原因？

  • January 26, 2021
檢視問答