October 8, 2009 at 12:38 am
I have indexes in place that was built looking at the execution plan of the query.
This is the mother of all procs, so it is expected that it will run slow, but 6 hours is ridicolous. So we've tuned the query as far as we can and got it down to about half the execution time. Now the delete part of the query takes VERY long still. ALL ID's that are refrenced in the where clause have indexes. Still the delete part of the query runs for about an hour. Is this due to the fact that it deletes row by row and writing to the log file, or can it be something else? Is there a way that I can delete faster?
the number of rows is roughly 40 000 lines, so it should not be too hectic...
October 8, 2009 at 1:08 am
ZA_Crafty (10/8/2009)
Is this due to the fact that it deletes row by row and writing to the log file, or can it be something else?
With row by row, you mean in a cursor? Do it in a single DELETE statement if so.
It could also be faster to save the rows that don't have to be deleted in a temp table, truncate the original table and the insert back the rows you saved, depending on the number of rows to save and to delete.
It's difficult to give a good advice without seeing the code, anyway.
-- Gianluca Sartori
October 8, 2009 at 1:43 am
thanks.
I cannot reallypost the code as it does not belong to me.
Here is a modified version:
delete from ReportDetailTable
where ReportDetailTableID in (Some SubQuery, not to complex)
PS there is a cursor, but that is not the delay. The cursor only defines sets that should be deleted from the DB. Thus the cursor only ever loops once. It is just in place for incase.
The actual rows are deleted as stated above. With a subquery.
October 8, 2009 at 1:50 am
delete from ReportDetailTable
where ReportDetailTableID in (Some SubQuery, not to complex)
If the cost of the query is on the delete step, there isn't much you can do.
You could drop and re-create indexes if the amount of data deleted is big.
You could also take a look at this article:
http://www.sqlservercentral.com/articles/T-SQL/67898/
Regards
Gianluca
-- Gianluca Sartori
October 8, 2009 at 2:58 am
Are you sure it's the actual deletion that is slow... what if you turn your delete statement
delete from ReportDetailTable
where ReportDetailTableID in (Some SubQuery, not to complex)
Into a SELECT statement
SELECT ReportDetailTableID FROM ReportDetailTable
where ReportDetailTableID in (Some SubQuery, not to complex)
How does that perform?
Do you have any delete triggers on the table?
You say you can't post the code... what about posting the query plan for the delete?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply