>>In Access & VFP 5.0, there are two index tags on the 1.5 million table, BRANCH and ITEM_ID. the following SQL was run in both VFP and Access:
>>
>>SELECT ITEM_ID, SUM(QTY * RATE) AS AMOUNT FROM SALES;
>> WHERE BRANCH = '01';
>> GROUP BY ITEM_ID
>>
>>It took VFP 2min 35sec to do it, Access took 2min 10 sec. This was on a 200 MMX machine with 64MB RAM.
>
>
>Abdul, I am puzzled why the query was so slow in VFP. The poor performance suggests that VFP is not trying to optimize the query but rather is doing a table scan.
>
>I have some tables of similar size, probably with a similar structure (insofar a field lengths, etc), with the necessary fields indexed. However, if I do something like:
>
>SELECT ITEM_ID, QTY, RATE FROM SALES ;
>INTO CURSOR TEMP1 ;
>WHERE BRANCH = '01'
>
>This is a partially optimizable query. In a 1.5 million record table on my 200 MhZ PC, it usually takes less than 10 seconds to run in VFP. The SALES table is stored locally, not on a network drive.
>
>Then if I say:
>SELECT ITEM_ID, SUM(QTY * RATE) AS AMOUNT FROM TEMP1 ;
>GROUP BY ITEM_ID
>
>This usually takes a fraction of a second unless the TEMP1 cursor is huge.
>
>So overall, I typically get a sub-10 second return with a query such as yours on a 1.5 million record table. Maybe try SYS(3054) to determine how your query is actually being optimized.

Mark,
I tried your suggestion of SYS(3054) and I get the following message:
"Using Index tag branch to rushmore optimize table Sales"
"Rushmore Optimization level for table Sales: full"

The speed is still the same. I have plenty of disk space available (200MB). The funny thing is that Access if giving a better performance than VFP whereas I have seen a lot of Access bashing on this site.

Thanks,

Abdul Ahad