当表达到特定行数时，如何自动删除最旧的行？ [英] How to automate deletion of oldest rows when table reaches certain number of rows?

问题描述

当文件达到一定数量的行时，是否有办法自动删除事务日志文件中最旧的行？目前，我们只是经常手动删除最旧的行。但我们希望能够自动完成这项工作。这样做的原因是最终提高应用程序将事务写入此文件的性能。当文件超过大约60,000行时，应用程序的性能似乎会降低。

Is there a way to automate the deletion of the oldest rows in a transaction log file when the file reaches a certain number of rows? Currently, we are simply deleting the oldest rows manually every so often. But we would like to put something in place that will do this automatically. The reason for doing this is to ultimately improve the application''s performance that writes the transactions to this file. When the file gets to be over about 60,000 rows, performance of the application seems to degrade.

推荐答案

此表中是否有唯一ID？

Do you have unique ID in this table?

是的，有一个唯一的RecordID

Yes, there is a unique RecordID

1。创建测试环境


[PHP] select * into #temp_table from log_table [/ PHP]


2. [PHP] Select * from＃ temp_table其中ID不在（通过ID desc从#temp_table顺序中选择Top 60000 ID）[/ PHP]

- 您可以使用日期或其他订单标准确保它是desc。我们的想法是选择最后60000条记录并将其从删除中排除

- 检查这些是否是您要删除的记录。

3. [PHP]从#temp_table删除，其中ID不在（通过ID desc从#temp_table顺序中选择Top 60000 ID）[/ PHP]

4.移动所有内容在生产中，如果它让你满意。


Godd Luck。

1. Create test environment

[PHP]select * into #temp_table from log_table [/PHP]

2. [PHP]Select * from #temp_table where ID not in (Select Top 60000 ID from #temp_table order by ID desc) [/PHP]
-- you can use date or other order criteria make sure it is desc. The idea is to select last 60000 records and exclude them from delete
-- Check if these are the records you want to delete.
3. [PHP]Delete from #temp_table where ID not in (Select Top 60000 ID from #temp_table order by ID desc)[/PHP]
4. Move everything in production if it worked to your satisfaction.


Godd Luck.

这篇关于当表达到特定行数时，如何自动删除最旧的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！