如何更快地獲得最近行的執行總數？

我目前正在設計一個事務表。我意識到需要計算每一行的執行總數，這可能會降低性能。因此，我創建了一個包含 100 萬行的表用於測試目的。

CREATE TABLE [dbo].[Table_1](
   [seq] [int] IDENTITY(1,1) NOT NULL,
   [value] [bigint] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
   [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

我試圖獲取最近的 10 行及其執行總數，但花了大約 10 秒。

--1st attempt
SELECT TOP 10 seq
   ,value
   ,sum(value) OVER (ORDER BY seq) total
FROM Table_1
ORDER BY seq DESC

--(10 rows affected)
--Table 'Worktable'. Scan count 1000001, logical reads 8461526, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Table_1'. Scan count 1, logical reads 2608, physical reads 516, read-ahead reads 2617, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 8483 ms,  elapsed time = 9786 ms.

我懷疑TOP是因為計劃中性能慢的原因，所以我這樣更改查詢，大約需要1~2秒。但我認為這對於生產來說仍然很慢，並且想知道這是否可以進一步改進。

--2nd attempt
SELECT *
   ,(
       SELECT SUM(value)
       FROM Table_1
       WHERE seq <= t.seq
       ) total
FROM (
   SELECT TOP 10 seq
       ,value
   FROM Table_1
   ORDER BY seq DESC
   ) t
ORDER BY seq DESC

--(10 rows affected)
--Table 'Table_1'. Scan count 11, logical reads 26083, physical reads 1, read-ahead reads 443, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 1422 ms,  elapsed time = 1621 ms.

我的問題是：

• 為什麼第一次嘗試的查詢比第二次慢？
• 如何進一步提高性能？我還可以更改架構。

為了清楚起見，兩個查詢都返回與下面相同的結果。

我建議使用更多數據進行測試，以更好地了解正在發生的事情並了解不同方法的執行情況。我將 1600 萬行載入到具有相同結構的表中。您可以在此答案的底部找到填充表格的程式碼。

以下方法在我的機器上需要 19 秒：

SELECT TOP (10) seq
   ,value
   ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

實際計劃在這裡。大部分時間都花在計算總和和排序上。令人擔憂的是，查詢計劃幾乎完成了整個結果集的所有工作，並過濾到您最後請求的 10 行。此查詢的執行時間隨表的大小而不是結果集的大小而變化。

這個選項在我的機器上需要 23 秒：

SELECT *
   ,(
       SELECT SUM(value)
       FROM dbo.[Table_1_BIG]
       WHERE seq <= t.seq
       ) total
FROM (
   SELECT TOP (10) seq
       ,value
   FROM dbo.[Table_1_BIG]
   ORDER BY seq DESC
   ) t
ORDER BY seq DESC;

實際計劃在這裡。這種方法隨著請求的行數和表的大小而擴展。從表中讀取了近 1.6 億行：

要獲得正確的結果，您必須對整個表的行求和。理想情況下，您只需執行一次此求和。如果您改變處理問題的方式，就有可能做到這一點。您可以計算整個表的總和，然後從結果集中的行中減去執行總計。這使您可以找到第 N 行的總和。一種方法：

SELECT TOP (10) seq
,value
, [value]
   - SUM([value]) OVER (ORDER BY seq DESC ROWS UNBOUNDED PRECEDING)
   + (SELECT SUM([value]) FROM dbo.[Table_1_BIG]) AS total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

實際計劃在這裡。新查詢在我的機器上執行 644 毫秒。該表被掃描一次以獲得完整的總數，然後為結果集中的每一行讀取額外的行。沒有排序，幾乎所有時間都花在計算計劃並行部分的總和上：

如果您希望此查詢更快，您只需優化計算完整總和的部分。上面的查詢執行聚集索引掃描。聚集索引包括所有列，但您只需要該[value]列。一種選擇是在該列上創建一個非聚集索引。另一種選擇是在該列上創建非聚集列儲存索引。兩者都會提高性能。如果您在 Enterprise 上，一個不錯的選擇是創建一個索引視圖，如下所示：

CREATE OR ALTER VIEW dbo.Table_1_BIG__SUM
WITH SCHEMABINDING
AS
SELECT SUM([value]) SUM_VALUE
, COUNT_BIG(*) FOR_U
FROM dbo.[Table_1_BIG];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON dbo.Table_1_BIG__SUM (SUM_VALUE);

此視圖返回單行，因此幾乎不佔用空間。進行 DML 時會受到懲罰，但與索引維護應該沒有太大區別。使用索引視圖，查詢現在需要 0 毫秒：

實際計劃在這裡。這種方法最好的部分是執行時不會因表的大小而改變。唯一重要的是返回了多少行。例如，如果您獲得前 10000 行，則查詢現在需要 18 毫秒才能執行。

填充表格的程式碼：

DROP TABLE IF EXISTS dbo.[Table_1_BIG];

CREATE TABLE dbo.[Table_1_BIG] (
   [seq] [int] NOT NULL,
   [value] [bigint] NOT NULL
);

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);

INSERT INTO #t WITH (TABLOCK)
SELECT TOP (4000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.[Table_1_BIG] WITH (TABLOCK)
SELECT t1.ID * 4000 + t2.ID, 8 * t2.ID + t1.ID
FROM (SELECT TOP (4000) ID FROM #t) t1
CROSS JOIN #t t2;

ALTER TABLE dbo.[Table_1_BIG]
ADD CONSTRAINT [PK_Table_1] PRIMARY KEY ([seq]);

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