根據變更日誌計算庫存數量

假設您有以下表結構：

LogId | ProductId | FromPositionId | ToPositionId | Date                 | Quantity
-----------------------------------------------------------------------------------
1     | 123       | 0              | 10002        | 2018-01-01 08:10:22  | 5
2     | 123       | 0              | 10003        | 2018-01-03 15:15:10  | 9
3     | 123       | 10002          | 10004        | 2018-01-07 21:08:56  | 3
4     | 123       | 10004          | 0            | 2018-02-09 10:03:23  | 1

FromPositionId並且ToPositionId是股票頭寸。一些職位 ID:s 有特殊含義，例如0. from 或 to 的事件0表示創建或刪除庫存。From0可以是交貨的庫存，to0可以是發貨的訂單。

該表目前包含大約 550 萬行。我們使用如下所示的查詢計算每個產品的庫存值並按計劃將其定位到記憶體表中：

WITH t AS
(
   SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId 
   FROM ProductPositionLog
   GROUP BY ToPositionId, ProductId
   UNION
   SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId 
   FROM ProductPositionLog
   GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0

即使這在合理的時間內完成（大約 20 秒），我覺得這是一種計算股票價值的非常低效的方法。我們很少在這個表中做除了INSERT:s 之外的任何事情，但有時我們會因為生成這些行的人的錯誤而手動調整數量或刪除一行。

我有一個想法，在一個單獨的表中創建“檢查點”，計算到特定時間點的值，並在創建我們的庫存數量記憶體表時將其用作起始值：

ProductId | PositionId | Date                | Quantity
-------------------------------------------------------
123       | 10002      | 2018-01-07 21:08:56 | 2

我們有時更改行的事實對此提出了問題，在這種情況下，我們還必須記住刪除在我們更改日誌行之後創建的任何檢查點。這可以通過直到現在才計算檢查點來解決，但是在從現在到最後一個檢查點之間留出一個月的時間（我們很少在很早之前進行更改）。

我們有時需要更改行的事實很難避免，我希望仍然能夠這樣做，它沒有顯示在這個結構中，但日誌事件有時與其他表中的其他記錄相關聯，並添加另一個日誌行有時無法獲得正確的數量。

可以想像，日誌表增長得非常快，計算時間只會隨著時間的推移而增加。

所以對於我的問題，你將如何解決這個問題？有沒有更有效的方法來計算目前股票價值？我對檢查站的想法好不好？

我們正在執行 SQL Server 2014 Web (12.0.5511)

執行計劃：https ://www.brentozar.com/pastetheplan/?id=Bk8gyc68Q

上面我其實給錯了執行時間，20s是記憶體完全更新所用的時間。此查詢大約需要 6-10 秒才能執行（我創建此查詢計劃時為 8 秒）。此查詢中還有一個原始問題中沒有的連接。

有時，您只需稍作調整即可提高查詢性能，而不是更改整個查詢。我注意到在您的實際查詢計劃中，您的查詢在三個地方溢出到 tempdb。這是一個例子：

解決這些 tempdb 溢出可能會提高性能。如果Quantity始終為非負數，那麼您可以將其替換UNION為UNION ALL可能會將雜湊聯合運算符更改為不需要記憶體授予的其他內容。您的其他 tempdb 溢出是由基數估計問題引起的。您使用的是 SQL Server 2014 並使用新的 CE，因此可能難以改進基數估計，因為查詢優化器不會使用多列統計資訊。作為快速修復，請考慮使用SQL Server 2014 SP2MIN_MEMORY_GRANT中提供的查詢提示. 您的查詢的記憶體授權僅為 49104 KB，最大可用授權為 5054840 KB，因此希望提高它不會對並發性產生太大影響。10% 是一個合理的起始猜測，但您可能需要根據您的硬體和數據進行調整併完成。綜上所述，您的查詢可能如下所示：

WITH t AS
(
   SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId 
   FROM ProductPositionLog
   GROUP BY ToPositionId, ProductId
   UNION ALL
   SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId 
   FROM ProductPositionLog
   GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0
OPTION (MIN_GRANT_PERCENT = 10);

如果您希望進一步提高性能，我建議您嘗試索引視圖，而不是建構和維護您自己的檢查點表。與涉及您自己的物化表或觸發器的自定義解決方案相比，索引視圖更容易獲得正確的結果。它們將為所有 DML 操作增加少量成本，但它可能允許您刪除目前擁有的一些非聚集索引。該產品的網路版似乎支持索引視圖。

索引視圖有一些限制，因此您需要創建一對。下面是一個範例實現，以及我用於測試的假數據：

CREATE TABLE dbo.ProductPositionLog (
   LogId BIGINT NOT NULL,
   ProductId BIGINT NOT NULL,
   FromPositionId BIGINT NOT NULL,
   ToPositionId BIGINT NOT NULL,
   Quantity INT NOT NULL,
   FILLER VARCHAR(20),
   PRIMARY KEY (LogId)
);

INSERT INTO dbo.ProductPositionLog WITH (TABLOCK)
SELECT RN, RN % 100, RN % 3999, 3998 - (RN % 3999), RN % 10, REPLICATE('Z', 20)
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) q;

CREATE INDEX NCI1 ON dbo.ProductPositionLog (ToPositionId, ProductId) INCLUDE (Quantity);
CREATE INDEX NCI2 ON dbo.ProductPositionLog (FromPositionId, ProductId) INCLUDE (Quantity);

GO    

CREATE VIEW ProductPositionLog_1
WITH SCHEMABINDING  
AS  
  SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
   FROM dbo.ProductPositionLog
   WHERE ToPositionId <> 0
   GROUP BY ToPositionId, ProductId
GO  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
   ON ProductPositionLog_1 (PositionId, ProductId);  
GO  

CREATE VIEW ProductPositionLog_2
WITH SCHEMABINDING  
AS  
  SELECT FromPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
   FROM dbo.ProductPositionLog
   WHERE FromPositionId <> 0
   GROUP BY FromPositionId, ProductId
GO  

CREATE UNIQUE CLUSTERED INDEX IDX_V2   
   ON ProductPositionLog_2 (PositionId, ProductId);  
GO  

如果沒有索引視圖，查詢大約需要 2.7 秒才能在我的機器上完成。我得到了一個與你類似的計劃，除了我的連續執行：

我相信您需要使用NOEXPAND提示查詢索引視圖，因為您不在企業版上。這是一種方法：

WITH t AS
(
   SELECT PositionId, Quantity, ProductId 
   FROM ProductPositionLog_1 WITH (NOEXPAND)
   UNION ALL
   SELECT PositionId, Quantity, ProductId 
   FROM ProductPositionLog_2 WITH (NOEXPAND)
)
SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0;

這個查詢有一個更簡單的計劃，在我的機器上完成不到 400 毫秒：

最好的部分是您不必更改任何將數據載入到ProductPositionLog表中的應用程式碼。您只需驗證這對索引視圖的 DML 成本是否可接受。

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