存档和清除实时交易表 [英] Archive and purge live transaction table

问题描述

生产中的Log表，它接收来自各种应用程序的条目。 Log表每秒接收大约5000行。根据客户要求，他们希望运行每小时作业将所有记录复制到存档日志表并从日志表中删除所有复制的记录。这将在每秒5000行插入时发生。



我的方法：

The is Log table in production which receive entries from various applications. The Log table receives around 5000 rows per second. As per client requirement, they want to run a hourly job to copy all records to Archive Log table and delete all the copied records from Log table. This will happen while 5000 rows per second inserts are committing.

My approach:

DECLARE @startID AS integer
DECLARE @endID AS integer

SET @startID = (SELECT MIN(ID) FROM Log)
SET @endID   = (SELECT MAX(ID) FROM Log)
INSERT INTO ArchiveLog SELECT * FROM Log WHERE ID BETWEEN @startID AND @endID
Delete FROM LOG WHERE ID BETWEEN @startID AND @endID

什么会是快速优化的最佳方法吗？请建议。



提前致谢。

What would be the best possible approach to do this in an fast and optimized way? Please suggest.

Thanks in advance.

推荐答案

根据您提供的信息，您的方法似乎有效对我来说。我会做的事很少

• 确保ID是唯一的并编入索引
• 通常我不鼓励使用NOLOCK提示但在这种情况下，我认为这可能是合理的。例如，参见使用NOLOCK提示时应考虑的因素 [ ^ ]
• 如果删除花费太多时间并且事务开始阻止其他人在循环中运行删除并删除较小部分的记录。每次迭代都会提交删除。根据逻辑，您可能需要添加检查存档日志表中是否存在该行

Based on the information you provided your approach seems valid to me. Few things I would do

• Ensure that the ID is unique and indexed
• Typically I discourage the use of NOLOCK hint but in this case I believe it may be justified. See for example What should be considered when NOLOCK hint is used[^]
• If the deletion takes too much time and the transaction starts to block others run the deletion in a loop and delete smaller portions of records. With each iteration commit the deletion. Depending on the logic you may have to add check that the row exists in the archivelog table

这篇关于存档和清除实时交易表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！