這個更大的子查詢如何（很多）更快？

在我的組織中，理光是列印機和影印機的供應商。Papercut 用於記錄製作的副本和帳戶。另一個與理光合作的應用程序是在不同的數據庫中註冊副本，但兩者都有一定的聯繫。

我的組織想要一個中心位置來檢索總成本，所以我寫了一些查詢。大多數工作正常，但下面的那個發生了一些奇怪的事情，我無法弄清楚為什麼會失敗。以下問題的原因可能是什麼。

由於數據庫非常複雜，因此很難製作 db<>fiddle。我將嘗試在這裡解釋這個問題。

本質上，有兩個主要的表。由 Ricoh 維護的表，包含客戶端標識符ClientId、price（複製成本）、列印時間戳和作業標識符。Papercut 維護的表還有一個時間戳、一個printed標誌和一個對相應 JobId 的引用job_comment。

現在考慮以下查詢，它給出了使用單獨的 Ricoh 應用程序列印時的總列印成本。

SELECT CliendId,
      SUM(ricoh.price) AS cost
FROM
 (SELECT a.SystemId as ClientId,
         a.ProcessInternalUid as JobId,
         a.price
  FROM   ricoh.printhistory AS a
  WHERE a.Exportdate >= '2020-10-01'
    AND a.Exportdate <= '2020-12-12') AS ricoh
INNER JOIN
 (SELECT substring(b.job_comment, 16, 36) AS JobId
  FROM  papercut.printer_usage_log AS b
  WHERE b.usage_date >= '2020-10-01'
    AND b.usage_date <= '2020-12-12'
    AND b.printed = 'Y'
  GROUP BY substring(b.job_comment, 16, 36)) AS papercut ON (papercut.JobId = ricoh.JobId)
GROUP BY ricoh.CliendId

如果我執行這個查詢，我會立即得到結果，其中子查詢分別有 96.321ricoh和papercut9.354 行。

但是，如果我將日期範圍的下限更改為2020-11-01查詢需要 49 分鐘（！）。而子查詢分別有 46.223 和 4.547 條記錄。

*怎麼會這樣？*具有更大子查詢的連接如何產生更快的結果？我真的對此感到困惑。時間增加的原因可能是什麼？（然後我可以嘗試進一步調試）

我做了一些（初始）調試並註意到以下內容：

• 有一個限制日期。如果我將下限設置為更高的值2020-10-08，則執行時間會變長。我去尋找一些帶有該時間戳的麻煩記錄，但沒有找到。此外，選擇任何更高的日期，甚至會2020-12-01導致執行時間變長。
• 如果我刪除GROUP BY聚合（和總和）執行是瞬時的。但是如果我添加一個ORDER BY ricoh.price然後執行時間再次上升。價格有問題嗎？它是一個浮點數。我嘗試將其轉換為整數，但問題仍然存在。

編輯：

根據使用者 JD 的要求添加了執行計劃我對這些計劃並不是很熟悉。我猜主要的區別是Hash Match什麼？

• “快速”查詢：執行計劃
• “慢”查詢：執行計劃

此外，此查詢在 SQL Server 2012 上執行。由於數據庫由外部公司管理，我無法添加索引。有沒有辦法重寫查詢以強制“快速”執行計劃？

幾乎可以保證您遇到的是Execution Plan導致不同操作（例如Index Scan代替Index Seek）發生的變化，因為cardinality一個日期範圍與另一個日期範圍返回的數據不同。

Cardinality是從被查詢的表返回的結果的唯一性。SQL Server 引擎用於cardinality確定在查找和提供數據時最好使用哪種類型的操作。然後它生成一個Execution Plan它需要進行的為數據提供服務的全套操作。

由您在、和子句中使用cardinality的 決定。predicatesWHERE``JOIN``HAVING

例如，假設您有一個Cars包含 10 條記錄和列的表Manufacturer，在這 10 條記錄中，其中 8 條Manufacturer設置為“Honda”，其中 2 條設置為“BMW”。“BMW”在表格中的價值比“Honda”要獨特得多。因此，Index Seek如果您的查詢是進行搜尋，則查找和提供數據的性能要高得多WHERE Manufacturer = 'BMW'。但是“Honda”是表中更常見的一個值，Cars如果您將查詢更改為，WHERE Manufacturer = 'Honda'那麼Index Scan操作將更加高效（並且Execution Plan獲取數據的方式也會改變）。這就是它適用於所有類型的列（日期、整數、浮點數等）的方式。

SQL Server 儲存statistics這些值以幫助確定，cardinality但有時它cardinality在提供數據時會錯誤估計導致次優的execution plans. 在沒有具體看到您的情況execution plans下，這是我們可以推測問題所在的最好方法，但似乎很可能是一個cardinality estimate問題。

最重要的一點是，返回的數據量並不是影響查詢性能的唯一因素，實際上更多的時候它並不是性能不佳的原因。在提供數據時，引擎的內部會發生更多的事情。

---

根據您更新的問題，這裡有一些與上述段落相關的附加資訊。

這是您的慢查詢中最相關的部分execution plan：

請注意這裡的箭頭要粗得多，這是因為在此步驟返回的實際數據量比之前在此步驟返回的數據量要多得多。但是再次不要停留在數據量上，更大的問題是，如果您查看“實際行數”，它會返回4,048,577 行，但在“估計行數”下它認為它只會是返回1 行。這就是cardinality estimate issue我提到的發生的地方。（這兩個數字可能會有一點偏差，但通常如果它們偏差一個數量級或更多，那麼這顯然是 . 的問題cardinality estimate。）

cardinality estimate此類問題導致性能問題的一個原因是它們導致引擎請求錯誤數量的硬體資源來提供數據。即，通過低估此步驟將返回的行數，它可能會嚴重不足請求處理此操作所需的記憶體和 CPU。

cardinality estimate可能導致問題發生的一件事是predicates在JOIN、WHERE和HAVING子句中使用函式時。在仔細查看您的查詢後，我看到您正在使用 SUBSTRING() 從papercut.printer_usage_log表中獲取表示 JobId 的值，然後在執行時在子查詢之外加入該值ON (papercut.JobId = ricoh.JobId)。這可能是您cardinality estimate問題的根源。如果您有另一種方法來獲取不涉及使用函式的 JobId（或者至少您可以嘗試替代函式），那麼您可能能夠解決您的問題。

---

您可以嘗試重新編寫這個邏輯等效的查詢，看看它是否能給您帶來更好的execution plan. 唯一的缺點是它不再是單語句查詢，而是利用TempDB. 我不確定是什麼最終消耗了您的查詢（View, Stored Procedure, Report / Application?），因此可能存在一些限制阻止您執行此操作，但如果沒有，那麼這可能會解決您的問題：

-- Materializing your logic for Ricoh and Papercut into their own TempTables
SELECT 
   a.SystemId as ClientId,
   a.ProcessInternalUid as JobId,
   a.price
INTO #Ricoh
FROM ricoh.printhistory AS a
WHERE a.Exportdate >= '2020-10-01'
   AND a.Exportdate <= '2020-12-12'

-- This materializes the SUBSTRING() function call's results to a TempTable so that when we join on JobId later on, we're not joining by a function call rather we're joining by the actual value itself
SELECT SUBSTRING(b.job_comment, 16, 36) AS JobId 
INTO #Papercut
FROM  papercut.printer_usage_log AS b
WHERE b.usage_date >= '2020-10-01'
   AND b.usage_date <= '2020-12-12'
   AND b.printed = 'Y'
GROUP BY SUBSTRING(b.job_comment, 16, 36)

-- Indexing the aforementioned temp tables
CREATE CLUSTERED INDEX IX_Ricoh_JobId ON #Ricoh (JobId)
CREATE CLUSTERED INDEX IX_Ricoh_JobId ON #Papercut (JobId)

-- Final select
SELECT 
   #Ricoh.CliendId,
   SUM(#Ricoh.price) AS cost
FROM #Ricoh
INNER JOIN #Papercut 
   ON (#Papercut.JobId = #Ricoh.JobId)
GROUP BY #Ricoh.CliendId

如果您能夠使用此多語句查詢，請告訴我它是如何工作的，您也可以上傳新execution plan的以供我們參考。

我仍在分析您之間的差異，execution plans並會盡快提出解決方案。

---

要查看，Execution Plan您可以像這樣在 SQL Server Management Studio 中啟用它（然後執行您的查詢）：

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