On difference may be that the faster method is working a CURSOR, rather than a DBF.
How fast does a scan or do loop run? Ex:
use in 0 table1 order fieldx
use in 0 table 2 order fieldx
create cursor tab1 (fieldxx c(7))
select table2
scan while !eof()
if seek(table2.fieldx,[table1])
* ignore
else
append blank in tab1
repl tab1.fieldxx with table2.fieldx
endif
endscan
>In VFP 6, two free tables, table1 (739,068 records) and table2 (609,862 records) each has a fieldx which is a 7 byte long character representation of a number which is unique within each table. Each table has an index tag on fieldx. The following:
>
>SELECT fieldx FROM table2 WHERE fieldx NOT IN (SELECT fieldx FROM table1)
>
>This correctly return 31,183 records in 1002.50 seconds.
>
>SELECT fieldx FROM table1 INTO CURSOR tab1 NOFI
>SELECT fieldx FROM table2 INTO CURSOR tab2 NOFI
>SELECT fieldx FROM tab2 WHERE fieldx NOT IN (SELECT fieldx FROM tab1)
>
>This correctly return 31,183 records in 25.60 seconds.
>
>Before each run I made sure that VFP was the only process running on the Windows 95 machine. Pretty strange.
Imagination is more important than knowledge