on 2010 Jul 14 5:13 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:)
Deletes need to
- modify table and index pages
- all indexes must be modified
- rollbacklog pages need to be allocated and filled with insert operations (can grow file too)
- can require much more cache around check contraints & pubs
- operations need to be logged
- statistics might need to updated
- will set off trigger actions, ri actions, cascade actions
- require exclusive locks and will block more readily and be exposed to more contention
- will cause disk reads and writes [more reads and writes are going to be more expensive than reads as well]
- the need to visit all pages assoc. w\\all columns, esp. extra's CLOBS, BLOBS, long types, ...
- replication could even be playing a role here
Selects only need to
- only accesses pages required
- only parts of indexes accessed by the plan are visited
- writes to the rollback log only if done in an updateable cursor
- occasionally stats get updated
- no triggers or checks
- typically only shared locking
- at low isolation levels there may be little or no contention
- if the cache is warmed then select may have no i/o to do except when returning results to the client
- selects may skip some of the extra stuff like BLOBs
- replication is not a big factor
Not to mention the potential for system resource limitations leading to swapping to the PagingFile, I/O contention with other processes, ... etc
Much depends upon the state of the server, the specific schema, the data distribuition, the state of the table and indexes in the database, and other activity on other connections and in applications.
Source: news://forums.sybase.com/sybase.public.sqlanywhere.general, thread "Slow deletes vs selects" starting 2010-03-31.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You could restart the engine in bulk modus. -b But for a productive environment this will not be a good idea.
Regards Thomas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
73 | |
10 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.