聚合查詢的慢速執行聚合

我正在尋找截至特定時間點有負餘額的所有賬戶的總餘額。我已經得到了下面的程式碼，它可以工作，但是效果很糟糕。

事務表包含 2000 萬行。下面的查詢大約需要 30 秒，這還不錯，但第二部分是我需要在數據庫啟動後的每個月的第一天重複 @report_date，這會將執行時間增加到約 30 分鐘。

這是在 MSSQL2008 上，我在執行計劃上沒有收到任何缺少索引的警告，但我懷疑我的問題仍然出在索引上，所以我故意將它們放在此處。

CREATE TABLE transactions(
   transaction_id int,
   account_id int,
   department_id int,
   location_id int,
   post_date date,
   amount money
   );

SELECT
   t2.department_id,
   t2.location_id,
   SUM(t2.credit_balances)
FROM
   (
   SELECT
       t1.department_id,
       t1.location_id,
       t1.account_id,
       SUM(t1.amount) as credit_balances
   FROM
       transactions t1
   WHERE
       t1.post_date < @report_date
   GROUP BY
       t1.department_id,
       t1.location_id,
       t1.account_id
   HAVING
       SUM(t1.amount) < 0
   ) t2
GROUP BY
   t2.department_id,
   t2.location_id;

該表包含 38 個不同的department_id,location_id組合，以及 450 萬個不同的department_id,location_id,account_id.

當您重複此查詢數月時，您將不斷重新聚合相同的行。

例如，第一個月的行將始終由t1.post_date < @report_date標準帶回，因此每個月都會重新處理。

為了避免這種情況，我可能會考慮從一開始就以迭代的方式一個月一次地完成它。根據歷史數據的波動性，我還可以考慮將預先計算的結果儲存在數據庫中，而不是每個月重新計算這些結果。

要在執行時計算此值，您可以創建一個具有以下結構的臨時表。

CREATE TABLE #balance
 (
    department_id   INT NOT NULL,
    location_id     INT NOT NULL,
    account_id      INT NOT NULL,
    balance_to_date MONEY NOT NULL,
    PRIMARY KEY (department_id, location_id, account_id)
 ); 

transactions您還可以考慮在您的表上添加以下索引

ALTER TABLE transactions
 ADD post_date_year_month AS (10000 * YEAR(post_date) + MONTH(post_date))

CREATE INDEX ix
 ON transactions(post_date_year_month, department_id, location_id, account_id)
 INCLUDE (amount) 

然後一次提取一個月transactions並合併到#balance（匹配時增加，不匹配時插入）。

前導post_date_year_month列意味著只要您編寫查詢 sargably，就可以有效地完成每個月的提取，並且一個月的提取行將通過在沒有排序的情況下department_id, location_id, account_id進行合併連接#balance來排序。

雖然這可能有利於此特定查詢，但您需要根據您的整體工作負載評估此索引的實用性。

然後計算department_id, location_id總數#balance（可以利用 PK 順序來避免排序）並將它們儲存在某個地方並移動到下個月。

（或者可能代替#balance您使用“臨時”永久表balance並在其上創建索引視圖以避免單獨的顯式聚合步驟，並在繼續之前直接複製值）

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