Posted by Zarrin on 10/06/00 11:33

Thanks Erlands, and sorry for replying so late. I was working with it
several days and finally I got it to a point while execution time for
my purge improved a lot from more than 24 hours to 1 hour with using
clustered indexes on all tables created to do the purge.

To clarify how I do the delete, I first select all IDs of records to
be deleted from the main table and keep them in another table, then I
search my 10 other tables that have related records and delete those
related IDs. I do all my deletes in chunks of 1000 to avoid blocking
the inserts into the tables meantime.

So what I changed was to use a clustered index on the intermediate
table that I had for keeping "to be deleted" IDs.

If later I end up in having more than a few millions purge records I
probably have to use your second solution which is partitioned views.

Thanks again it helped.

• Next in forum: Help needed by deadlock problem
• Prev in forum: Can REPLICATION on SQL Server 2000 allow dirty reads
• Thread view: Re: Bulk delete on operational data

[Reply to this message]