INFO: ADO & ODBC performance test Oracle rdb

KingSunSha 2002-05-09 11:54:52

下面的文章从公司的news group中摘录，希望对大家有所帮助

Report on a performance test of different ODBC drivers towards rdb.

Test Team
---------
Anders Qvarnström (AQVA) Corporate Technology
- Research, testing, Oracle and database specialist
Emil Kvarnhammar (EMKV) Corporate Technolgy
- VB and ADO specialist

If you have any questions, don't hesitate to contact us.

-------------------------
Hi!
I have on request from MTC/GADD development team tested
alternative ODBC drivers for connection to MHS Rdb database. The
connections are made and tested from VB6 SP 1 - ADO 2.5 to an
Oracle Rdb V7.0-21 database.
I have as requested tested connection performance, query
performance and the possibility to use the query cancel function.

Summary
-------
* Recommendation is to use same ODBC driver as today,
e.g. Microsoft ODBC for Oracle.
* The query cancel function is working
* Query performance is very much depending on ...
- network (big difference between LAN and WAN)
- workload on the MHS server
- the ODBC driver you use
* Connection (logon) performance is very much depending on ...
- network (big difference between LAN and WAN)
- workload on the MHS server
- sql/services connection pooling, e.g. if there are
a prestarted connection process that can be used

The tests can be summaries in following matrix

Query Query Logon Logon Cancel
Driver LAN WAN LAN WAN function
------ ----- ----- ----- ----- --------
MS Fast Medium Fast Medium Working
Ora73 Fast Medium Fast Medium Working
Ora8 Medium Slow Fast Medium Working
Rdb Medium Slow Fast Fast Working

MS = Microsoft ODBC for Oracle
Ora73 = Oracle73
Ora8 = Oracle ODBC Driver
Rdb = Oracle ODBC driver for Rdb

The above abbreviations for the ODBC drivers will further on be
used in this report.

ODBC drivers
------------
There are four different ODBC drivers in ICC2 that can be used to
connect to an Rdb database.

* Microsoft ODBC for Oracle, v.2.573.5303.00 (MS)
- support for v. 2.5 of the Microsoft ODBC specification
- uses sql*net 2.3 protocol
* Oracle73, v. 2.5.3.1.6 (Ora73)
- support for v. 2.5 of the Microsoft ODBC specification
- uses sql*net 2.3 protocol
* Oracle ODBC Driver, v. 8.0.5.7 (Ora8)
- support for v. 3.00 of the Microsoft ODBC specification
- uses Net8 protocol, should preferable be used towards
Oracle 8 databases
* Oracle ODBC driver for Rdb, 2.10.14 (Rdb)
- support for v. 2.01 of the Microsoft ODBC specification
- uses tcp/ip protocol and attachment statement to the
database, sql*net and sql/services is not used

These four ODBC drivers have been tested.

Query performance
-----------------
Query performance is in addition to the query itself very much
depending on ...
- Network, LAN or WAN, if there is a lot of data to be fetched
- Workload on the Rdb (mhs) server
- ODBC driver

The query performance depends very much on the ODBC driver.

Query time Query time
Driver LAN (s) WAN (s)
------ ----------- -----------
MS 0.15 1.5-5.0
Ora73 0.15 1.5-5.0
Ora8 0.8 60-80
Rdb 0.6 60-80

Test-query: select * from v_iwaaa limit to 500 rows
Test machine LAN: MHS830 (all LAN tests are towards this server)
Test machine WAN: MHS141 (all WAN tests are towards this server)

The best ODBC drivers when comes to query performance is the MS
and the Ora73 ODBC Drivers. The Ora8 and Rdb ODBC drivers are very
slow in query performance, more than ten times slower.

Note! The figures above are very approximately and depends very
much on server performance & workload and network capacity & load.

Conclusion:
=> Use MS or Ora73 ODBC driver when comes to query performance.

Logon performance
-----------------
Logon (time to connect) performance is very much depending on ...
- network (big difference between LAN and WAN)
- workload on the MHS server
- sql/services connection pooling, e.g. if there are
a prestarted connection process that can be used.
- ODBC driver

Logon time Logon time
Driver LAN (s) WAN (s)
------ ----------- -----------
MS 0.5 2-5
Ora73 0.5 2-5
Ora8 0.6 3-8
Rdb 0.06 0.4-1.0

MS, Ora73 and Ora8 ODBC drivers uses Sql/services to connect to
Rdb. Sql/services (sql*net gateway to Rdb) is configured to have
pre-started connection processes. If there are a free pre-started
connection process when trying to connect, the connection (logon)
time is comparable to connecting to an Oracle database. If
sql/services has to start a new connection process, the connecting
time will be much longer, apprx 10-20 seconds over LAN and 25-30
seconds over WAN.
The above test results are when there are a pre started
sql/services connection process to be used.

An investigation could be done to see if there is a possibility to
have more prestarted sql/services connection processes.

Rdb ODBC driver doesn't use sql/services to connect to Rdb. It
uses tcp/ip connecting to server and then the rdb attachment
statement for connecting to Rdb.

Conclusion:
=> Use Rdb ODBC driver when only comes to logon/connecting
performance. But when in addition comes to query performance use
MS or Ora73 driver.

Cancel function
---------------
The query cancel function works for all four ODBC drivers.

To be able to use the query cancel function there are some
prerequisites to the result set.

The cursorlocation for the result set has to be set to
adUseClient.

Dim oRS As ADODB.Recordset
oRS.CursorLocation = adUseClient

When opening the resultset you have to use the switches
adOpenDynamic and adAsyncFetch.

oRS.Open strSqlStmnt, oCn, adOpenDynamic, adLockReadOnly,
adAsyncFetch

To cancel the query do cancel on the resultset

oRS.Cancel

As mentioned before the query cancel function works for all ODBC
drivers. The time to do cancel query depends on the driver.

Apx Cancel Apx Cancel
Driver time LAN (s) time WAN (s)
------ ------------ ------------
MS 0.01 0.01
Ora73 0.01 0.1
Ora8 0.02 2
Rdb 0.02 2

Conclusion:
=> The cancel function works very well. Use MS ODBC driver as this
seems to has the best performance.

Conclusion
----------
The Microsoft ODBC for Oracle is the one to be recommended of the
four ICC2 ODBC drivers to be used against Rdb databases.

The MS ODBC driver is one of the best drivers in all tested
categories except logon/connecting time. Oracle73 driver is
comparable with the MS driver in all tests. But since you already
use the MS driver there is no reason for changing. For fast logons
the Rdb driver is the fastest, but it has very bad query
performance and it only support ODBC 2.0 specification, so this
can't be recommended.

=> Recommendation: Use driver Microsoft ODBC for Oracle.

...全文