每10分钟删除记录的有效方法
当前的问题
需要每 10 分钟从 SQL Server 数据库表中删除几千条记录。这是旧记录清理的一部分。
正在考虑的解决方案
- .Net 服务正在运行以实现其他一些功能。相同的服务可以与计时器一起使用来在数据库上执行 SQL 删除命令。
- SQL 服务器作业
- 触发器
提供解决方案的关键考虑因素
- 我们的产品是部署在不同客户端位置的 Web 产品。我们希望运营开销最小化，因为部署资源的技术技能非常有限，而且我们还希望确保我们的产品几乎没有配置要求。
- 性能非常重要，因为它在实时事务数据库上。
Problem at hand
Need to delete some few thousand records every 10 minutes from a SQL Server database table.This is part of cleanup for older records.
Solutions under consideration
- There's .Net Service running for some other functionality. Same service can be used with a timer to execute SQL delete command on db.
- SQL server job
- Trigger
Key consideration for providing solution
- Ours is a web product which gets deployed at different client locations. we want minimal operational overhead as resources doing deployment are very limited technical skill and we also want to make sure that there's less to none configuration requirement for our Product.
- Performance is very important, as it on live transactional database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这听起来正是 SQL Server 作业旨在提供的工作类型；数据库维护。
计划作业可以执行基本的 T-SQL 语句，该语句将按照您希望其运行的任何计划删除您不再需要的记录。作业创建可以编写为标准部署脚本的一部分，这应该可以消除部署成本。
此外，通过利用 SQL Server 的既定部分，您可以利用其他数据库管理员的知识，他们将了解 SQL 作业并能够管理它们。
This sounds like exactly the sort of work that a SQL Server job was intended to provide; database maintenance.
A scheduled job can execute a basic T-SQL statement that will delete the records you don't want any more, on whatever schedule you want it to run on. The job creation can be scripted to be part of your standard deployment scripts, which should negate the deployment costs.
Additionally, by utilizing an established part of SQL Server, you capitalize on the knowledge of other database administrators that will understand SQL jobs and be able to manage them.
我不会使用触发器...并坚持使用 SQL Server DTS 或 SSIS。显然，您将需要某种标识符，因此我将使用带有索引的时间戳列...如果不需要，只需每晚触发一次 TRUNCATE 即可。
I would not use a trigger...and stick with SQL Server DTS or SSIS. Obviously you will need some kind of identifier so I would use a timestamp column with an index...if that's not required just fire off a TRUNCATE once nightly.
删除的效率来自于索引，与定时器如何触发无关。通过范围扫描轻松识别“旧”记录非常重要。如果 DELETE 必须扫描整个表才能找到这些“旧”记录，它将阻止所有其他活动。通常在这种情况下，表首先按日期时间值聚集，并且如果需要，唯一主键将委托给非聚集索引。
现在如何弹出计时器，您实际上有三种选择：
SQL 代理作业是 10 分钟间隔的最佳选择。唯一的缺点是它不适用于 SQL Express 部署。如果这是一个问题，那么对话计时器和激活程序是可行的选择。
最后一个选项的缺点是应用程序必须正在运行，计时器才能触发删除。如果这不是一个问题（即，如果应用程序没有运行，记录不被删除并不重要），那么就可以了。请注意，ASP.Net 应用程序对于此类计时器来说是非常糟糕的主机，因为 IIS 和 ASP 可能会选择回收应用程序池并将其置于睡眠状态。
The efficiency of the delete comes from indexes, has nothing to do how the timer is triggered. It is very important that the 'old' records be easily identifiable by a range scan. If the DELETE has to scan the whole table to find these 'old' records, it will block all other activity. Usually in such cases the table is clustered by the datetime value first, and unique primary keys are delegated to a non-clustered index, if needed.
Now how to pop the timer, you really have three alternatives:
SQL Agent job is the best option for 10 minute intervals. Only drawback is that it does not work on SQL Express deployments. If that is a concern, then conversation timers and activated procedures are a viable alternative.
Last option has the disadvantage that the application must be running for the timer to trigger deletion. If this is not a concern (ie. if the application is not running, it doesn't matter that the records are not deleted) then is OK. Note that ASP.Net applications are very bad host for such timers, because of the way IIS and ASP may choose to recycle and put to sleep app pools.