Nadya,

SET DELETE ON, won't help you with INDEXSEEK(somevalue, .f.,"Table") because it never actually searches the table, but only the index. therefore it should be the fastest way within VFP. But since you don't have a filtered index and the table can contain deleted records, you'll have to switch to the other alternatives.

Of course you could add a filtered index, but I guess since it is not your application, it would not be prefferred.

Walter,


>Hi Walter,
>
>Interesting idea. No, both tables don't have filtered indexes, and they have deleted records. What if I set deleted on?
>Would your solution work?
>
>Unfortunately, it's not my application, but we can try.
>
>David's idea is also promising. Basically, there are only few orphans or even 0.
>I'll try them.
>
>Thanks.
>
>>Nadya,
>>
>>If your table2 does not contain any deleted records or the join index is filtered for !DELETED(), you may want to try :

>>SCAN FOR INDEXSEEK(PropID,.f.,"Table2")

INDEXSEEK does not retrieve records from the table but searches trough the index itself. I can't think of any faster method.
>>
>>Walter,
>>
>>
>>>Hi everybody,
>>>
>>>I have Table1 (child) with ~5mln.records and Table2(parent) with ~4mln.records
>>>
>>>I want to find and cound each record in Table1 key PropID, what doesn't have
>>>corresponding record in Table2.
>>>
>>>Two ways of doing that:

>>>scan Table1
>>>seek PropID && in Table2
>>>if not found()
>>>   *** This is an orphan.
>>>endif
>>>endscan

>>>
>>>or
>>>
>>>select PropID from Table1 where PropID not in (select PropID from Table2).
>>>
>>>Both tables, of course, have indexes on PropID.
>>>
>>>My question is: what is the fastest way of doing this?
>>>Currently we're using method 1 and I have not patience enough to perform test2.
>>>
>>>What do you think?
>>>
>>>Thanks in advance.