I have a table in a database that can get quite large. The
data on this table has a timestamp column, which is used to
see how old the data is, and at regular intervals, our
application purges this table using a DELETE from tableX
where DATEDIFF(day, tableXTimeColumn, GETDATE()) > Y

There is a primary key in the table, and various other int
columns, and a big varchar(1000) column that holds some
data. There are foreign key columns out of this table, but
no other table has a foreign key referencing this table.

there is a clustered composite index, (doesn't relate to
the timecolumn) and there is a separate index for this
timecolumn.

We find that whenever a large number of rows (several
hundred thousand) should be deleted from this table, the
database can take upto a few hours todo it. This is on a
relatively fast box, (asa 8.0.2.4332) During this time,
i/o activity shows the dbsrv8 process writing gigs and gigs
(to a temp file I suppose, for a possible rollback) The db
is started with the -m option. And all other settings
pretty much default.

Is there any way to get around this heavy i/o writing and to
try to speed up this operation? Other queries on the db run
excessively slow because of the i/o bottleneck this causes.
I see in the docs that @@rowcount could be used to limit the
number of rows a delete statement causes, hence shrinking
the transaction size. I'm perfectly fine trying out this
method, to loop over many smaller deletes and perhaps
sleeping in between the calls, but perhaps someone has a
different way of going about this.

Thank you for any help you can provide.

Oguzhan Eris

The rollback log is stored in the database file so a bazillion deletes
in one transaction will inflate the .DB file. Perhaps you could use a
cursor loop to do the deleting. Set BACKGROUND_PRIORITY to 'ON' to let
other connections go fast, specify WITH HOLD on the DECLARE, and do a
COMMIT every once in a while (1000 rows?) to keep the rollback log
from going crazy. Do not *ever* run with -m, IMO, that's just asking
for trouble, use a safer technique to keep the .LOG size under control
(dbbackup, for example).

The indexes might be hopelessly fragmented after the deletes; consider
REORGANIZE.

Are you deleting the majority of the rows? Because copy/truncate
table/copy back might be faster.

Breck

On 8 Dec 2003 15:15:27 -0800, Oguzhan Eris wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com