如何提高排序連接的性能？

這感覺像是一個常見的問題，我會理解它是否已關閉，但如果是這樣，請建議我可以問的更好的地方。我有以下兩個感興趣的表：

CREATE TABLE [dbo].[Sessions]
(
   [Id] [int] PRIMARY KEY,
   [DateConnected] [datetime] NOT NULL,
   [Origin] [nvarchar](max) NULL,
   [TrackerId] [int] NULL,
   [Imei] [nvarchar](max) NULL,
   [Sim] [nvarchar](max) NULL,
   [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
   [Id] [int] PRIMARY KEY,
   [RequestId] [int] NULL,
   [SessionId] [int] NOT NULL,
   [DateProcessed] [datetime] NOT NULL,
   [Direction] [int] NOT NULL,
   [Sequence] [int] NOT NULL,
   [Acknowledgement] [int] NOT NULL,
   [DateRecorded] [datetime] NOT NULL,
   [Version] [tinyint] NOT NULL,
   [Command] [tinyint] NOT NULL,
   [Flags] [tinyint] NOT NULL,
   [Checksum] [tinyint] NOT NULL,
   [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
   [TrackerId] ASC,
   [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
   [SessionId] ASC,
   [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

我最常見的查詢，也是最昂貴的（現在經常超時）涉及列出特定跟踪器的所有數據包傳輸。

DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

起初這很好，但現在有很多數據，它已經放慢了速度。我今天嘗試獲取查詢計劃花了 2 分鐘，並表明它將使用 table scan，而不是我創建的索引。即使我強制索引，它仍然很慢。

相比之下，如果我首先選擇一個會話，並且只搜尋該會話中記錄的數據包傳輸，則查詢使用索引並且速度非常快。

我加快查詢速度的最成功嘗試是首先按會話 ID 對結果進行排序，然後按處理日期對結果進行排序，以匹配索引順序。雖然這在技術上並不總是正確的，但它是可以接受的。但是，即使這樣也開始超時，我覺得我對如何使JOIN速度更快的理解有問題。

我可以做些什麼來提高這個查詢的性能？

使用DATETIME變數而不是查詢DATETIME2已經簡化了查詢計劃，但是它仍然很慢。

• 會話有 265,929 行
• PacketTransmissions 有 32,916,233 行

平均每個會話有 123.7 個數據包。

• 一些會話是針對未註冊設備的，因此它們創建一個會話，發送一到三個數據包，然後會話被伺服器拒絕。
• 我通常會調試已註冊的設備，因此每個會話的實際數據包數量要高得多，每個會話在 300 到 5000 個數據包之間
• 如果有連接，一些跟踪器可能會一次保持同一個會話一個月

我過去在更改聚集索引以使用非順序鍵方面有過不好的經歷。它會導致大量的亂序寫入和頁面拆分，並且插入性能顯著下降。

實際執行計劃的問題是我不想在最大 DTU 下執行數據庫長達一個小時，並且可能同時插入失敗。

也許這很瘋狂，但我喜歡每隔一段時間嘗試一些藍天思維，所以我會考慮將TrackerId列添加到dbo.PacketTransmissions表中以完全避免連接。顯然，這意味著您需要修改表的行插入過程，這可能可行，也可能不可行。

但是，這種變化，結合了一個簡單的索引：

CREATE INDEX IX_PacketTransmissions ON dbo.PacketTransmissions
(
   TrackerId ASC
   , DateProcessed ASC
) 
INCLUDE (Id); --not strictly required, since the primary key 
             --is always included in every non-clustered index
             --I include them just to be explicit

使用普通索引查找創建查詢計劃，並結合對返回的每一行的鍵查找。如：

為了測試這一點，我創建了一個最低限度完整的可驗證範例：

USE tempdb;

IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL
DROP TABLE dbo.[Sessions];
IF OBJECT_ID(N'dbo.PacketTransmissions', N'U') IS NOT NULL
DROP TABLE dbo.PacketTransmissions;
GO

CREATE TABLE [dbo].[Sessions]
(
     [Id] int 
       CONSTRAINT PK_Sessions
       PRIMARY KEY CLUSTERED
   , [DateConnected] datetime NOT NULL
   , [Origin] nvarchar(max) NULL
   , [TrackerId] int NULL
   , [Imei] nvarchar(max) NULL
   , [Sim] nvarchar(max) NULL
   , [ProtocolVersion] tinyint NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
     [Id] int 
       CONSTRAINT PK_PacketTransmissions 
       PRIMARY KEY CLUSTERED
   , [RequestId] int NULL
   , [SessionId] int NOT NULL
   , [DateProcessed] datetime NOT NULL
   , [Direction] int NOT NULL
   , [Sequence] int NOT NULL
   , [Acknowledgement] int NOT NULL
   , [DateRecorded] datetime NOT NULL
   , [Version] tinyint NOT NULL
   , [Command] tinyint NOT NULL
   , [Flags] tinyint NOT NULL
   , [Checksum] tinyint NOT NULL
   , [Data] varbinary(max) NULL
   , [TrackerId] int NULL
)
GO

INSERT INTO dbo.[Sessions] (Id, DateConnected, Origin, TrackerId, Imei, Sim, ProtocolVersion)
SELECT ROW_NUMBER() OVER (ORDER BY sc1.id)
   , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
   , CONVERT(nvarchar(max), CRYPT_GEN_RANDOM(128))
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
   , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
   , CONVERT(tinyint, CRYPT_GEN_RANDOM(1))
FROM sys.syscolumns sc1
   CROSS JOIN sys.syscolumns sc2;

INSERT INTO dbo.PacketTransmissions (Id, RequestId, SessionId, DateProcessed, Direction, Sequence, Acknowledgement, DateRecorded, Version, Command, Flags, Checksum, Data, TrackerId)
SELECT ROW_NUMBER() OVER (ORDER BY s.Id)
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(int, CRYPT_GEN_RANDOM(3))
   , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(int, CRYPT_GEN_RANDOM(2))
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CONVERT(int, CRYPT_GEN_RANDOM(1))
   , CRYPT_GEN_RANDOM(128)
   , s.TrackerId
FROM dbo.[Sessions] s
   CROSS JOIN (SELECT v.n
   FROM (VALUES (0), (1))v(n)) v;
GO

在我的系統上，這會創建大約 700,000 個會話行，並使傳輸行數翻倍。

然後查詢變為：

DECLARE @TrackerId int = 100;
DECLARE @StartDate datetime = '2017-03-10';
DECLARE @EndDate   datetime = '2017-03-12';

SELECT [PacketTransmissions].*
FROM [PacketTransmissions] 
WHERE [PacketTransmissions].[TrackerId] = @TrackerId
   AND [PacketTransmissions].[DateProcessed] > @StartDate
   AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC;

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