Re: Large Table Deletes
- From: grendal <im_gumby@xxxxxxxxxxx>
- Date: Tue, 16 Jun 2009 05:38:02 -0700 (PDT)
On Jun 15, 7:54 pm, Art Kagel <art.ka...@xxxxxxxxx> wrote:
The large number of locks is why I wrote dbdelete. It holds no SELECT locksWell slow is relative and like I said, you could always fetch the rows
while it is deleting and no delete locks while it is selecting. It actually
selects 8192 rowids (all that fit into the until recently maximum fetch
buffer of 32K - that has been expanded to 4GB fairly recently and I'm in the
process of updating dbdelete and dbcopy to take advantage of the increase)
and uses that list to build a maximum length (64K) DELETE ... WHERE ROWID IN
(...) statement and execute it. Each 8192 deletes is committed as a single
transaction, so relatively few locks are held. If you try to fetch a rowid
and delete it then fetch the next one etc, all while the SELECT cursor is
open, your app will run MUCH slower.
Art
in to a memory buffer or buffers and then run through the buffers
pretty quick.
It all depends on how fancy you want to get. You could do this in
parallel with each delete in a separate connection and you offset your
position in the list of rowids by a large enough amount so that the
rows are most likely not going to be next to each other.
ESQL/C is your friend. ;-)
The point is that there are many ways to slice and dice this without
crushing your machine.
.
- References:
- Large Table Deletes
- From: Dave
- Re: Large Table Deletes
- From: grendal
- Large Table Deletes
- Prev by Date: WWW.PETBED-CATTREE.COM Pet Furniture,China Pet Furniture,Pet Furniture Manufacturer,China Pet Furniture Supplier,China Pet Furniture Exporter
- Next by Date: Re: Large Table Deletes
- Previous by thread: Re: Large Table Deletes
- Next by thread: Re: Large Table Deletes
- Index(es):
Relevant Pages
|
