Renauld,
Here full optimization:
CREATE CURSOR Mytable (DocId I, CAddress C(50), cStatus C(3), DDate T)
INSERT INTO MyTable VALUES (1,'asdasdasd','AAA',DATETIME())
INSERT INTO MyTable VALUES (1,'asdasdasd','XXX',DATETIME())
INDEX ON DocId TAG DocId
INDEX ON CStatus TAG CStatus
INDEX ON TTOD(DDate)TAG DDAte
tDateFrom = DATETIME()-10000
tDateTo = DATETIME()+3600
SYS(3054,12,"sss")
SELECT count(*);
FROM MYTABLE;
WHERE MYTABLE.docid = 1 AND;
MYTABLE.cstatus <> "XXX" AND;
BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo);
GROUP BY MYTABLE.caddress;
INTO ARRAY laCount
MessageBox(sss)
>Thanks for the immediate reply. Here is the results...
>
>Using this code:
>SYS(3054,12,"sqlTest")
> SELECT count(*);
> FROM MYTABLE;
> WHERE MYTABLE.docid = doctor.docid AND;
> MYTABLE.cstatus <> "XXX" AND;
> BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo)
> GROUP BY MYTABLE.caddress;
> INTO ARRAY laCount
>MessageBox(sqlTest)
>
>*------------------------------------------------------
>This is the result when I run the expression that you wrote.
>
>Using index tag xdocid to rushmore optimize table dispatch
>Rushmore optimization level for table MYTABLE: partial
>*------------------------------------------------------
>Now if i put 2 index for CStatus and TTOD(DDate) will this make a difference? What is the quickest way to time this?
>
>For my date field (MYTABLE.DDate), this is a DATETIME type but my low/high value are DATE type so i have to convert my MYTABLE.DDate with TTOD function.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.