如何優化此日誌記錄表上的查詢？

我正在嘗試為記錄事件的表優化表佈局。

日誌表包含三個相關列：Timestamp, ItemId, LocationId

每行表示在給定time的 ，在某個item已經看到某個location。

2017-01-01 10:00    Item A has been seen at location 1
2017-01-01 10:01    Item A has been seen at location 1
2017-01-01 11:00    Item B has been seen at location 1
2017-01-01 11:01    Item B has been seen at location 2
2017-01-01 11:02    Item A has been seen at location 2
2017-01-01 11:03    Item B has been seen at location 1

大約有 100 個不同的位置，每天 20.000 個新項目，每天 100 萬個事件和 14 天的日誌。

現在我需要對這些數據執行查詢，例如：

• 哪些項目在時間 ‘2017-01-01 11:00’ 位於位置 ‘1’

（= 哪些項目在時間 ‘2017-01-01 11:00’ 之前在位置 1 被看到，並且在出現後在其他地方沒有看到見於 1 但在 ‘2017-01-01 11:00’ 之前

要獲取此數據，我可以執行

SELECT DISTINCT  ItemId     
FROM events e1 
WHERE LocationId = 1
 AND e1.TimeStamp < '2017-01-01 11:00'
 AND NOT EXISTS (SELECT 1 FROM events e2
                 WHERE e2.LocationId <> e1.LocationId
                   AND e2.ItemId = e1.ItemId
                   AND e2.TimeStamp >= e1.TimeStamp
                   AND e2.TimeStamp <'2017-01-01 11:00')

目前，當數據庫負載為零時，此查詢大約需要 15 秒。目標是在 100 毫秒內執行這個查詢，負載很重。我認為目前的設計不可能做到這一點。

我有一個關於項目和位置的索引，以及一個關於時間戳的聚集索引

是否有表格佈局可以讓我更有效地執行此查詢？

或者是否有適用於現有表的查詢？

您可以嘗試不同的查詢：

SELECT ItemID
 FROM (SELECT ItemID
             ,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY TimeStamp DESC) rn
             ,LocationId
         FROM events
        WHERE TimeStamp < '2017-01-01 11:00'
      ) e1
 WHERE LocationId = 1
   AND rn = 1
;

沒有承諾這會做得更好（實際上可能更糟）；這只是一種不同的方法。

另外-如果有意義，您可能希望對可能的TimeStamp值設置下限；如果您可以在查找時間之前超過 12 小時忽略所有內容，則可能會消除大量行。

我無法重現該問題。我不確定我是否做錯了數據準備，或者我正在測試的機器與你的機器有很大不同。話雖如此，可能值得調整系統而不是查詢。

在我的表中，我有 1450 萬行、100 個不同的位置和 338k 不同的項目。每個項目每 90 分鐘移動一次，持續三天，之後停止移動。這是程式碼：

CREATE TABLE [events] (
   [TimeStamp] DATETIME NOT NULL,
   ItemId BIGINT NOT NULL,
   LocationId BIGINT NOT NULL,
   FILLER VARCHAR(60) NOT NULL
);

CREATE CLUSTERED INDEX CI_events ON [events] ([TimeStamp]);

INSERT INTO [events] WITH (TABLOCK)
SELECT *
FROM
(
   SELECT 
     DATEADD(MINUTE, 90 * t.cnt, DATEADD(SECOND, ItemId * (14 * 86400.) / (17. * 20000), '20161220')) [TimeStamp]
   , i.ItemId
   , 1 + ((1 + i.ItemId % 100) + 7 * t.cnt) % 100 LocationId
   , REPLICATE('Z', 30) FILLER
   FROM
   (
       SELECT TOP (17 * 20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ItemId
       FROM master..spt_values t1
       CROSS JOIN master..spt_values t2
   ) i
   CROSS JOIN (
       SELECT TOP (48) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) cnt
       FROM master..spt_values t1
   ) t
) t2
WHERE t2.[TimeStamp] >= '20161223'
OPTION (MAXDOP 1);


CREATE INDEX IX_events_Loc_Item ON [events] (LocationId, ItemId);

CREATE INDEX IX_events_Item_Loc ON [events] (ItemId, LocationId);

根據您的查詢計劃，您似乎在表上有另一個未提及的非聚集索引。我將假設它在位置和項目上。

您的查詢在我的機器上執行大約 250 毫秒：

SELECT DISTINCT ItemId     
FROM events e1 
WHERE LocationId = 1
 AND e1.[TimeStamp] < '2017-01-01 11:00'
 AND NOT EXISTS (SELECT 1 FROM [events] e2
                 WHERE e2.LocationId <> e1.LocationId
                   AND e2.ItemId = e1.ItemId
                   AND e2.[TimeStamp] >= e1.[TimeStamp]
                   AND e2.[TimeStamp] <'2017-01-01 11:00')
OPTION (MAXDOP 1);

查詢也可以寫成不使用連接：

SELECT ItemId
FROM events e1
WHERE e1.[TimeStamp] < '2017-01-01 11:00'
GROUP BY ItemId
HAVING MAX(CASE WHEN LocationID = 1 THEN ([TimeStamp]) ELSE NULL END) = MAX([TimeStamp])
OPTION (MAXDOP 1);

查詢計劃：

重寫並行性很好，但不太可能達到 100 毫秒的目標時間。它正在對 1000 萬行進行索引掃描。過濾器TimeStamp根本沒有足夠的選擇性。真的很難知道這是否會有所幫助，但您可以添加一個列，ItemId每次項目移動時都會增加一列。這將允許您搜尋更少的行以找到每個項目的下一個相關事件。這是我創建表的方式：

CREATE TABLE [events_with_id] (
   LocationId BIGINT NOT NULL,
   ItemId BIGINT NOT NULL,
   [TimeStamp] DATETIME NOT NULL,
   Item_move_id  BIGINT NOT NULL,
   FILLER VARCHAR(60) NOT NULL
);

CREATE CLUSTERED INDEX CI_events_with_id ON [events_with_id] ([TimeStamp] );

INSERT INTO [events_with_id] WITH (TABLOCK)
SELECT 
 LocationId
, ItemId
, [Timestamp]
, ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY [Timestamp])
, REPLICATE('Z', 30)
FROM [events];

CREATE INDEX IX_events_new_loc_start ON [events_with_id] (LocationId, [TimeStamp])
   INCLUDE (ItemId, Item_move_id);
CREATE UNIQUE INDEX IX_events_item_item_move ON [events_with_id] (ItemId, Item_move_id);

這是查詢：

SELECT DISTINCT ItemId     
FROM [events_with_id] e1 
WHERE LocationId = 1
 AND e1.[TimeStamp] < '2017-01-01 11:00'
 AND NOT EXISTS (SELECT 1 FROM [events_with_id] e2
                 WHERE e2.Item_move_id = e1.Item_move_id + 1
                   AND e2.ItemId = e1.ItemId
                   AND e2.[TimeStamp] <'2017-01-01 11:00')
OPTION (LOOP JOIN, MAXDOP 1);

沒有提示，我得到了一個表現不佳的合併連接。這個查詢在我的機器上執行了 175 毫秒，所以它比第一個查詢略有改進。

另一種選擇是包括表格中每一行的開始時間和結束時間。您必須保持更新，這比每次移動項目時簡單地插入一行更困難。這是最初填充它的程式碼：

CREATE TABLE [events_with_end] (
   LocationId BIGINT NOT NULL,
   ItemId BIGINT NOT NULL,
   [Start_TimeStamp] DATETIME NOT NULL,
   [End_TimeStamp] DATETIME NULL,
   FILLER VARCHAR(60) NOT NULL
);

CREATE CLUSTERED INDEX CI_events_new ON [events_with_end] ([Start_TimeStamp] );

INSERT INTO [events_with_end] WITH (TABLOCK)
SELECT LocationId, ItemId, [Timestamp], LEAD([Timestamp]) OVER (PARTITION BY ItemId ORDER BY [Timestamp]), REPLICATE('Z', 30)
FROM [events];

CREATE INDEX IX_events_new_loc_start ON [events_with_end] (LocationId, [Start_TimeStamp]) INCLUDE (ItemId, [End_TimeStamp]);

CREATE INDEX IX_events_new_loc_end ON [events_with_end] (LocationId, [End_TimeStamp]) INCLUDE (ItemId);

現在我可以進行更有針對性的搜尋，儘管 SQL Server 無法同時在開始時間和結束時間索引搜尋：

SELECT DISTINCT ItemId
FROM [events_with_end] e1
WHERE LocationID = 1 
AND e1.[Start_TimeStamp] < '2017-01-01 11:00'
AND (e1.[End_TimeStamp] > '2017-01-01 11:00' OR e1.[End_TimeStamp] IS NULL)
OPTION (MAXDOP 1);

該查詢使用IX_events_new_loc_end索引並在大約 10 毫秒內完成。如果我將過濾時間更改為更接近表的開頭 ( 2016-12-25 11:00)，那麼我將使用另一個索引：

查詢仍然在大約 10 毫秒內完成。目前尚不清楚是否有必要創建兩個索引。

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