ODBC Performance
SQL
Query Performance Metrics for ODBC and Oracle Call Interface
In 2002, Ken North tested SQL query performance using
wire protocol ODBC drivers. Performance tests
comparing wire protocol ODBC with other API alternatives found no evidence of
an ODBC performance penalty.
TimesTen Performance Software is
raising the bar on SQL database performance with its in-memory database that provide an
ODBC interface. TimesTen and Compaq jointly demonstrated performance benchmarks in March
1999 (TimesTen running on Compaq servers). The benchmark peaked at:
1,560,000 separate SQL read operations per minute (26,000
per second)
708,000 SQL updates (11,800 per second)
336,000 SQL inserts (5,600 per second).
Since 1992 there has been much discussion about ODBC
performance. Unfortunately, much of the commentary has been by persons using RAD products,
database engines, or object layers that exhibit their own distinct performance behavior.
In simple terms, those solutions do not provide a true measure of ODBC performance.
PowerBuilder, SQLWindows, Delphi (Borland Database Engine), Access, Visual Basic with
Jet/DAO, C++ classes, components and other rapid development solutions all introduce
performance factors of their own.
Does this mean that you shouldn't use rapid application
development tools? No. Just be aware that some products don't deliver maximum performance
when it comes to processing queries. If you have performance constraints then you should
do some testing before committing to a product or a specific ODBC driver.
There have been several notable efforts to clarify ODBC
performance with benchmarks and performance tests. In some instances, the performance
tests compare ODBC with proprietary SQL APIs. One such benchmarking effort by Ken North
showed ODBC to be faster for 17 out of 29 tests. Click here to
read the APIBench (SQL API) performance test results.
"Performance
Testing, ODBC and Native SQL APIs" discusses performance testing
methods and programming APIBench, the SQL API benchmark suite. The article appeared in Dr.
Dobb's Sourcebook and it includes a link for downloading source code from Dr. Dobb's
server.
Larry Kostmayer's tests published in DBMS (October 1994) used Visual Basic and C
tests of DB-Library and ODBC. Kostmayer's tests showed no performance gap. Microsoft has
since adopted ODBC clients for Microsoft SQL Server TPC-C performance tests.
"Understanding
ODBC and Multidatabase APIs" (DBMS, March 1994) included informal
benchmarks that showed there were significant differences in the execution time of several
applications that were executing the same query against the same tables using the same
ODBC driver. Clearly execution time in this instance was a function of the application,
not of the query or ODBC.
An article in Data-Based Advisor benchmarked ODBC
with Oracle Objects for OLE. The results showed no ODBC performance penalty.
When evaluating whether to use ODBC to connect databases to
Oracle Open Gateways, Oracle conducted benchmarks that were reported in PC Week in 1995 and in the July 1996 issue of Oracle Informant. As a result of
those tests, Oracle decided to use ODBC.
Visual Basic
"Understanding
ODBC and Multidatabase APIs" (DBMS, March 1994) showed that performance
of a Visual Basic application differed significantly based on data access technique.
Operating with same query, tables, and drivers produced four different execution times
when using the data control, snapshots, dynasets and direct ODBC API calls. Direct calls
were the fastest while bound controls were the slowest.
copyright (C) 1997-2002, Ken North
Last update: September 22, 2002
ODBC Benchmark
Report
Ken North Home Page