Many applications for the enterprise, the Web and the desktop use SQL databases. The widespread adoption of SQL databases has put a spotlight on SQL application programming interfaces (API) and
performance issues related to SQL APIs.
Because SQL is declarative and non-procedural, application performance does not depend on techniques used to navigate a database. SQL application performance is instead a function of several
factors, including database design, network latency, and query optimization. Another factor is the influence of constituent software such as components and libraries. Poorly tuned queries and
sub-optimal coding techniques often contribute more to performance problems than the choice of SQL API. Nonetheless, the decision whether to use a proprietary (native) API or Open Database
Connectivity (ODBC) is the subject of much debate.
Once approach to comparing the performance of ODBC and proprietary APIs is to perform benchmarks. To do an “apples to apples” comparison of ODBC and native APIs, it’s necessary to use the API
directly without encapsulating it in intermediary software. For the tests reported here, the benchmark software used direct function calls to ODBC and the Oracle Call Interface (OCI). It did not
use intermediary software to wrap or encapsulate the SQL APIs.
Each benchmark test varied only the SQL API with other factors being held constant. The programs executed the same queries running against the same database and tables. The queries were launched
from the same client and executed on the same network and server.
The most recent series of benchmarks were for Oracle 8i and 9i. Earlier benchmarks, published in 1995, used Oracle 7 and version 2.x ODBC drivers. The latest tests used the DataDirect Connect for
ODBC 4.1 Oracle Wire Protocol driver from DataDirect Technologies (www.datadirect-technologies.com). This driver differs from
classical ODBC technology because it doesn’t use Oracle network libraries, unlike the driver used in the 1995 benchmarking.
The benchmarks included 10 different SQL queries (1 INSERT, 5 SELECT, 2 UPDATE, 2 DELETE). In 1995, the benchmark tests used nine queries and ODBC had a performance advantage on five of the nine.
The difference in execution time for ODBC and Oracle Call Interface varied as much as 25%. In the recent tests, ODBC had better performance on seven of the ten queries, with one being equal. The
difference in execution times between OCI and ODBC was much closer than in earlier tests. Considering average execution times, the ODBC performance advantage ranged from 0 to 1.18 seconds. For two
query types, the OCI performance advantage was 60 milliseconds and 260 milliseconds.
Click Here to Read Benchmarking SQL Application Programming Interfaces (.pdf)
By Ken North
