ODBC Alternatives

Alternatives to ODBC include ADO (which is used in the Active Server Pages examples in Chapter 9) and OLE DB. Currently, most ODBC alternatives are implemented as wrappers around ODBC. If you are creating an application accessing a database that uses direct ODBC calls, it will virtually always perform better than ADO. In a client application, this difference might not be too important because most of the time is spent waiting for the relatively slow user . In the world of server applications, however, the program will seldom have time to catch its breath , and the best performance possible is almost always best.

To test my assumptions, I ran a series of tests using Microsoft Application Performance Explorer. Usually it is not necessary to draw conclusions based on very small differences in performance, but the differences I discovered were not small. Figure 8-2 shows the result of the third test run using ADO as the data transport mechanism. It is important to note that the first test will be noticeably slower than subsequent tests due to the startup tasks associated with each protocol. In general, these differences should be ignored especially in long-lived processes such as server applications. Figure 8-3 shows the results from the third run of the same test using the ODBC API. The difference is dramatic.

Overall calls per second for ODBC were almost twice those for ADO; client calls per second for ODBC were over twice those for ADO. The total test times were also much shorter with the direct ODBC calls. Of course, your mileage may vary. As with any benchmarks, these measurements may or may not be representative of your application.

Figure 8-2 Microsoft Application Performance Explorer output for an ADO test.

Figure 8-3 Application Performance Explorer output for an ODBC API test.

The only other reasonable alternatives that provide database access are the various proprietary APIs provided by database vendors. Of course, the difficulty involved in writing applications to proprietary APIs is exactly why ODBC was invented. If you write to the proprietary API and supporting another database vendor's product becomes important, a rewrite is likely on the horizon. Additionally, database vendors are increasingly moving away from support for their proprietary APIs. SQL Server supports a proprietary API: dbLib. This API has not been changed to support all new features of SQL Server 7.0, and the presumption is that it will not be updated to fully exploit future releases.