ADO MaxRows property and Oracle 7.3.4

ADO MaxRows property and Oracle 7.3.4

Post by Linus Concepcio » Wed, 21 Jul 1999 04:00:00



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.

 
 
 

ADO MaxRows property and Oracle 7.3.4

Post by Joe Spanice » Wed, 21 Jul 1999 04:00:00


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

 
 
 

ADO MaxRows property and Oracle 7.3.4

Post by Jeff Ashle » Wed, 21 Jul 1999 04:00:00


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 . . .

> 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

 
 
 

ADO MaxRows property and Oracle 7.3.4

Post by John Hurre » Fri, 23 Jul 1999 04:00:00


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


>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 . . .

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.

 
 
 

ADO MaxRows property and Oracle 7.3.4

Post by Jeff Ashle » Fri, 23 Jul 1999 04:00:00


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

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

> >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 . . .

> 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.

 
 
 

1. 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.


Thanks.

2. W4GL ---- H E L P !!!!

3. MaxRows and RowsetSize properties in User Connection Designer

4. SQL Join Issue.

5. How to use MaxRows Property without an rdoQuery Object

6. SQL: include "'" within the statement

7. SELECT TOP vs ADO MaxRows

8. Connecting DOS machine to NT server

9. MaxRows - Oracle 2.73.726900 (MDAC) Driver Help

10. field properties for ADO oracle providers

11. Setting property OLEDB Provider for Oracle in ADO

12. ado properties from oracle on asp -pls help

13. xpath ado vs ado.net properties