Faster way to delete rows

[RobertT687]

I have a MSSQL 6.5 database with several tables of over 1 million rows. I have a control table in another database on the same server with key fields to the rows I want to delete (approximately 800K rows in each of 4 tables). The control table called DBSdemo..pmt_rqst_purge_ctrl has the structure:
vendor_id char(10) NOT NULL,
vendor_loc_code char(3) NOT NULL,
pmt_rqst_nbr char(22) NOT NULL,
pmt_rqst_date datetime NOT NULL,
schld_pmt_nbr smallint NOT NULL
with a unique clustered index made up of all the columns in the order shown. The tables to be purged also have primary keys and/or unique clustered indexes containing all of the above columns plus one or two others.

I'm using code like the following to delete older data I no longer need, and it's taking FOREVER.

DECLARE @RowsDeleted int
SELECT @RowsDeleted = 0
SELECT "Starting purge of pmt_rqst_tax @" + convert(varchar(26),Getdate(),109)
pmt_rqst_tax_Loop:
SET ROWCOUNT 40000
DELETE FROM pmt_rqst_tax
FROM pmt_rqst_tax pr,
DBSdemo..pmt_rqst_purge_ctrl pc
WHERE pr.vendor_id = pc.vendor_id
AND pr.vendor_loc_code = pc.vendor_loc_code
AND pr.pmt_rqst_nbr = pc.pmt_rqst_nbr
AND pr.pmt_rqst_date = pc.pmt_rqst_date
AND pr.schld_pmt_nbr = pc.schld_pmt_nbr
SELECT @RowsDeleted = @@ROWCOUNT
IF @RowsDeleted > 0
Begin
DUMP Transaction DBSprqt WITH TRUNCATE_ONLY
SELECT @RowsDeleted = 0
GOTO pmt_rqst_tax_Loop
End
SELECT "Finished purge of pmt_rqst_tax @" + convert(varchar(26),Getdate(),109)
go

The ROWCOUNT limit is present only to keep the transaction log from filling up.

Can any one suggest an improved way to get the delete done quicker?
A test run on my backup server has been running for over ten hours. Its not in a loop, just VERY SLOW.

Thanks in advance!!

 

[malexanian]

It may be faster if you drop all indexes (especially clustered ones) before the delete and rebuild the index afterwards. Maintaining the clustered one during the delete is the worst because the records are physically reordered for EVERY record deleted.

 

[RobertT687]

I will give that a try.
Thanks malexanian

 

[karluk]

An obvious alternative to what you are doing is to create copies of the tables you're trying to purge, and insert the rows that you want to preserve. Then drop the original tables and rename the copies to have the same name as the originals. Since you seem to be deleting the majority of rows in the tables, it seems likely that the amount of time to insert about 200k rows would be smaller than the amount of time to delete 800k rows.

You should make sure there are no indexes on the tables undergoing update activity. All that does is create extra overhead. If you stick with deleting from the original tables, you should drop the indexes and recreate them later. If you insert to copies, you should create the indexes after the inserts are finished.

 

[mwa]

I'm not sure if this would make it any faster, but have you tried writing a SP that uses a cursor to loop through each record in your control table then delete the lines associated with the control, inside the cursor?

I'm also curious if this is SmartStream that you are working on?

mwa

 

[RobertT687]

To mwa:
Already tried the Cursor route and its even worse.

Yes this is SmartStream Version 6.0.3.

 

[mwa]

I thought I recognized those field names... So how long have you been using SS? And... What do you think?

 

[RobertT687]

To mwa:
I've been responsible for the dba functions on the financial package for about a year now, but my experience with SS goes back to 1996 when our company elected to be a Beta-site for SS Payroll/HR. Worst thing about it is the awful report writer (Management Reporter) and impossible to understand criteria for running purges (that's one reason I'm rolling my own!).

 

[mwa]

I don't blame you for doing your own purges... I've been working as a programmer/analyst on the SS financial systems for almost 2 years... And I have had to write some procedures to do similar things that you are... Good Luck...