级联删除性能:删除一行的 1-Many 行的最快方法是什么? [英] Cascade on delete performance: Whats the fastest way to delete a row its 1-Many rows?

问题描述

我有一个数据库，其中有一个父帐户"行，然后该行与另一个表具有 1-Many 关系，并且该表与另一个表具有 1-Many 关系.这大约有 6 个级别深(帐户在顶部).在最底部可能有数千(甚至可以超过 100k)行.在每个表上都有一个外键设置为在删除时级联.

I have a database in which there is a parent "Account" row that then has a 1-Many relationship with another table, and that table has a 1-Many relationship with another table. This goes on about 6 levels deep (with Account at the top). At the very bottom there could possibly be thousands (can even go beyond 100k) of rows. On each table there is a foreign key set to cascade on delete.

问题是，如果我尝试删除最顶行(帐户")，可能需要几分钟，有时甚至超过 10 分钟.是否有更快的方法来删除所有行(例如可能在单个删除语句中从下往上删除)或者几乎是级联的?

The issue is, that if I try to delete the very top row (an "Account"), it can take minutes, sometimes well over 10 minutes. Is there a faster way to delete all the rows (such as maybe going from the bottom up in individual delete statements) or is cascading pretty much it?

我使用的是 MSSQL 2005 &服务器使用 MSSQL 2008，使用 L2S 执行删除操作，不过如果速度更快，我可以使用 T-SQL 语句.

I am using MSSQL 2005 & MSSQL 2008 for the server, ans L2S to perform the delete, although i can use a T-SQL statement if it is faster.

我也试过从 SQL Management Studio 中进行删除操作，但花费的时间也一样长.

Ive tried doing the delete from the SQL Management Studio too, and that takes just as long.

编辑:我们尝试重新索引数据库，差异可以忽略不计，可能有一两分钟的差异.感谢您的所有回答，看来我将不得不开始编写一些代码来执行软删除！

edit: we have tried re-indexing the database, with negligible difference, maybe a minute or two difference. I appreciate all your answers, it looks like i am going to have to start writing some code to do soft deletes!

推荐答案

删除就是删除，如果要删除大量行(100k)，需要一段时间.

A delete is a delete, and if you want to delete massive amounts of rows (100k), it will take a while.

如果您执行软删除(例如将状态设置为D")，您可以运行一项作业，以便随着时间的推移以 1,000 左右为单位实际删除行，它可能更适合您.软删除应该只更新标题行，而且速度会非常快.不过，您需要对应用程序进行编码以忽略这些D"状态行及其子行.

If you do a soft delete (set a status to "D" for example) you can then run a job to actually delete the rows in batches of say 1,000 or so over time it may work better for you. The soft delete should update only the header row and would be very fast. You'd need to code your application to ignore these "D" status rows and their children though.

编辑进一步@Kane 的评论.您只能进行软删除，或者您可以进行软删除，然后进行批处理以进行实际删除(如果您真的想要).如果驱动器空间不是问题，我会坚持使用软删除.

EDIT To further @Kane's comment. you could only do a soft delete, or you could do a soft delete followed by a batch process to do the actual deletes if you really want to. I'd just stick with the soft deletes if drive space is not an issue.

这篇关于级联删除性能:删除一行的 1-Many 行的最快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！