>I have some very large tables from which I have to get records NOT IN other very large tables. Code like this:
>
>select * from table1;
>where keyfield NOT IN;
>(select keyfield from table2)
>
>is not optimizable, and is extremely slow (about 15 minutes or more) for each query. I have tried a SCAN/SEEK, but it is at least as slow or worse, since you're looking for unique keys that don't exist, and therefore must hit every record.
>
>Any ideas for something faster?

Do you have to push these tables & indices back and forth across a network cable? You might try setting up a COM server on the server machine so these queries could be processed locally.

As far as your code goes, it seems to me that optimized code should equal or beat SELECT - SQL in most cases. If you've tried SEEK/SCAN WHILE a la JVP, and you're moving record pointers yourself (you might try doing it without SET RELATIONs) I don't think it gets much faster. I'd be GLAD to be proven wrong here!

I like Bill Caton's idea of separately maintained intermediate tables, to prevent recalculating the same intermediate data time and again.