Bulk delete on operational data

[Zarrin]

Hello,

I read several articles of newsgroup about the bulk delete, and I found
one way is to:
-create a temporary table with all constraints of original table
-insert rows to be retained into that temp table
-drop constraints on original table
-drop the original table
-rename the temporary table

My purge is a daily job, and my question is how this work on a heavy
load operational database? I mean thousand of records are written into
my tables (the same table that I want to purge some rows from) every
second. While I am doing the copy to temp table and drop the table what
happens to those operational data?

I also realized another way of doing the bulk delete is using BCP:

1) BCP out rows to be deleted to an archive file
2) BCP out rows to be retained
3) Drop indexes and truncate table
4) BCP in rows to be retained
5) Create indexes

Again the same question: When I'm doing the BCP is there any insertion
blocking to my original table? What happens to my rows meantime to be
inserted?
Does BCP acquire an exclusive lock on the table which prevents any
other insertion?
Does any one have an experience with a BCP command for querying out 2
million records, and how long will it take?

I appreciate your help.

[Erland Sommarskog]

Zarrin (zl******@yahoo .com) writes:

My purge is a daily job, and my question is how this work on a heavy
load operational database? I mean thousand of records are written into
my tables (the same table that I want to purge some rows from) every
second. While I am doing the copy to temp table and drop the table what
happens to those operational data?

I don't think it is a good idea to create new tables every day. Particularly
not if data are being added as you delete.

I can think of two ways:

1) Keep it all in the same table. It's imperative that the condition for
the DELETE is aligned with the clustered index, so that INSERTs are
not blocked by the DELETE:

2) Use a partitioned view. A partitioned view consists of a number
tables with the same structure and where the PK has a CHCEK constraint
the defines what does into which table. The table are then combined
in a SELECT with UNION ALL. Again, the partition condition has to be
aligned for the condition for the DELETE. More exactly so that a
DELETE is simple a TRUNCATE TABLE on a single table. Since you do this
on daily basis, it will be a lot of tables, unless there is a round-
robin mechanism. You can of course change the view defintion every
now and then, and while this is a swift operation, you must still lock
out the INSERT process, so it does not try to insert into a non-
existing view.

Read more about partitioned views in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Zarrin]

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.