>Sorry, Craig. All I got, is: "Oops" :)
>
>Why it could not be fully optimized? What if I remove join to lender table?
>

I excluded Lender join from this query, but it didn't improve the situation. I guess, there is nothing I can do... I also tried to change order between joins, but it didn't help.


>>You will never fully optimize this query. See the article on Rushmore on my web site.
>>
>>
>>>Hi everybody,
>>>
>>>I have a query, which executes too long (though I executed it through PCAnywhere, but it should not be a factor here):
>>>
>>>

SELECT oJC.CurState as State, CMRView.*, Date, Mortgage, Source, ;
>>>ParentCode as Lender, extrWeek, extrYear ;
>>>FROM TranMstr inner join Lookups!Lender on TranMstr.Lender=Lender.Code ;
>>>Inner Join SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
>>>Inner Join CMRView on SiteMstr.town=CMRView.town ;
>>> where (between(date,ctod('01/01/2001'),ctod('06/30/2001')) ) and between(Mortgage,1,1000000000)  INTO TABLE \redp\work\CMR\CMR_0FE18SEWQ

>>>
>>>Here is sys(3054) result:
>>>

>>>
>>>Using index tag Date to rushmore optimize table tranmstr
>>>Using index tag Mortgage to rushmore optimize table tranmstr
>>>Using index tag Deleted to rushmore optimize table tranmstr
>>>Rushmore optimization level for table tranmstr: full
>>>Using index tag Deleted to rushmore optimize table lender
>>>Rushmore optimization level for table lender: full
>>>Using index tag Deleted to rushmore optimize table sitemstr
>>>Rushmore optimization level for table sitemstr: full
>>>Rushmore optimization level for intermediate result: none
>>>Joining table tranmstr and table sitemstr using index tag Propid
>>>Joining intermediate result and intermediate result using temp index
>>>Joining table lender and intermediate result using temp index
>>>Selected 13446 records in 368.26 seconds
>>>        275
>>>        375
>>>        475
>>>        575
>>>        675
>>>        775
>>>        875
>>>        975

>>>
>>>BTW, what do the numbers (275, 375, etc.) show here?
>>>
>>>Do you have suggestions to optimize it (don't suggest to drop deleted tags :))?
>>>
>>>Thanks a lot in advance.