sql-server - Sql Server 删除和合并性能

Question

我有一个包含一些买入/卖出数据的表格，其中有大约 800 万条记录:

CREATE TABLE [dbo].[Transactions](
[id] [int] IDENTITY(1,1) NOT NULL,
[itemId] [bigint] NOT NULL,
[dt] [datetime] NOT NULL,
[count] [int] NOT NULL,
[price] [float] NOT NULL,
[platform] [char](1) NOT NULL
) ON [PRIMARY]

每 X 分钟，我的程序都会为每个 itemId 获取新交易，我需要更新它。我的第一个解决方案是两步 DELETE+INSERT:

delete from Transactions where platform=@platform and itemid=@itemid
insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price)
[...]
insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price)

问题是，这个 DELETE 语句平均需要 5 秒。太长了。

我发现的第二个解决方案是使用 MERGE。我创建了这样的存储过程，它采用表值参数:

CREATE PROCEDURE [dbo].[sp_updateTransactions]
@Table dbo.tp_Transactions readonly,
@itemId bigint,
@platform char(1)
AS
BEGIN
MERGE Transactions AS TARGET
USING @Table AS SOURCE  
ON (    
TARGET.[itemId] = SOURCE.[itemId] AND
TARGET.[platform] = SOURCE.[platform] AND 
TARGET.[dt] = SOURCE.[dt] AND 
TARGET.[count] = SOURCE.[count] AND
TARGET.[price] = SOURCE.[price] ) 


WHEN NOT MATCHED BY TARGET THEN 
INSERT VALUES (SOURCE.[itemId], 
                SOURCE.[dt],
                SOURCE.[count],
                SOURCE.[price],
                SOURCE.[platform])

WHEN NOT MATCHED BY SOURCE AND TARGET.[itemId] = @itemId AND TARGET.[platform] = @platform THEN 
DELETE;

END

对于包含 70k 条记录的表，此过程大约需要 7 秒。因此，对于 8M，可能需要几分钟时间。瓶颈是“当不匹配时”——当我评论这一行时，这个过程平均运行 0.01 秒。

那么问题来了:如何提高delete语句的性能？

需要删除以确保该表不包含在应用程序中删除的事务。但在真实情况下，这种情况很少发生，10000 次事务更新中删除记录的真正需求小于 1。

我的理论解决方法是创建额外的列，如“transactionDeleted bit”并使用 UPDATE 而不是 DELETE，然后每 X 分钟或几小时通过批处理作业清理表并执行

delete from transactions where transactionDeleted=1

它应该更快，但我需要更新应用程序其他部分的所有 SELECT 语句，仅使用 transactionDeleted=0 记录，因此它也可能影响应用程序性能。

你知道更好的解决方案吗？

更新:当前索引:

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[Transactions] 
(
[platform] ASC,
[ItemId] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]


CONSTRAINT [IX2] UNIQUE NONCLUSTERED 
(
[ItemId] DESC,
[count] ASC,
[dt] DESC,
[platform] ASC,
[price] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Answer 1

好的，这也是另一种方法。对于类似的问题(大扫描 WHEN NOT MATCHED BY SOURCE 然后 DELETE)，我将 MERGE 执行时间从 806ms 减少到 6ms!

上述问题的一个问题是“WHEN NOT MATCHED BY SOURCE”子句正在扫描整个 TARGET 表。

这不是那么明显，但 Microsoft 允许在执行合并之前(通过使用 CTE)过滤 TARGET 表。所以在我的例子中，TARGET 行从 250K 减少到少于 10 行。巨大差距。

假设上述问题适用于由 @itemid 和 @platform 过滤的 TARGET，那么 MERGE 代码将如下所示。上面对索引的更改也将有助于这种逻辑。

WITH Transactions_CTE (itemId
                        ,dt
                        ,count
                        ,price
                        ,platform
                        )
AS
-- Define the CTE query that will reduce the size of the TARGET table.  
(  
    SELECT itemId
        ,dt
        ,count
        ,price
        ,platform
    FROM Transactions  
    WHERE itemId = @itemId
      AND platform = @platform  
)  
MERGE Transactions_CTE AS TARGET
USING @Table AS SOURCE
    ON (
        TARGET.[itemId] = SOURCE.[itemId]
        AND TARGET.[platform] = SOURCE.[platform]
        AND TARGET.[dt] = SOURCE.[dt]
        AND TARGET.[count] = SOURCE.[count]
        AND TARGET.[price] = SOURCE.[price]
        )
WHEN NOT MATCHED BY TARGET  THEN
        INSERT
        VALUES (
            SOURCE.[itemId]
            ,SOURCE.[dt]
            ,SOURCE.[count]
            ,SOURCE.[price]
            ,SOURCE.[platform]
            )
WHEN NOT MATCHED BY SOURCE THEN
        DELETE;