|
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 |
|
You could restart the engine in bulk modus. -b But for a productive environment this will not be a good idea. Regards Thomas |
|
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. |
Make sure you're doing commits during the delete quite frequently - otherwise the rollback log will become huge as it has to store all deleted rows until the transaction is commited/rolled back. So I would try to split the delete into smaller chunks, e.g. by using "DELETE TOP n ... ORDER BY <yourpreferredorder>; COMMIT;" in a loop until all wanted rows are deleted.
Volker - I tried this today using a simple test procedure. I didn't notice any improvement in performance -- it took just as long to run when not using a loop in ISQL. But you think this will cut back on log growth? That in itself would be good -- our log grows over 1GB every day until restarted during our backup process. Tom
@Tom: The split-approach won't reduce the size of the translog as still the contents of each deleted row has to be stored in the translog. That's necessary for recovery purposes. It should reduce the size of the rollback log (which itself is part of the translog AFAIK) which contains all uncommitted changes and is dropped on rollback/commit. But the reduced size of the rollback log is only a temporary effect and as such should not impact the general translog file size.
@Tom: Whether the split approach might fasten your deletes significantly or not will depend on the actual query. Are there lots of locks held during the delete (or may the delete have to wait for other connections to commit)? - It might be helpful if you could present your query (with plan) here so that experts like Glenn can give advice...
Did you find any answer? If so, please post it here. Thanks!
Here is a standard delete for one of our client uploads:
DELETE FROM gt_payments WHERE fd_entity = '0048'; COMMIT;
The gt_payments table has just shy of 1 million rows. It has about 50,000 rows for client 0048. I just ran this statement in ISQL and it took 12 minutes to complete. The table has a pk of 3 columns and has 3 indexes -- none of which I created. Per Ron's point below, how can I determine if one or more of these indexes is unnecessary?
I created a plan. What's the best way to get it uploaded here? thanks, Tom
Just to correct that older statement: AFAIK now, the rollback log is part of the system dbspace, not of the translog - to cite from the 12.0.1 docs: