Craig,

It finally donned on me what Nadya was saying. The link to your Rushmore Article on your site is not operating [ Oops... ].

>>Sorry, Craig. All I got, is: "Oops" :)
>>
>>Why it could not be fully optimized? What if I remove join to lender table?
>
>Did you read the article on my website? It will explain why it will never be fully optimized.
>
>>
>>>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.