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?