在 Azure SQL 数据库(SQL2019 兼容)上,我有一个 ETL 进程,它以 DeltaTrack 模式填充 HISTORY 表。
在 Proc 中,有一个对 HISTORY 表的更新,查询引擎正在使用 SORT,但我有一个应该覆盖它的索引。
此 UPDATE 的用例是针对现有行,自从该行首次添加到 HISTORY 表以来,我们已向摄取添加了额外的列。
这种排序会导致我们更大/更宽的表上的更新速度极其缓慢。
如何调整索引或查询以删除查询 3中的排序?
这是根据京东要求更新的 执行计划
这是 DDL。
DROP TABLE IF EXISTS dbo.STAGE;
GO
CREATE TABLE dbo.STAGE
(
Id varchar(18) NULL,
CreatedDate varchar(4000) NULL,
LastModifiedDate varchar(4000) NULL,
LastReferencedDate varchar(4000) NULL,
[Name] varchar(4000) NULL,
OwnerId varchar(4000) NULL,
SystemTimestamp datetime2(7) NULL
)
GO
DROP TABLE IF EXISTS dbo.HISTORY;
GO
CREATE TABLE dbo.HISTORY
(
HistoryRecordId int IDENTITY(1,1) NOT NULL,
[Hash] binary(64) NOT NULL,
[IsActive] BIT NOT NULL ,
ActiveFromDateTime datetime2(7) NOT NULL,
ActiveToDateTime datetime2(7) NOT NULL,
Id varchar(18) NOT NULL,
CreatedDate datetime2(7) NULL,
LastModifiedDate datetime2(7) NULL,
LastReferencedDate datetime2(7) NULL,
[Name] varchar(80) NULL,
OwnerId varchar(18) NULL,
SystemTimestamp datetime2(7) NULL
)
GO
CREATE UNIQUE CLUSTERED INDEX [CL__HISTORY] ON dbo.HISTORY
(
Id ,
[ActiveToDateTime] ASC,
[IsActive] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX__HISTORY_IsActive] ON dbo.HISTORY
(
[Id] ASC
)
INCLUDE([IsActive],[ActiveToDateTime])
GO
DROP TABLE IF EXISTS #updates;
GO
WITH src AS (
SELECT
CONVERT(VARCHAR(18), t.[Id]) AS [Id]
, CONVERT(DATETIME2, t.[CreatedDate]) AS [CreatedDate]
, CONVERT(DATETIME2, t.[LastModifiedDate]) AS [LastModifiedDate]
, CONVERT(DATETIME2, t.[LastReferencedDate]) AS [LastReferencedDate]
, CONVERT(VARCHAR(80), t.[Name]) AS [Name]
, CONVERT(VARCHAR(18), t.[OwnerId]) AS [OwnerId]
, CONVERT(DATETIME2, t.SystemTimestamp) AS SystemTimestamp
, dgst.[Hash]
, CONVERT(DATETIME2, SystemTimestamp) AS [ActiveFromDateTime]
, RN = ROW_NUMBER() OVER (
PARTITION BY
t.[Id]
ORDER BY CONVERT(DATETIME2, SystemTimestamp) DESC
)
FROM dbo.STAGE t
OUTER APPLY (
SELECT
CAST(HASHBYTES('SHA2_256',
COALESCE(CAST([CreatedDate] AS NVARCHAR(4000)), N'')
+ N'||' + COALESCE(CAST([LastModifiedDate] AS NVARCHAR(4000)), N'')
+ N'||' + COALESCE(CAST([LastReferencedDate] AS NVARCHAR(4000)), N'')
+ N'||' + COALESCE(CAST([Name] AS NVARCHAR(4000)), N'')
+ N'||' + COALESCE(CAST([OwnerId] AS NVARCHAR(4000)), N'')
+ N'||' + COALESCE(CAST(SystemTimestamp AS NVARCHAR(4000)), N'')
) AS BINARY(64)) AS [Hash]
) dgst
), tgt AS (
SELECT *
FROM dbo.HISTORY t
WHERE t.[ActiveToDateTime] > GETUTCDATE()
AND 1 = 1
)
SELECT
tgt.HistoryRecordId
, src.*
INTO #updates
FROM src
LEFT JOIN tgt
ON tgt.[Id] = src.[Id] WHERE src.RN = 1;
GO
--Create index on temp table (#updates)
CREATE NONCLUSTERED INDEX NCCI_#updates__Kimble_HISTORY_ForecastStatus
ON #updates ( [Id] , ActiveFromDateTime, [Hash] );
GO
UPDATE tgt
SET
tgt.[Hash] = src.[Hash]
, tgt.IsActive = 1
, tgt.[CreatedDate] = src.[CreatedDate]
, tgt.[LastModifiedDate] = src.[LastModifiedDate]
, tgt.[LastReferencedDate] = src.[LastReferencedDate]
, tgt.[Name] = src.[Name]
, tgt.[OwnerId] = src.[OwnerId]
, tgt.SystemTimestamp = src.SystemTimestamp
FROM dbo.HISTORY tgt
INNER JOIN #updates src
ON tgt.[Id] = src.[Id]
AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime]
AND tgt.[Hash] <> src.[Hash] ;
GO