優化回收同一過濾器的一系列查詢

我總共有 50 個表中的大約 30 個查詢，每個查詢都提取或聚合資訊，以便稍後全部連接在一起。提供的原始程式碼完全使用臨時表，但很難維護。查詢中的一個共同主題是WHERE子句中的過濾器，它選擇我們想要的記錄。

我嘗試將臨時表轉換為一系列鍊式 CTE，並將該WHERE子句用作過濾器 CTE，該過濾器 CTE 內部連接到所有子 CTE，以便在更改該過濾器時，它會級聯到所有其他查詢。此外，當添加新查詢時，它們只需加入此 CTE 並應用過濾器。這個想法是我們從只包含所需記錄的 ID 的基本過濾表開始，然後左連接以附加我們想要的度量。

問題是與臨時表相比，這具有巨大的性能下降。我們為了一致性、模組化和易於維護而犧牲了性能。不過，有什麼方法可以調整對我們有利的性能嗎？最大的成功似乎發生在那些使用視窗函式將跨多行的字元串連接成每條記錄一行的人身上。

我以前從未嘗試過這種性質的事情，而 CTE 似乎是合乎邏輯的方法。曾經需要 4-5 分鐘的查詢現在需要 30 分鐘。

臨時表的外觀：

select
   ...
INTO
   #temp1
FROM
   ...
WHERE
   <repeated filter>
   AND
   <temp1 filter>
select
   ...
INTO
   #temp2
FROM
   ...
WHERE
   <repeated filter>
   AND
   <temp2 filter>

SELECT
   ...
FROM
   #temp1
       join
   #temp2
WHERE
   <repeated filter>

並與 CTE 的：

WITH Filter AS (
   SELECT
       ...
   FROM
       ...
   WHERE
       ...
), Query1 AS (
   SELECT
       ...
   FROM
       ...
           INNER JOIN
       Filter
   WHERE
       <query1 filter>
), Query2 AS (
   SELECT
       ...
   FROM
       ...
           INNER JOIN
       Filter
   WHERE
       <query2 filter>
)
SELECT
   ...
FROM
   Filter
       LEFT JOIN
   Query1
       LEFT JOIN
   Query2

要意識到的關鍵是公用表表達式（CTE）不是表；這是一個表達式。至少在 SQL Server 中，沒有實現CTE ；查詢不會執行一次，如果 CTE 在主查詢中出現多次，則結果會被重新使用。每次引用 CTE 時，都會重新執行該查詢。

因此，您可能需要考慮一種混合方法。

Filter我將使用基本過濾數據建構一個臨時表 - CTE目前返回的數據。我也會稍微擴展一下。目前你說它只返回一個 ID 值。如果您在最終查詢中需要任何其他值（特別是如果它們來自未參與中間查詢的表），請將其包含在臨時表中 - 否則，您只需要返回並稍後得到它。

請注意，您可以索引臨時表。除非臨時表的行比我預期的要寬，否則我會在 ID 上放置一個聚集索引。這可能有助於其他連接。（另請注意，使用 an 填充表ORDER BY可能會碰巧以特定順序插入數據，但它不能保證數據庫引擎會辨識 - 它會將數據視為未排序）。

現在，使用 CTE 列表中的臨時Filter表。不必為使用它的每個查詢都重新建構它，這應該會大大改善一些事情。

如果您的列表中有其他 CTE 被多次使用，您可能還需要考慮將它們設為臨時表。但是，根據查詢的複雜性，與可維護性的好處相比，使用兩次或三次可能不會導致顯著的性能損失。

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