Need Performance help deleting rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Performance help deleting rows

Author

Topic

airjrdn
Starting Member

35 Posts

Posted - 2002-02-19 : 12:22:14

We've got a table with 55 million rows. We need to delete based on a CallDate (there is an index with that as the only column in the index). Typically each call date will relate to about half a million rows. There are about 13 indexes on the table (and they are all needed). Performing the delete currently takes about an hour (to delete the .5 million rows). Looking at the query plan, it IS using the index. What can we do to speed this up?

Thanks in advance!

JamesH
Posting Yak Master

149 Posts

Posted - 2002-02-19 : 12:44:46

Depending upon the amount of records you need to delete and how often they need deleted you might want to consider BCP'ing the data out that you want to keep, truncate the table, and then bring the data back in. After that you should be able to create a job that can do it in smaller chunks. I have some tables with 900 Million + records and the only way to delete data without Locking users out for days is to find some way to do it in chunks (Usually with DTS/Dynamic Properties Task/ and procs. I really can't say beyond that as I don't know your business process.

JamesH

airjrdn
Starting Member

35 Posts

Posted - 2002-02-19 : 13:01:07

That's definitely food for thought. We currently need to delete about 30 million rows, but after that's done we'll only need to delete out about .5 million per day.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-20 : 02:11:08

Yep, I'd say break it into chunks, if you can. For instance, if you know you've got about 500,000 rows to delete a day, create a job that runs once every 15 minutes and does something like:

delete from calls where call_id in (select top 6000 call_id from calls where calldate=@dCalldate)

...that way you spread the delete out over the day, rather than trying to do 500,000 all at once.

Cheers
-b

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-20 : 02:25:47

I think Aiken is onto the right solution except instead of using a sub query use SET ROWCOUNT

eg



SET ROWCOUNT 10000
Select 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE Calls WHERE calldate= ?
END
SET ROWCOUNT 0

It will do 1 extra pass....

HTH

DavidM

Tomorrow is the same day as Today was the day before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources