Dragan,

>>TranMstr tabe now has a Lender tag.
>>Lender table has a code tag (Lender is a Lookup table, has all lenders and their details).
>>Lender table is not used in where expression.
>>The exact SQL looks like:
>>

>>select ccode, town, date, mortgage, parentcode, ;
>>source, extrweek, extryear ;
>>from TranMstr inner join SiteMstr ;
>>on TranMstr.PropID=SiteMstr.PropID ;
>>inner join Lookups!Lender on TranMstr.Lender=Lender.code ;
>>where Source='S' and between(mortgage, 25000,500000) ;
>>into table (qry_arg)
>>

>>SQL uses temp index, which I don't understand.
>
>Beats me - does it tell you what does it use the temp index for? Try to analyze it without the where clause, and if it stops, then try adding its pieces back one by one. Also try to move the join clause into the where clause (like in 2.x days).

Ok, mystery continues :) I added Force clause in my Select expression and now it uses code tag to optimize Join condition. The SQL runs much faster (but still not fast enough, I think). The interesting fact is we're using Novell here at work. My colleague tried this SQL on her machine, where she uses NT. Without Force just by changing the order of joining expressions she achieved the significant speed increase and index code was used in optimization. Therefore, I conclude, this SQL is OS dependable! Very strange...

I don't have reasonable explanantions here. But this is not the end. If I run this SQL from our menu with Show optimization plan turned on, this SQL produces an error after it finishes. If it's turned off, it goes smoothly... I tried to debug this problem the whole evening, but didn't find an answer yet...

Thanks in advance for any clues...