Tom,

Just a comment - to keep my general criticism of VFP alive. . .

Josh W stated that Rushmore is a strange and wonderful thing. It definitely is wonderful, but that strange part need NOT be so.

It should not be necessary for each and every one of us to "experiment" and subsequently make up our own 'rules' based on our personal observations.
Especially being so critical, Rushmore and tghe whole of VFP-SQL facilities deserve full and comprehensive DOCUMENTATION, to both save huge amounts of time for developers and to keep expectations in line with reality.

There are lots of other areas where the docs are also poor, but SQL/Rushmore are two of the more critical.

have fun experimenting

Jim N

>>>>>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?