Yeah, Jim alerted me to that. I thought I had checked all the links when I moved the material from esolserv to my new host..and FrontPage didn't complain about a broken link. It's back up now.


>
>Craig,
>
>I would be glad to read it, but all I get, is 404 Error.
>
>I removed Lender Join from the Joins and I remembered, that I left out one condition on SiteMstr (PrefCode='P'). I added it, but it didn't improve the situation much. The next step would be to divide this query by 2, say, first, TranMstr+SiteMstr, the next limit by towns using CMRView. Unfortunately, it doesn't fit well in our application definition. I have to create an additional step or manage it another way...
>
>
>>>
>>>>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.