Hi Bernhart,
Index on DTOS() wouldn't help because for Rushmore to kick in condition in where clause has to match index expression and there is no DTOS() in the where clause. Indexes on dStart, dEnd and dDate may speed up your query. Try
Select COUNT(Temp2.Categorie ) ;
FROM Table1, Table2 ;
WHERE Table2.ddate BETWEEN dvstart AND dvend ;
AND Table2.ddate BETWEEN Table1.dStart AND Table1.dEnd ;
ORDER By Temp2.Categorie GROUP By Categorie Into Cursor Temp
You can also use SYS(3054,11) to see how your query is optimized by VFP.
>Hi all,
>
>How can i improve a requery ?
>
>
>(Sample)
>LOCAL dvstart,;
>dvend
>
>
>dvstart = DATE() - 6000
>dvend = DATE() + 12000
>
>Table1
>NAME C(18)
>dstart D(8)
>dEnd D(8)
>
>
>Table2 && Many Records
>ddate D(8) && Unique dates
>Categorie C(1)
>
>
>A = SECONDS()
>
>
>Select COUNT(Temp2.Categorie ) FROM Table1,Table2 ;
> WHERE BETW( Table2.ddate , dvstart , dvend ) AND;
>BETW( Table2.ddate , Table1.dStart , Table2.dEnd )= .T;
>ORDER By Temp2.Categorie GROUP By Categorie Into Cursor Temp
>
>
>B=SECONDS()
> WAIT WINDOW NOWAIT TRANSFORM( B - A , "9999.99999")
>
>It 's slow !
>
>What can i do ?
>
>Index on DTOS(dstart) and DTOS(dend) and Dtos(ddate) don't improve.
>
>bernhart
--sb--