>>>>>How exactly are the fields indexed? For example, is the index key UPPER(f1) or f1+f2+f3+f4? If it's just plain f1 I would expect that join to be optimized. You should try it in the WHERE clause to see if it makes a difference. It's also possible that SYS(3054) only looks for optimization in the WHERE clause and won't find it in the JOIN clause even if it is optimized. I would do some expirimentation and see what works. Rushmore is a strange and wonderful thing!
>>>>>
>>>>>>I am trying to join a 300K table to one that has
>>>>>>about 5 million records. The 1 to many join is
>>>>>>based on 4 fields. All 4 fields in both tables
>>>>>>are indexed.
>>>>>>
>>>>>>Is it best to use the ANSI 92 JOIN ON with all
>>>>>>4 fields combined with an AND or should I use the
>>>>>>WHERE clause? I tried using the SYS(3054,11) command
>>>>>>and am getting 'none' as the optimization level.
>>>>>>
>>>>>>What can I do to optimize this join?
>>>>>>
>>>>>>The info below represent what I have now:
>>>>>>
>>>>>>SELECT tablea.* ;
>>>>>> from tablea aa ;
>>>>>> JOIN tableb bb ;
>>>>>> ON aa.f1 = bb.f1 AND ;
>>>>>> aa.f2 = bb.f2 AND ;
>>>>>> aa.f3 = bb.f3 AND ;
>>>>>> aa.f4 = bb.f4 ;
>>>>>> INTO CURSOR cx
>>>>
>>>>I have individual index tags on each field (4 tags on each table). The tags
>>>>are simply on the field - no upper() or other functions are used.
>>>
>>>How you will use the result of this join. It's just a curiosity, because 300K joint to 5000K looks scary.
>>
>>I expect the result set will be 1000K records.
>
>Well it worked and I got my 908K records. SYS(3054,11) showed the initial
>optimization of none on both tables. The end result showed a partial optimization on the index tag temp. Guess this is one that VFP created.
>Sure which I knew what it used. Is it possible the index tag should be on
>the concatenation of all 4 fields?
No, it should definitely be on each field unless your join looks like this: aa.f1+aa.f2+aa.f3+aa.f4=bb.f1+bb.f2+bb.f3+bb.f4.
How's the response time on your query? If you don't want to use DELETED() tags and you know there are no deleted records, SET DELETED OFF will speed the query.
