May 13, 2009 at 4:06 pm
Can someone point me to a resource that explains all (or the key) factors that can slow down a DELETE statement? I am most interested in factors directly tied to the table itself (size, columns, records, constraints, etc), but also interested in indirect factors as well.
I don't have a specific problem that I need addressed, I just want to expand my understanding generally as I've run into problems in this area in the past.
Thanks in advance. -Carleton
May 13, 2009 at 4:17 pm
I think you need to be much more specific!
Deleting one row on a unique clustered index .. really quick
Deleting one row on a nonclustered index .. slightly slower
Deleting one row with no indexes on a big table .. awful
Basically you need to hit indexes and unique clustered indexes are best but there is a whole lot more to it than that!
Can you ask a more specific question?
Tim
.
May 13, 2009 at 4:34 pm
Delete statements are logged to the transaction log, and thus slower than a TRUNCATE for example which is not logged.
The speed of a delete will depend upon many things. How much data are you deleting? Is the delete statement using a Index, PK, etc. to access the data via the WHERE clause? The DELETE statement will also have to obtain an exclusive lock on the table and thus blocking will occur so it will block other transactions, etc.
That's about the 10,000 ft view.
A.J.
DBA with an attitude
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply