>Hi all,
>
>I have a problem whereby Rushmore seems to fail on a largeish table; 100k records 200 fields. Once the table goes beyond a certain size MIN(), MAX() and TOP 1 simple seem to stop being optimized properly even though SYS(3054) still indicates that it is being optimized.
>
>An example:
>* note Orders is 100k records and 200 fields wide
>* Indexed on OrdNo and LEFT(OrdNo,1)
>
>* This takes 50 seconds
>SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo > cLastOrdNo ;
> AND Left(OrdNo,1) = cDept ORDER BY OrdNo
>
>* but seperate into a narrower table
>SELECT OrdNo FROM Orders INTO TABLE OrdersNoOnly
>INDEX ON OrdNo TAG OrdNo
>INDEX ON LEFT(OrdNo,1) TAG Dept
>
>* This takes 0.7 seconds although the table is still 100k records long
>SELECT TOP 1 OrdNo FROM OrdersNoOnly WHERE OrdNo > cLastOrdNo ;
> AND Left(OrdNo,1) = cDept ORDER BY OrdNo
>
>Has anyone ever encountered anything similar?

If you have antivirus real-time scanning enabled, try disabling it for testing, or configure it to not scan VFP tables and temp files/folders.

Just how large is the "largeish" table? As an estimate, if each column averages 10 bytes then each row is about 2K bytes; 100k rows would be about 200MB. Plus there are the sizes of any associated .FPT and .CDX files. It might be that with the "full" table, VFP is just running out of RAM and is having to page to disk.

Looking at RAM usage in Task Manager while you run these queries, how much RAM is VFP peaking at? You can experiment with SYS(3050) to see what's specified by default and play around with giving VFP more RAM.