使用 $Partition 函式提高查詢性能

我有基於INT列分區的表。

我看到一些使用$Partition函式來比較分區號而不是比較實際欄位數據的查詢。

例如，而不是說：

select *
from T1 
   inner join T2 on T2.SnapshotKey = T1.SnapshotKey

它們的寫法如下：

select *
from T1 
   inner join T2 on $Partition.PF_Name(T2.SnapshotKey) = $Partition.PF_Name(T1.SnapshotKey)

其中PF_Name是分區函式的名稱。

我看到對這些查詢的評論說這樣做是為了提高性能，當我執行這兩個查詢時，我看到執行時間和執行計劃不同。我不確定這兩個查詢有何不同。

這是一個真實的查詢：

-- this takes about 9 seconds 
select sum(PrinBal)
from fpc
   inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

-- this takes about 5 seconds
select sum(PrinBal)
from fpc
   inner join gl on $Partition.SnapshotKeyPF(gl.SnapshotKey) = $Partition.SnapshotKeyPF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

以下是真實查詢的執行計劃：

抱歉，我什至無法上傳經過清理的執行計劃，因為上傳受到我們的網路監控，這可能違反了政策。

問題是：為什麼執行計劃不同，為什麼第二個查詢更快。

如果有人可以就此分享任何想法，我將不勝感激。只是想知道它們為什麼不同。不過越快越好。

這發生在 SQL Server 2014 上。如果我在 SQL Server 2012 上執行相同的程序，結果會有所不同，第一個查詢會執行得更快！

為什麼執行計劃不同

第一次查詢

select sum(PrinBal)
from fpc
   inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

優化器知道：

• gl.SnapshotKey = fpc.SnapshotKey; 和
• fpc.SnapshotKey = 201703

所以它可以推斷：

• gl.SnapshotKey = 201703

就像你寫的一樣：

select sum(PrinBal)
from fpc
   inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703
and gl.SnapshotKey = 201703

優化器也可以使用文字值 201703 來確定分區 ID。對於兩個SnapshotKey謂詞（一個給定，一個推斷），這意味著優化器知道兩個表的分區 ID。

更進一步，在兩個表上現在都可以使用文字值 (201703) SnapshotKey，連接謂詞：

• gl.SnapshotKey = fpc.SnapshotKey

簡化為：

• 201703 = 201703; 或者乾脆
• true

這意味著根本沒有連接謂詞。結果是邏輯交叉連接。使用最接近的可用 T-SQL 語法表達最終的執行計劃，就好像你寫了：

SELECT
   CASE
       WHEN SUM(Q1.c) = 0 THEN NULL
       ELSE SUM(Q1.s)
   END
FROM 
(
   SELECT c = COUNT_BIG(*), s = SUM(GL.PrinBal)
   FROM dbo.gl AS GL
   WHERE GL.SnapshotKey = 201703
   AND $PARTITION.PF(GL.SnapshotKey) = $PARTITION.PF(201703)
) AS Q1
CROSS JOIN
(
   SELECT Dummy = 1
   FROM dbo.fpc AS FPC
   WHERE FPC.SnapshotKey = 201703
   AND $PARTITION.PF(FPC.SnapshotKey) = $PARTITION.PF(201703)
) AS Q2;

第二次查詢

select sum(PrinBal)
from fpc
   inner join gl on $Partition.PF(gl.SnapshotKey) = $Partition.PF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

優化器無法再推斷出任何關於的資訊gl.SnapshotKey，因此不再可能對第一個查詢進行簡化和轉換。

實際上，除非每個分區確實只有一個 ，否則SnapshotKey不能保證重寫會產生相同的結果。

同樣，表達使用最接近的可用 T-SQL 語法生成的執行計劃：

SELECT
   CASE
       WHEN SUM(Q2.c) = 0 THEN NULL
       ELSE SUM(Q2.s)
   END
FROM 
(
   SELECT
       Q1.PtnID,
       c = COUNT_BIG(*),
       s = SUM(Q1.PrinBal)
   FROM 
   (
       SELECT GL.PrinBal, PtnID = $PARTITION.PF(GL.SnapshotKey)
       FROM dbo.gl AS GL
   ) AS Q1
   GROUP BY
       Q1.PtnID
) AS Q2
CROSS APPLY
(
   SELECT Dummy = 1
   FROM dbo.fpc AS FPC
   WHERE
       $PARTITION.PF(FPC.SnapshotKey) = Q2.PtnID
       AND FPC.SnapshotKey = 201703
) AS Q3;

這次沒有邏輯交叉連接。相反，分區 id 上有一個相關連接（應用）。

---

為什麼第二個查詢更快。

這很難從給出的資訊中評估。使用基於提供的查詢和計劃圖像的模擬數據和表，我發現第一個查詢在每種情況下都優於第二個查詢。

使用不同語法表示的相同查詢通常會產生不同的執行計劃，這僅僅是因為優化器從不同的點開始，並在找到合適的執行計劃之前以不同的順序探索選項。計劃搜尋並不詳盡，而且並非所有可能的邏輯轉換都可用，因此最終結果可能會有所不同。如上所述，這兩個查詢不一定表達相同的要求（至少考慮到優化器可用的資訊）。

另請注意，SQL Server 2012（以及在較小程度上，2014）中的初始列儲存實現有很多限制，尤其是在優化方面。通過升級到更新版本（最好是最新版本），您可能會獲得更好、更一致的結果。如果您要使用分區，則尤其如此。

我當然不建議您養成使用 重寫連接的習慣$PARTITION，除非作為最後的手段，並且對您正在做的事情有非常深刻的理解。

這就是我能說的所有內容，但無法查看架構或計劃細節。

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