[RBASE-L] - RE: speeding up a delete rows

[Troy Sosamon]

Deletes on large tables are slow because the indexes have to be updated.   You might want to try something like this:   Project temp_tranacctid from tranacctid using all Create index #none on temp_tranacctid (tranid) Delete from tranacctid Append temp_tranacctid to tranacctid where tranid not in (select tranid from temptrans)   Troy     From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Alan Wolfe Sent: Thursday, March 18, 2004 6:05 PM To: RBASE-L Mailing List Subject: [RBASE-L] - speeding up a delete rows   Hey everyone,   we have a delete that for some reason is taking alot longer than it seems it should.   we have 1 table declared like this:   create table temptrans (tranid integer) create index on temptrans tranid   this has 44,524 tranids in it (all are unique tranids).   we have a second table declared like this:   create table tranacctid (tranid integer, acctid integer, acctidamount currency, transtate text 1, source text 1) create index on tranacctid tranid create index on tranacctid acctid   in tranacctid the tranid's are not necesarily unique, and theres 175950 rows.   the sql we are trying to do is this:   delete rows from tranacctid where tranid in (sel tranid from temptrans)   but that is going SUPER slow.  I have a decent computer (dual cpu 2.6 pentium 4's with 512MB RAM) and i waited 10-15 mins and then cancelled the command and it had deleted 22,000 records, when there are 44,524 matches so in 10-15 mins it was about half way done.   Anyone have any ideas on how to speed this up?  we tried correlating the subselect but it didn't seem to help any unfortunately.   Thanks! Alan