(ADO + Oracle)*MTS = Poor Performance 
Author Message
 (ADO + Oracle)*MTS = Poor Performance

My development team and I have run into a brick wall. Spray painted all over
that wall is ADO, Oracle and MTS. We are stress testing services for our
database application and have recorded the following benchmarks. These tests
do the following four things via ADO disconnected, batch updates a more
complete description of our data access method is described below.

1) Insert 2000 records (30 column table)
2) Insert 6000 records (5 column table)
3) Insert 2000 records (2 column table)
4) Insert 2000 records (3 column table)

The following times were logged for the 2 different back ends

4 Processor NT -SQL Server 7.0              12 processor sun 4500 running
Oracle 8i
---------------------------------------------------        -----------------
------------------------------------------
1)    20 seconds                                                46 seconds
2)    10 seconds                                                1 minute 47
seconds
3)     3 seconds                                                  40 seconds
4)     3 seconds                                                  40 seconds

Just FYI, we brought in an oracle consultant to "tune" the database and the
numbers posted by oracle DOUBLED!

We are using MS OLEDB for SQL Server for the SQL Server and MS OLEDB  or
Oracle for the Oracle DB via the method described at the end of this post.
Someone out there must be pushing the edge of MS data access technologies.
Please respond with any insight you might have to offer.

Thanks,
Tim Sublette

Method
---------------------------
'Record set settings
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockBatchOptimistic

For i  = 1 to 2000
    rs.addnew
    rs.fields(1).value = f1 : rs.fields(2).value=f2 : ...
    rs.movenext
next i

rs.Updatebatch



Sat, 31 Aug 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance
I wouldn't be surprised as sun 4500 are pretty old.  I think the issue would
be that if you put on 100 users the Oracle timings wouldn't change but sql
would.

IMHO of course!


Quote:
> My development team and I have run into a brick wall. Spray painted all
over
> that wall is ADO, Oracle and MTS. We are stress testing services for our
> database application and have recorded the following benchmarks. These
tests
> do the following four things via ADO disconnected, batch updates a more
> complete description of our data access method is described below.

> 1) Insert 2000 records (30 column table)
> 2) Insert 6000 records (5 column table)
> 3) Insert 2000 records (2 column table)
> 4) Insert 2000 records (3 column table)

> The following times were logged for the 2 different back ends

> 4 Processor NT -SQL Server 7.0              12 processor sun 4500 running
> Oracle 8i
> ---------------------------------------------------        ---------------
--
> ------------------------------------------
> 1)    20 seconds                                                46 seconds
> 2)    10 seconds                                                1 minute
47
> seconds
> 3)     3 seconds                                                  40
seconds
> 4)     3 seconds                                                  40
seconds

> Just FYI, we brought in an oracle consultant to "tune" the database and
the
> numbers posted by oracle DOUBLED!

> We are using MS OLEDB for SQL Server for the SQL Server and MS OLEDB  or
> Oracle for the Oracle DB via the method described at the end of this post.
> Someone out there must be pushing the edge of MS data access technologies.
> Please respond with any insight you might have to offer.

> Thanks,
> Tim Sublette

> Method
> ---------------------------
> 'Record set settings
>     rs.CursorLocation = adUseClient
>     rs.CursorType = adOpenForwardOnly
>     rs.LockType = adLockBatchOptimistic

> For i  = 1 to 2000
>     rs.addnew
>     rs.fields(1).value = f1 : rs.fields(2).value=f2 : ...
>     rs.movenext
> next i

> rs.Updatebatch



Mon, 02 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance
I would buy that if there was a 10-30% difference. But 10 seconds vs. 1
minute 47 seconds, I could get better performance out of a TRS-80 running
Oracle .001.

I did find out, however, that when I issue a rs.UpdateBatch, ADO is
basically executing 12,000 inserts with a commit after each one. The commit
caused 200 seconds of IO wait time for the run. That just about covers the
amount of time diff between SQL Server and Oracle.

I would be willing to bet the SQL server executes all statements in memory
then commits the load at the end and Oracle is not. Can anyone confirm this?
Can anyone tell me how to make Oracle stop that?

Thanks for the help.
Tim


Quote:
> I wouldn't be surprised as sun 4500 are pretty old.  I think the issue
would
> be that if you put on 100 users the Oracle timings wouldn't change but sql
> would.

> IMHO of course!



> > My development team and I have run into a brick wall. Spray painted all
> over
> > that wall is ADO, Oracle and MTS. We are stress testing services for our
> > database application and have recorded the following benchmarks. These
> tests
> > do the following four things via ADO disconnected, batch updates a more
> > complete description of our data access method is described below.

> > 1) Insert 2000 records (30 column table)
> > 2) Insert 6000 records (5 column table)
> > 3) Insert 2000 records (2 column table)
> > 4) Insert 2000 records (3 column table)

> > The following times were logged for the 2 different back ends

> > 4 Processor NT -SQL Server 7.0              12 processor sun 4500
running
> > Oracle 8i

----------------------------------------------        ---------------

- Show quoted text -

Quote:
> --
> > ------------------------------------------
> > 1)    20 seconds                                                46
seconds
> > 2)    10 seconds                                                1 minute
> 47
> > seconds
> > 3)     3 seconds                                                  40
> seconds
> > 4)     3 seconds                                                  40
> seconds

> > Just FYI, we brought in an oracle consultant to "tune" the database and
> the
> > numbers posted by oracle DOUBLED!

> > We are using MS OLEDB for SQL Server for the SQL Server and MS OLEDB  or
> > Oracle for the Oracle DB via the method described at the end of this
post.
> > Someone out there must be pushing the edge of MS data access
technologies.
> > Please respond with any insight you might have to offer.

> > Thanks,
> > Tim Sublette

> > Method
> > ---------------------------
> > 'Record set settings
> >     rs.CursorLocation = adUseClient
> >     rs.CursorType = adOpenForwardOnly
> >     rs.LockType = adLockBatchOptimistic

> > For i  = 1 to 2000
> >     rs.addnew
> >     rs.fields(1).value = f1 : rs.fields(2).value=f2 : ...
> >     rs.movenext
> > next i

> > rs.Updatebatch



Mon, 02 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance
Try making the transaction explicit, e.g.

-------------
'Record set settings
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockBatchOptimistic

rs.activeConnection.BeginTrans
For i  = 1 to 2000
    rs.addnew
    rs.fields(1).value = f1 : rs.fields(2).value=f2 : ...
    rs.movenext
next i

rs.Updatebatch
rs.ActiveConnection.CommitTrans

Regards

Paul Hatcher, MCSD
Principal Consultant
Graduate Associates Ltd



Tue, 03 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance

1.) The MS ODBC Driver for Oracle performs better than the OLE DB provider.

2.) If any of your columns are numeric, then odds are that the SQL Server
test
     results in a single statement compilation (all values bound) vs. Oracle
getting
     a different statement for each row (numeric columns not bound).

3.) Wrapping a transaction around your update will help (as already noted).

4.) You mention MTS - don't use distributed transactions. In my most recent
      benchmarks I noted that timing for SQL Server is roughly the same
using
      ADO connection based and DTC transactions. This is not true for
Oracle;
      performance is on average 5-6 times worse using DTC.

5.) Database tuning for Oracle is extremely important, but few people have
      the knowledge to properly tune the engine for a specific environment.
      I have found that the Oracle BDE team can help significantly, but
these
      guys aren't available for general consulting.

6.) Install Oracle on the same NT box for an apples-to-apples comparison.
     (However, you should get much better throughput on the Sun than what
     you are reporting.)

7.) ADO offers only roughly 50% the throughput vs. the ODBC API. Also,
      you could perform bulk inserts with the ODBC API.

8.) If you aren't concerned with portability then use Oracle Object for OLE.

In my most recent benchmarks, Oracle edged out SQL Server by a
narrow margin. I ran Oracle 8.1.5 and SQL Server SP1 on the same
hardware to minimize differences in the test environment. I was able to
get 400,000 more method calls through in the Oracle test - equivalent
to 2,000,000 additional rows added to the database.

I guess I have to eat crow about previous statements that SQL Server
will always outperform Oracle on the same hardware.

However, the SQL Server numbers are able to obtain with a default
install. For Oracle, I had to tweak the parameters extensively.

Mike

Quote:

>My development team and I have run into a brick wall. Spray painted all
over
>that wall is ADO, Oracle and MTS. We are stress testing services for our
>database application and have recorded the following benchmarks. These
tests
>do the following four things via ADO disconnected, batch updates a more
>complete description of our data access method is described below.

>1) Insert 2000 records (30 column table)
>2) Insert 6000 records (5 column table)
>3) Insert 2000 records (2 column table)
>4) Insert 2000 records (3 column table)

>The following times were logged for the 2 different back ends

>4 Processor NT -SQL Server 7.0              12 processor sun 4500 running
>Oracle 8i
>---------------------------------------------------        ----------------
-
>------------------------------------------
>1)    20 seconds                                                46 seconds
>2)    10 seconds                                                1 minute 47
>seconds
>3)     3 seconds                                                  40
seconds
>4)     3 seconds                                                  40
seconds

>Just FYI, we brought in an oracle consultant to "tune" the database and the
>numbers posted by oracle DOUBLED!

>We are using MS OLEDB for SQL Server for the SQL Server and MS OLEDB  or
>Oracle for the Oracle DB via the method described at the end of this post.
>Someone out there must be pushing the edge of MS data access technologies.
>Please respond with any insight you might have to offer.

>Thanks,
>Tim Sublette

>Method
>---------------------------
>'Record set settings
>    rs.CursorLocation = adUseClient
>    rs.CursorType = adOpenForwardOnly
>    rs.LockType = adLockBatchOptimistic

>For i  = 1 to 2000
>    rs.addnew
>    rs.fields(1).value = f1 : rs.fields(2).value=f2 : ...
>    rs.movenext
>next i

>rs.Updatebatch



Wed, 04 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance
What's the performance difference between the MS ODBC driver and the
ADO driver and how does the Oracle ODBC compare?

Regards

Paul Hatcher, MCSD
Principal Consultant
Graduate Associates Ltd



Sun, 08 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance

I keep meaning to formalize my results and publish a summary
along with source. There is an intent on my part to publish the
results of my current testing, which I have every intent of covering
both SQL Server and Oracle (DB2 also, if time permits).

As to general performance of the previous tests - there is very
little measurable difference at single user loading. However,
as the load increases the latency becomes significantly more
pronounced. At 100 clients running 1 second "think time" the
OLE DB provider produced a communications failure within
15 minutes. The ODBC driver produced a similar fault, but
the same loading ran for roughly 24 hours before failure.

These results were produced against MDAC 2.1 SP2.
Recent testing against MDAC 2.5 / 2.6 have shown an
unexpected change in behavior (for the better) with the
ODBC driver. I have not retested the OLE DB provider.

Mike

Quote:

>What's the performance difference between the MS ODBC driver and the
>ADO driver and how does the Oracle ODBC compare?

>Regards

>Paul Hatcher, MCSD
>Principal Consultant
>Graduate Associates Ltd



Mon, 09 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance
Thanks, a client of mine is starting to get significant numbers of
users against Oracle instead of SQL Server so I'm trying to ensure we
don't get burned by differences in the database engines/ADO providers.

Regards

Paul Hatcher, MCSD
Principal Consultant
Graduate Associates Ltd



Tue, 10 Sep 2002 03:00:00 GMT  
 (ADO + Oracle)*MTS = Poor Performance

BTW, with appropriate tuning of the Oracle server you can
support up to 60 in-call objects enlisted in DTC transactions
per MTS server. Beyond this and you will eventually see
session exhaustion on the database server.

In a heavily utilized environment you need a server farm
at the MTS layer. I am aware of sites running between
18 and 60 MTS servers and still experiencing issues
with performance that arise before the melt-down.

If you avoid distributed transactions then you will not
experience the problem related to MTS / DTC and
the DBMS (note that the issue isn't restricted to
Oracle, the problem simply arises at a lower load
when using XA transactions).

Mike

Quote:

>Thanks, a client of mine is starting to get significant numbers of
>users against Oracle instead of SQL Server so I'm trying to ensure we
>don't get burned by differences in the database engines/ADO providers.

>Regards

>Paul Hatcher, MCSD
>Principal Consultant
>Graduate Associates Ltd



Wed, 11 Sep 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Poor performance of MTS Componant

2. Poor performance of MTS Componant

3. help with oracle performance : using ado, oracle longraw fields

4. help with oracle performance : using ado, oracle 8 longraw field

5. help with oracle performance : using ado, vb6, oracle 8 longraw field

6. Poor performance in query

7. poor performance

8. very very poor plotting performance in vb.net

9. Poor SQL Server 6.5 performance with Image Datatype

10. SQL Help... Poor Performance (RDO)

11. OLE container control performance poor?

12. poor network performance - help

 

 
Powered by phpBB® Forum Software