>Nadya,
>
>Wouldn't it help a bit to use something other than the ctod('01/01/2001') and ctod('06/30/2001')? these have to be converted to dates on every record? i.e.:
>
>dStart = ctod('01/01/2001')
>dEnd = ctod('06/30/2001')
>and then use those variables (or constants computed another way) in the SELECT statement?


May be, but I don't know, how to do it :( Here is the program, which generates where condition for this query:

********************************************************************
*  Description.......: Select2CMR - CMR where expression
*  Calling Samples...: 
*  Parameter List....: 
*  Created by........:  
*  Modified by.......: 
********************************************************************
local lcSearch, lcWhereYTD, lcWhereMtg
do case
   case CMRView.TimePeriod=padr('CalYear',10) && Annual
        lcSearch='YR'+oJC.CalYear
   case CMRView.TimePeriod=padr('CalPeriod',10) && Calendar Quoter
        lcSearch=oJC.CalPeriod+oJC.CalYear      
   case CMRView.TimePeriod=padr('IssMonth',10) && Issue Month
        lcSearch=oJC.IssMonth
   otherwise
        lcSearch=evaluate('oJC.'+CMRView.TimePeriod) && Issue Quoter
endcase
if not seek(oJC.CurState+m.lcSearch, 'CMRIssue')
   select (m.lnSelect)
   return .f. && ??? or ""
else
  lcWhereYTD=CMRIssue.YTDDates
  lcWhereYTD=strtran(m.lcWhereYTD,['+oJC.calyear],oJC.CalYear+['])
  lcWhereYTD=strtran(m.lcWhereYTD,'endweek',[']+CMRIssue.EndWeek+['])
endif  
lcWhereMtg ='between(Mortgage,'+transform(CMRView.MtgLow)+ ','+transform(CMRView.MtgHigh)+')'                 
return 'where (' + m.lcWhereYTD+') and '+ m.lcWhereMtg

>
>HTH
>>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.