如何提高排序連接的性能?
這感覺像是一個常見的問題,我會理解它是否已關閉,但如果是這樣,請建議我可以問的更好的地方。我有以下兩個感興趣的表:
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;