Can I make my delete run faster?

On our OLTP 24/6 system we have a table with over 40,000,000 rows in
it, the need is to remove half of these rows. There are 7 indexes on
the table (one clustered, six non-clustered). Since the IT director
won't let me move it to a faster server, I have to delete the records
month by month. The data is not referential (I didn't design the db) so
I can't use cascading deletes and have to use a delete trigger:
CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
FOR DELETE
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM INSERTED)
BEGIN
delete from tbl1 where exists (select * from deleted as d where
d.[master id] = tbl1.[master id])
delete from tbl2 where exists (select * from deleted as d where
d.[master id] = tbl2.[master id])
delete from tbl3 where exists (select * from deleted as d where
d.[master id] = tbl3.[master id])
delete from tbl4 where exists (select * from deleted as d where
d.[master id] = tbl4.[master id])
. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. ..
(up to 17 tables)
END
The trigger works fine and is deleting the relevant data in the above
tables, however I am wondering if there is some way I can speed up the
deletes. I have tried using a while loop and deleting in piecemeal
however this doesn't seem to be any faster than using the following
statement:
delete from [consignments]
where [Date] >= '20011201' and [Date] <= '20011207'
Currently on our high spec'd dev server (10 disks, quad procs,
enterprise edn) I am experiencing odd timings, sometimes it takes 5
minutes to delete 1million rows, yet it can take over 30 mins to delete
1.5million rows. There is no one else accessing this server. I created
a further index on [master id] and [Date] no visible increase in
deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
Updated the stats, no joy also. So, any ideas you SQL MVP's?
Should I be using index hints? (I have read the SQL Server can do this
efficiently on its own.)
Cheers
qhScott
Why do you need a trigger?
Write stored procedure instead
Don't run the transaction to delete all rows at once. Divide the tran into
small ones
SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
DELETE FROM MyTable WHERE ........
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||If you are deleting half of the rows in this table, then I assume this is a
one time deal and not a daily operation. Most of the time spent grinding
away is SQL Server logging the deletes to the transaction log. In addition
to deleting the records in batches of say 100,000 at a time, consider
changing the recovery model, perhaps temporarily, of the database to
"simple" and executing a "checkpoint" between each iteration of deletes.
This will minimize the amount of transaction logging. You may also want to
delete indexes from the table prior to the mass delete and then re-apply
them again afterward, especially the primary / clustered index. Also,
possibly temporary setting the database to SINGLE_USER mode could help.
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||JT wrote:
> If you are deleting half of the rows in this table, then I assume
this is a
> one time deal and not a daily operation.
Yes, for political reasons, I can't simply create a new table and copy
the rows we want then rename the new/old table. Therefore on a wend
(only time db isn't being used) I need to run a script that can remove
over 15,000,000 in a few hours. I should be able to do it now, however
I want to see if I can make it faster.
I'm using a trigger to ensure that I don't have any redundant data in
the other tables. Yes RI should have been setup in the design stage
however, in a perfect world, etc...
Thanks for the reply.
Scott|||Hi
This can be modified as:
delete tbl4
FROM tbl4
INNER JOIN deleted as d ON d.[master id] = tbl4.[master id]
this will improve the performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Scott" wrote:

> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>