下面的查詢組Snippets通過ChannelId并回傳UnreadSnippetCount。

為了確定UnreadSnippetCount,Channel被加入ChannelUsers以獲取User上次讀取的日期，Channel并使用它LastReadDate來將計數限制為在用戶上次讀取頻道后創建片段的行。

    SELECT c.Id, COUNT(s.Id) as [UnreadSnippetCount] 
    FROM Channels c
    INNER JOIN ChannelUsers cu
        ON cu.ChannelId = c.Id
    LEFT JOIN Snippets s
        ON cu.ChannelId = s.ChannelId
        AND s.CreatedByUserId <> @UserId
    WHERE cu.UserId = @UserId
    AND (cu.LastReadDate IS NULL OR s.CreatedDate > cu.LastReadDate)
    AND c.Id IN (select value from STRING_SPLIT(@ChannelIds, ','))

    GROUP BY c.Id

該查詢在邏輯上運行良好，但對于Channels具有大量Snippets(97691) 的查詢，該查詢可能需要 10 分鐘或更長時間才能回傳。

創建了以下索引：

CREATE NONCLUSTERED INDEX [IX_Snippets_CreatedDate] ON [dbo].[Snippets]
(
    [CreatedDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

更新：

查詢執行計劃（原始查詢）：

https://www.brentozar.com/pastetheplan/?id=B19sI105F

更新 2

按照建議將where子句移入join：

    SELECT c.Id, COUNT(s.Id) as [UnreadSnippetCount] 
    FROM Channels c
    INNER JOIN ChannelUsers cu
        ON cu.ChannelId = c.Id
    LEFT JOIN Snippets s
        ON cu.ChannelId = s.ChannelId
        AND s.CreatedByUserId <> @UserId
        AND s.CreatedDate > cu.LastReadDate
    WHERE cu.UserId = @UserId
    AND c.Id IN (select value from STRING_SPLIT(@ChannelIds, ',')

產生這個執行計劃：

https://www.brentozar.com/pastetheplan/?id=HkqwFk0ct

我可以使用更好的日期比較方法嗎？

更新 3 - 解決方案

指數

CREATE NONCLUSTERED INDEX [IX_Snippet_Created] ON [dbo].[Snippets]
  (ChannelId ASC, CreatedDate ASC) INCLUDE (CreatedByUserId);

存盤程序

ALTER PROCEDURE [dbo].[GetUnreadSnippetCounts2]
(
    @ChannelIds ChannelIdsType READONLY,
    @UserId nvarchar(36)
)
AS
    SET NOCOUNT ON

SELECT
    c.Id,
    COUNT(s.Id) as [UnreadSnippetCount] 
FROM Channels c
JOIN @ChannelIds cid
    ON cid.Id = c.Id
INNER JOIN ChannelUsers cu
    ON cu.ChannelId = c.Id
    AND cu.UserId = @UserId
JOIN Snippets s
    ON cu.ChannelId = s.ChannelId
    AND s.CreatedByUserId <> @UserId
    AND (cu.LastReadDate IS NULL OR s.CreatedDate > cu.LastReadDate)
GROUP BY c.Id;

這在邏輯上給出了正確的結果并快速回傳。

結果執行計劃：

https://www.brentozar.com/pastetheplan/?id=S1GwRCCcK

uj5u.com熱心網友回復：

我可以在查詢計劃中看到許多低效率。

1. 使用STRING_SPLIT意味著編譯器不知道回傳了多少個值，或者它們是唯一的，并且資料型別不匹配。理想情況下，您會傳入一個表值引數，但是如果您不能這樣做，那么另一種解決方案是將它們轉儲到表變數中

   DECLARE @tmp TABLE (Id int PRIMARY KEY);
   INSERT @tmp (Id)
   select value
   from STRING_SPLIT(@ChannelIds, ',')
   

2. 你需要更好的索引Snippets。我會建議以下

   CREATE NONCLUSTERED INDEX [IX_Snippet_Created] ON [dbo].[Snippets]
     (ChannelId ASC, CreatedDate ASC) INCLUDE (CreatedByUserId);
   

   放在CreatedByUserId鍵中是沒有意義的，因為它是一個不等式。把它放在INCLUDE

3. 正如您已經被告知的，最好將條件（對于左連接表）移到ON子句中。我不知道你是否還需要cu.LastReadDate IS NULL支票，我已經把它留在了。

4. 我必須說，我不清楚你的架構，但INNER JOIN ChannelUsers cu在這里感覺不對，也許它應該是LEFT JOIN? 如果沒有看到您的完整設定和所需的輸出，我無法進一步說。

SELECT
  c.Id,
  COUNT(s.Id) as [UnreadSnippetCount] 
FROM Channels c
JOIN @tmp t
    ON t.Id = c.Id
INNER JOIN ChannelUsers cu
    ON cu.ChannelId = c.Id
    AND cu.UserId = @UserId
LEFT JOIN Snippets s
    ON cu.ChannelId = s.ChannelId
    AND s.CreatedByUserId <> @UserId
    AND (cu.LastReadDate IS NULL OR s.CreatedDate > cu.LastReadDate)
GROUP BY c.Id;

標籤：sql sql-server

上一篇：SSISHH::MM:SS到秒，SSIS中的HH部分超過24

下一篇：在連接表中插入多個值