Very Computer

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.

Thanks.

Hi,

An ORDER BY clause tells the ORACLE database engine to order the rows
returned using
the field specified. ORACLE will first order the 500,000 rows it has in
the table,
it will then send all the rows selected back to VB which will then
display 250 rows.

The 70sec is the ordering been performed, removing the ORDER BY clause
tells ORACLE
to just return the selected rows which is quite quik.

Thats how it works and the time difference is what you would expect.

But if you just want to return 250 rows, not fussed with the order of
results,
use the following

setTable1.Open "SELECT Field1, Field2 FROM Owner.Table1 WHERE rownum
<251"

Hope this helps

Joe

Yeah, but if you are ordering by primary key, there should be _zero_
difference!  I don't work with Oracle, but there must be something getting
missed here . . .

On Tue, 20 Jul 1999 08:01:14 -0400, Jeff Ashley

A primary key is merely a field that is guaranteed to be unique, not
fast. An INDEX is a set of information about the physical location of
data and therefore WILL speed performance.

The bad news is, in either case using an ORDER BY clause will incur a
performance hit because Oracle has to sort the recordset before it can
return the first X number of records you request.

But _not_ if you are ordering by an indexed field, right?