优化 SQL Server 上的删除 [英] Optimizing Delete on SQL Server

问题描述

Delete 有时很慢，我经常需要优化它们以减少所需的时间.我一直在谷歌上搜索有关如何做到这一点的提示，我发现了各种各样的建议.我想知道您最喜欢和最有效的驯服删除野兽的技术，以及它们的工作方式和原因.

Deletes on sql server are sometimes slow and I've been often in need to optimize them in order to diminish the needed time. I've been googleing a bit looking for tips on how to do that, and I've found diverse suggestions. I'd like to know your favorite and most effective techinques to tame the delete beast, and how and why they work.

直到现在:

• 确保外键有索引

• be sure foreign keys have indexes

确保 where 条件被索引

be sure the where conditions are indexed

使用WITH ROWLOCK

销毁未使用的索引，删除，重建索引

destroy unused indexes, delete, rebuild the indexes

现在轮到你了.

推荐答案

您可能会对以下文章 Fast Ordered Delete Operations 感兴趣.

The following article, Fast Ordered Delete Operations may be of interest to you.

执行快速 SQL Server 删除操作

该解决方案侧重于利用视图来简化为批量删除操作生成的执行计划.这是通过引用给定表一次而不是两次来实现的，这反过来又减少了所需的 I/O 量.

The solution focuses on utilising a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.

这篇关于优化 SQL Server 上的删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！