Slow delete statement

Simple delete query is taking more than 15 hrs. Table contains around 6 852 968 records.

Query is :

delete from wmcrs_mf_transactions where file_syscode = 56020                    

Find below for the Create table query of that table. Pls provide your valuable suggestions to close this issue. Thanks in advance.

more ▼

asked Oct 26, 2009 at 03:43 PM in Default

Arul 1 gravatar image

Arul 1
11 1 1 1

Please edit your question to include the create table statement. Also indicate whether file_syscode has an index and how many rows you think should be deleted by this statement.
Oct 26, 2009 at 04:04 PM graz ♦
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

nonclustered located on PRIMARY >> file_syscode

Well, you've got an index on that field, so it should be quick.

1) Make sure all indexes (or at the very least this one and the clsutered index) have been recently rebuilt.

2) Other than that it will be the Constraints - which all have to be checked. Rebuild the indexes that the Constraints are relying on if that has not been done recently.

more ▼

answered Oct 27, 2009 at 07:03 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

Just another thought, you said that you were expecting to delete 3K rows from a table that has 6.8M rows and you said was taking 15 hours, as pointed out the column is indexed.

When was the last time that the statistics were updated?

Are there any delete triggers on the table that might be causing issues?

more ▼

answered Nov 05, 2009 at 01:04 AM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(comments are locked)
10|1200 characters needed characters left

Any foreign keys to from this table?
I have the same problem. When I try to delete one record out of 200 million, it takes about 5 hours even if I use the PK column. When I disable/remove the FK constraints, it takes about 1 second.

more ▼

answered Jan 19, 2010 at 12:46 PM

Peso gravatar image

1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left

Is there an index on the file_syscode?

How active is the table when you're trying to do the delete?

Assuming that the table has a primary key, and that you are deleteing a large number of rows, you could break the delete into a set of smaller deletes to see if that helps


SET @i = 1

WHILE @i > 0
DELETE FROM wmcrs_mf_transactions
WHERE KeyValue IN (SELECT TOP 10 KeyValue FROM wmcrs_mf_transactions WHERE file_syscode = 56020)

SET @i = @@ROWCOUNT            

PRINT CAST(@i AS VARCHAR(10)) + ' Rows Deleted'            
more ▼

answered Oct 26, 2009 at 05:39 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

@Jay : I recommend not having an embedded SELECT to get the next "10" key values. In a large / inefficient table that will take longer than the delete :( Our approach is to get the primary key values of all the rows to be deleted into a TEMP table which additionally has an IDENTITY column. Then loop, as per your example, on increasing ranges of the TEMP table joined to the Main table and DELETE those. Can also put a short WAITFOR in the loop so that other processes get a chance to run too.
Oct 27, 2009 at 06:58 AM Kristen ♦
@Kristen, 10 was an arbitrary value, it could have been 10,000. After reading your comment, I realized I had done deletes in the past when working with large tables as you described, by using a temp table, and generally, that would be a better approach. Since, all the key values should be significantly less than the the entire base table.... So your suggestion is definitely a better approach than the embedded select I suggested
Nov 05, 2009 at 12:59 AM Jay Bonk
(comments are locked)
10|1200 characters needed characters left

Query is expected to delete around 3000 records only.

Following are the indexes created on the table..........

nonclustered located on PRIMARY >> transaction_date nonclustered located on PRIMARY >> customer_syscode nonclustered located on PRIMARY >> prematch_syscode nonclustered located on PRIMARY >> file_syscode clustered, unique, primary key located on PRIMARY >> mf_tran_syscode nonclustered located on PRIMARY >> customer_syscode, transaction_date, transaction_type, scheme_option

Also the table is having some more constraints......

I am getting following msg, when i am trying to paste the create table scripts in the forum

* Oops! Your answer couldn't be submitted because:
* sorry, new users can only post a maximum of one hyperlink
more ▼

answered Oct 27, 2009 at 12:47 AM

Arul 1 gravatar image

Arul 1
11 1 1 1

Please either edit your original question, or add a Comment to the Answer. Please don't clutter up the site with comments entered as an Answer - because it isn't an Answer, right?!
Oct 27, 2009 at 06:59 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 26, 2009 at 03:43 PM

Seen: 3006 times

Last Updated: Oct 26, 2009 at 04:03 PM