Table 1 has about 300K values. IT's car stuff with make, model, year,
carcode
Table 2 has about 1000 values. Also car stuff make, model, year,
carcode.

I need to scan table 1, find the matching carcode in table 2, and
replace the values for make/model/year in table 1. Easy..but it is
running slow as molasses.

I have index on carcode in both tables. The carcode in table 1 has
more data than I need char(20)so I have this:

select 1
use table 1
scan
scatter memvar
code1 = substr(m.carcode,1,5)
code2 = substr(m.carcode, 8,1)
goodcode = code1+code2 #this exactly matches the carcode in table 2
which is a char(9)

select 2
select * from table2 where goodcode =carcode into cursor carcurs
newmake = carcurs.make
newmodel = carcurs.model
newyear = carcurs.year

select 1
replace make with newmake, model with newmodel, year with newyear

endscan

It's doing 50 in about 2 seconds which makes for a long haul on 300K
records or more. Is there a good way to batch this to speed it up? I
need to do these tables 2-3 times a day and this is taking forever.

Molly

I would add a few steps.

select diset carcode from table1 into cursor carcodes

select carcodes
scan
if seek(carcodes.carcode,"table2","carcode")
select table2
scatter memvar
select table1
* do your logic here.
replace all make with newmake, model with newmodel, year with newyear
else
* No carcode in table2, so nothing to update in table1
endif
endscan

This way; you're not doing lookups for the same carcode more than once.

This is aircode; but should spark some creativity.

On 5 Nov 2006 12:54:47 -0800, "MollyShark" <mollyshark (AT) gmail (DOT) com> wrote:


--- AntiSpam/harvest ---
Remove X's to send email to me.