Evaluating a new technology is difficult, but one source of valuable information is the early adopter, particularly those early adopters that use the technology to build products. You'll find their comments interesting as we explore facts and myths about ODBC and multidatabase APIs.
Andy Barnhart of SAS Institute Inc. (Cary, N.C.) compared times for ODBC and native Oracle applications. He reported that downloading 5000 rows from an Oracle table was slower with ODBC, but observed that the difference was negligible when doing ad hoc queries to gather aggregate statistics or import subsets. KCI Computing Inc.'s (Los Angeles, Calif.) financial modeling product is an APL application that uses SQL databases. Chris Shaw of KCI posted on CompuServe a summary of his company's ODBC tests using a variety of servers, drivers, and SQL Server's native API (DBLIB). He reported that ODBC itself doesn't necessarily impose a performance penalty. Selection of networking software and physical layout was more critical for his project. In one test, the individual insertion time for 500 row tables using one ODBC driver was two seconds less than the time using DBLIB. You can get more details by downloading ODBCPE.ZIP from the ODBC libraries of WINEXT. Ken Elston of Paradigm/RSG (Bothell, Wash.) reports that there is no noticeable difference in performance between ODBC and DBLIB when using Visual Basic to make direct API calls.
Microsoft's case studies confirm this behavior. One Microsoft tester said, "It would make sense for an ODBC program to run faster than a DBLIB program on a faster CPU: the implementation of the SQL Server Driver is marginally more efficient than DBLIB for programs that use bound local storage to retrieve data. . . the driver directly transfers data from the net to the users' bound buffers, rather than double buffering, which is the way DBLIB is written. ODBC does add a certain amount of computational overhead, in that escape sequences are scanned for ODBC escape clauses and certain nonstandard statements. . . This performance overhead can be minimized by turning off scanning in the driver, as long as the application does not need to worry about supporting ODBC escape sequences." At the ODBC 2.0 Developers Conference, Microsoft discussed informal, unaudited benchmarks that showed little difference between ODBC and DBLIB (0.09 versus 0.08 TPS, respectively). They reported that the overhead of the Driver Manager for a SQLFetch is 23 lines of C (95 assembly instructions). On a 486/50, it requires 6.64 microseconds to execute, or as Kyle Geiger of Microsoft stated, "10,000 times in the time it takes to blink."
David Yach, Engineering VP at Watcom International Corp. (Waterloo, Ontario) says, "Watcom SQL includes an extremely efficient ODBC driver. The engine supports advanced features required by ODBC such as bidirectional scrolling cursors, without having to simulate these features in the driver." Some drivers outperform native applications because a skilled driver developer generally possesses a better understanding of the target DBMS than the average programmer, and certainly more than someone learning a new API. Terry Cunningham of Crystal Services (Vancouver, B.C.) reports that QELIB 2.0 was faster than some of their native SQL implementations. Q+E Software Inc. (Raleigh, N.C.) reported a significant performance increase between their ODBC Driver Pack 1.0 and 1.1 drivers, because of improvements in caching and query optimization. Finally, driver performance is likely to improve because of competition among ODBC driver vendors.
David Osborne, VP of Bankers Trust (New York, N.Y.), reports that they use Visual Basic and Access clients with drivers for Access, Excel, Gupta, and SQL Server to build multiple office-automation applications such as portfolio management, security pricing, and accrual posting to accounting systems. He reports that there are still some problems with cursors and parameters for stored procedures but they wrote their own ODBC VBX, which worked out well. Greg Brewer of Mallinckrodt Systems (Ann Arbor, Mich.) uses Visual C++ and Crystal Reports to develop a networked manufacturing system that will access R:Base, FoxPro, dBASE, and AS/400 SQL databases. He uses the R:Base driver to capture and analyze test data and feels that ODBC can be a very valuable tool for connectivity to different DBMS platforms. Joachim Wester (Stockholm, Sweden) uses VB, C++, and Access, for sales and marketing, point of sale, and distribution systems for the book and record industries. He cites C++ for speed and flexibility and VB for simplified ODBC programming. Wester reports that simple queries are measurably faster in C++ than in Visual Basic, but the difference when doing complex queries is "virtually non-existent." When discussing the large databases of the sales and marketing system and the requirement to access several large databases, he says, "ODBC appears to be the ideal solution." When discussing the topic of ODBC overhead, he says, "Some engines do not show any sign of overhead at all." He reports significant differences in performance when testing various drivers for SQL Server, Watcom, dBASE, and Quadbase.
Glenn Bader reports that Tony's Food Service (Marshall, Minn.) is downsizing mainframe integrated business systems to client/server and using Visual C++ and ODBC for all database access to DB2, SQL Server, Informix, Access, and Paradox. The company is using Micro Decisionware (MDI) and Q+E drivers for DB2, and Q+E for Informix. According to Bader, the company built class libraries corresponding to each of its business units and used order entry as a proof-of-concept application. He reports some queries took longer in ODBC than DBLIB, but this wasn't significant because they still took an average of only 0.10 to 0.15 seconds using ODBC. He feels there could be improvement in ODBC's column binding and connection handling, but states, "We are happy with the flexibility and future maintainability of our application using ODBC."
J.D. Evans, Jr. of ETN Corp. (Montoursville, Pa.) is developing a client application for a major information systems vendor. He is using Visual Basic and Access with SQL Server NT. He states, "ODBC is (really) a big advance in RDBMS connectivity and much better than using DBLIB, VBSQL.VBX, or whatever." Alan Buck of Paradigm/RSG (Bothell, Wash.) changed from VBSQL to VB and ODBC for a risk management application using Microsoft and Sybase SQL Servers. When testing his risk management application, Buck found that retrieving single rows across the network (using a 486/50 running SQL Server NT) is practically instantaneous. He reports that he wrote to the ODBC API because the object layer of VB 2.0 was significantly slower, but that the 3.0 object layer has improved. His current application runs with Microsoft and Sybase SQL Server but he expects to connect to Oracle and DB2 as the application grows to 1,300,000 rows. Alan states, "The more I use ODBC, the better I like it."
David Allen of Electronic Data Systems Corp. (EDS, Plano, Texas) used QELIB 1.x and then Q+E's Multilink product when it supported ODBC drivers. EDS is doing office-automation projects for an engineering division of General Motors, and Allen reports that they've used connections to dBASE, Paradox, and SQLBase, with Ingres on the schedule. He reports that he's used Q+E as a portability solution because his group is still actively evaluating server products. Chriss Hill of Northhampton, U.K. used the dBASE ODBC driver and the ODBC SDK for a contact and business list-management application that involved a lot of data entry from remote sites to a main office. He feels that, for his application, ODBC seemed very dependent on the hardware. He says, "It crawls on a 4MB 386/33, but flies on a 486/DX2 with 16MB RAM."