ADO MaxRows property and Oracle 7.3.4

Hi.

I need some suggestions on tuning my application's performance. First off, my limitations: I have to use ADO to access an Oracle 7.3.4 backend; the application's main architecture is fat client ODBC access; we don't use stored procedures; I'd like to stay away from using embedded Oracle hints if at all possible.

So, say I had a simple query:

SELECT Field1, Field2 FROM Owner.Table1 ORDER BY Field1;

Also, say Field1 is the primary key for Table1, and that Table1 has 500,000 records.

The user has performed a fairly useless query, so for performance sake, I just choose to return the first 250 rows. Here's the code so far--in pseudocode/VB code:

Dim setTable1 As New ADODB.Recordset, vtRows As Variant setTable1.MaxRows = 250
setTable1.Open "SELECT Field1, Field2 FROM Owner.Table1 ORDER BY Field1", adoConnection
vtRows = setTable1.GetRows( 250 )

The intention for the code above, is just for Oracle to return the first 250 rows. However, all indications seem to show that Oracle is doing a lot more. This query, even after ordering by the primary key and doing no filter, takes ~70 seconds. If I remove the ORDER BY clause, the operation takes <1 second.

Is there some way of telling ORACLE that I only care about 250 records, and it should finish the query as soon as it finds them.

Any help would be greatly appreciated.

Please respond to this post, or email me at Linus.Concepcion_at_marshmc.com.

Thanks. Received on Tue Jul 20 1999 - 00:05:11 CDT