I have 2 tables, each with close to 4 million records. They have one
field in common (VIN). I want to delete each row from table 2 where
vin is also in table 1. I have indexed vin in both tables. No matter
how I try to word this, it is taking an extraordinary amount of time.
Optimization is set on. I'm running a P4 with 512 memory (which isn't
a ton, but shouldn't be bad) and VFP 7.0. What is the ultimate way to
do this for fastest results. I have 4 more to do like this, so it is
a major order.

Thanks.

Monica

Simple really.
I use the following method

if Seek table 1 (VIN)
do while true
if seek(table 1 key, table 2)
replace key in table 2 with replicate(Chr(255),len(field))
delete record in table 2
else
exit
endif
enddo

This is fast and has another advantage.

If you set the order to the key and do a go bottom whenever you want
to add a record, you can reuse deleted records, thereby cutting down
the need for packing of files.

So,
add new memory stuff
set order to key
set deleted off
go bottom
if deleted()
recall
gather memvar
else
insert into file from memvar
endif
set deleted on

Also remember to always, always have an index on deleted(). This will
speed up things (a LOT) if you have a high number of deleted records.
Actually, this is mentioned in the docs for rushmore.

Rod


On 5 Apr 2004 07:19:21 -0700, monica (AT) datashark (DOT) net (Monica J.
Braverman) wrote: