I agree, except that in our system Query is a separate application. So, I either should create a job consisting of multiple Query steps, which is quite inconvenient or try to optimize this query to the best I can... Another option would be to make selections based on TranMstr and SiteMstr (join to SiteMstr is needed, because TranMstr doesn't have a town field), and to join to CMRView in my application. But unfortunately, the first resulting table would be too big and I also has to change the logic in my own application...

>Hi Nadya,
>In cases like that I usually try to split the query into multiple steps
>First, I would run query like follows and see how much faster it gets
>

SELECT TranMstr.PropID, oJC.CurState as State, Date, Mortgage, Source, ;
>ParentCode as Lender, extrWeek, extrYear ;
>FROM TranMstr ;
>where between date ctod('01/01/2001')and ctod('06/30/2001') and between Mortgage 1 and ,1000000000 INTO CURSOR Step01

>
>If it really speads up things than I'would join the rest of tables with this cursor or try some other combinations.
>
>
>
>>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.