>>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
>>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.
>
>I've often found a scan to be faster in VFP for this.
>I would do it this way, however:
use table2
>use table1 order propid in 0
>set relation to propid into table1
>scan for eof("table1")
> ** do code
>endscan
Trey,
Are you sure, your code would find records from Table1, which don't have corresponding records in Table2? You're scanning Table2, so I assume, your code would find opposite.
If it's not broken, fix it until it is.
My Blog