SQL Server删除和合并性能 [英] Sql Server Delete and Merge performance
问题描述
我的表中包含一些买卖数据，其中包含约800万条记录:
I've table that contains some buy/sell data, with around 8M records in it:
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:
Every X mins my program gets new transactions for each itemId and I need to update it. My first solution is two step 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秒钟.太长了.
The problem is, that this DELETE statement takes average 5 seconds. It's much too long.
我发现的第二个解决方案是使用MERGE.我已经创建了这样的存储过程，wchich采用表值参数:
The second solution I found is to use MERGE. I've created such Stored Procedure, wchich takes Table-valued parameter:
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秒.
This procedure takes around 7 seconds with table with 70k records. So with 8M it would probably take few minutes. The bottleneck is "When not matched" - when I commented this line, this procedure runs on average 0,01 second.
所以问题是:如何提高delete语句的性能?
So the question is: how to improve perfomance of the delete statement?
需要删除以确保该表不包含已在应用程序中删除的事务.但是在实际情况下，这种情况很少发生，并且在10000个事务更新中删除记录的真正需求小于1.
Delete is needed to make sure, that table doesn't contains transaction that as been removed in application. But it real scenario it happens really rarely, ane the true need of deleting records is less than 1 on 10000 transaction updates.
我的理论解决方法是创建其他列，例如"transactionDeleted bit"，并使用UPDATE而不是DELETE，然后通过每X分钟或小时按批处理作业进行表清理并执行
My theoretical workaround is to create additional column like "transactionDeleted bit" and use UPDATE instead of DELETE, ane then make table cleanup by batch job every X minutes or hours and Execute
delete from transactions where transactionDeleted=1
它应该更快，但是我将需要更新应用程序其他部分中的所有SELECT语句，以仅使用transactionDeleted = 0记录，因此它也可能影响应用程序性能.
It should be faster, but I would need to update all SELECT statements in other parts of application, to use only transactionDeleted=0 records and so it also may afect application performance.
您知道更好的解决方案吗?
Do you know any better solution?
更新:当前索引:
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]
推荐答案
好的，这也是另一种方法.对于类似的问题(大扫描时，如果未按源匹配，则删除)，我将合并执行时间从806ms减少到了6ms！
OK, here is another approach also. For a similar problem (large scan WHEN NOT MATCHED BY SOURCE then DELETE) I reduced the MERGE execute time from 806ms to 6ms!
上述问题的一个问题是未按来源匹配时"子句正在扫描整个TARGET表.
One issue with the problem above is that the "WHEN NOT MATCHED BY SOURCE" clause is scanning the whole TARGET table.
不是很明显，但是Microsoft允许在合并之前先过滤TARGET表(通过使用CTE).因此，在我的情况下，TARGET行从250K减少到少于10行.大不同.
It is not that obvious but Microsoft allows the TARGET table to be filtered (by using a CTE) BEFORE doing the merge. So in my case the TARGET rows were reduced from 250K to less than 10 rows. BIG difference.
假设上述问题适用于使用@itemid和@platform过滤的TARGET，则MERGE代码如下所示.索引上面的更改也将有助于此逻辑.
Assuming that the above problem works with the TARGET being filtered by @itemid and @platform then the MERGE code would look like this. The changes above to the indexes would help this logic too.
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;
这篇关于SQL Server删除和合并性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
