>>>>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?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only