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
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
Dim setTable1 As New ADODB.Recordset, vtRows As Variant
setTable1.MaxRows = 250
setTable1.Open "SELECT Field1, Field2 FROM Owner.Table1 ORDER BY Field1",
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.