On a daily basis, we have to delete large volumes of data. Our delete statements can take a long time to run. We've had some success using the MERGE statement but this is one table in particular where that won't work. I think the slow performance of the delete statements is because it constanly writes to the log while it does this. Is there a way to speed this up by not writing to the log (i.e. disabling it briefly and then turning it back on)? We can't use TRUNCATE TABLE because the delete only removes a subset of the total rows in the given table. Thanks, Tom
asked 14 Jul '10, 04:13
The transaction log is critical for recovery -- so allow me to restate your question to "How can I improve the performance of my DELETEs"
My first course of action would be what Volker stated in his comment, which is to loop through a series of smaller deletes -- and committing each time. I don't know why this works, but it does for me -- I assume it's because it creates smaller locks -- again as Volker suggests.
Another thing is that when you delete records, any related index has to be updated, and dependencies have to be checked. So make sure you don't have any unnecessary indexes defined and you don't have any unnecessary foreign key relationships defined either.
answered 28 Jul '10, 11:52
You could restart the engine in bulk modus. -b But for a productive environment this will not be a good idea.
answered 14 Jul '10, 07:13
Just as a further pointer (wthout knowing if the actual problem is still unsolved...):
Nick Elson [Sybase iAnywhere] has listed a lot of factors that may make a difference between the performance of a SELECT statement and an according DELETE statement based on the same SELECT.
So it's basically focussed on the question "Whis is a delete much slower than the according select?".
Don't know if this is the actual question here - but I'd like to quote the answer anyway. As it is in some kind of home-brewn table-format, I hope I have quoted it correctly:)
Source: news://forums.sybase.com/sybase.public.sqlanywhere.general, thread "Slow deletes vs selects" starting 2010-03-31.
answered 28 Jul '10, 15:43