All geek questions in one place

DELETE SQL Server performance by clustered index, large table

I have a table with over 20 million rows, and when I do this:

DELETE [Table] WHERE ID = ?

It takes more than 40 seconds. The identifier column is grouped.

Is that what you expect? or is it possible to optimize?

+6
source share
3 answers

In addition to the exact JNK points included in their answer, one particular killer I saw is when you delete rows from a referenced table for one or more foreign key constraints and referenced columns (columns) to referenced tables (tables) are not indexed - you force a table scan for each of these tables before deletion can be accepted.

+14
source

It will depend on many factors that you are not talking about ...

How many lines are deleted? Other lines obviously mean more time.

Are there other indexes? . Each index must be updated, not just clustered. If you delete 10 indexes, it will take about 10 times (very rude).

Is there any other activity? If there are updates or insertions taking place there, it is very likely that there will be contention.

In addition, very generally speaking, the number of seconds an operation takes FAR depends on the hardware settings. If you ran this on a desktop computer compared to a server with a high-performance array and 12 cores, the wait will be very different.

+7
source

Also try deleting the data in the package. Example

 set rowcount 10000 delete [table] where id = ? while @@rowcount >0 begin delete [table] where id = ? end
0
source

Source: https://habr.com/ru/post/890919/

More articles:


All Articles