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.