Examining JDBC Drivers

Current drivers fit into one of four categories

Mukul is a staff engineer at Digital Focus in Fairfax, Virginia. He can be contacted at [email protected].

Java Database Connectivity (JDBC) provides a programming-level interface for communicating with databases in a uniform manner, similar to Microsoft's Open Database Connectivity (ODBC). Like ODBC, JDBC is based on the X/Open SQL Call-Level Interface (for more information, see "SQL Access Group's Call-Level Interface," by Roger Sippl, Dr. Dobb's Sourcebook, January/February 1996; available electronically, see "Resource Center," page 3). JavaSoft has taken the first step toward assuring that JDBC implementations comply with the specification by defining a "JDBC-compliant" designation. To use this designation, a JDBC driver must support the ANSI SQL 2 Entry Level Standard and must also pass conformance tests provided by JavaSoft.

There are currently more than 20 commercially available JDBC implementations. In implementing JDBC drivers, vendors have considerable freedom to distinguish themselves, in both their strategies for implementing the JDBC specification and in any extensions they provide. Still, current drivers fit into one of four categories defined by JavaSoft:

• JDBC/ODBC bridge.
• Native-API, partly Java driver.
• Network-protocol, all-Java driver.
• Native-protocol, all-Java driver.

In this article, I'll briefly discuss each of these categories, and then compare them in terms of performance.

JDBC/ODBC Bridge (Type I)

The JDBC/ODBC Bridge was jointly developed by JavaSoft and Intersolv to take advantage of the large number of ODBC-enabled data sources. The client-side Java applet (or application) is written using the JDBC API. The bridge converts JDBC calls into ODBC calls and passes them to the appropriate ODBC driver for the back-end database (see Figure 1). The main advantage of this bridge is that applications can easily access databases from multiple vendors by choosing an appropriate ODBC driver. However, this type of database connectivity does involve considerable overhead and complexity, because calls must go from JDBC to the bridge to the ODBC driver, and finally from ODBC to the native client-API to the database.

Additionally, this type of driver does not allow for just-in-time delivery of Java applets. Native code must be preinstalled on any client that directly uses the JDBC/ODBC Bridge to implement the API calls. The requirement to preinstall software implies the same type of administrative burden you get with traditional client-server applications. Thus, the JDBC/ODBC Bridge by itself does not solve the problem of client program deployment.

Native-API, Partly Java Driver (Type II)

Type II, the native-API, partly Java driver, is two-tier; that is, the JDBC driver requires a vendor-supplied library to translate JDBC functions into the DBMS's specific query language (for example, the library for Sybase is dblib, for Oracle it is ocilib, and so on). These drivers are usually written in some combination of Java and C/C++, since the driver must use a layer of C to make calls to the vendor libraries (written in C); see Figure 2.

Type II drivers, like the JDBC/ODBC Bridge, require that code (that is, the vendor library) be installed on each client. Thus, they have same software-maintenance problems as the bridge. However, Type II drivers are faster than Type I because the extra layer of translation to ODBC is removed.

Network-Protocol, All-Java Driver (Type III)

The network-protocol, all-Java Type III driver translates JDBC calls into a database-independent network protocol, which is then translated into database-specific API calls by a middle-tier server. (The middle-tier server may, in fact, use a Type I or Type II driver if written in Java.) The overall architecture consists of three tiers: the JDBC client and driver, middleware, and database(s) being accessed. The small JDBC driver (usually 200 KB or less) executes on the client and only implements the logic needed to pass SQL commands over the network to the JDBC server, receive the data back from the server, and manage the connection. Type III drivers allow for just-in-time client deployment.

The JDBC server manages multiple connections to the database, as well as exception and status events resulting from SQL execution. It also packages the data for transmission over the network to JDBC clients.

The middleware server component can be implemented as a native component or written in Java. Native implementations connect to the database server using either a vendor's client library or ODBC. Symantec's dbAnywhere and Intersolv's SequeLink both fall in this category, although SequeLink does not require that any database client libraries be installed on the server -- it uses its own libraries. The server has to be configured for the database(s) being accessed. This could involve setting port numbers, database-specific environment variables (for instance, DSQuery with Sybase), database-specific parameters (logging, translation), and other parameters that the server may require; see Figure 3. If the middleware server is written in Java, it can use any JDBC-compliant driver to communicate with the DBMS via the database vendor's proprietary protocol.

There is no need to configure the server for target database(s). For other features like connection caching, number of listener threads, and so on, the required parameters have to be set. Weblogic's T3 server falls in this category. The JDBC T3 driver, executing on the client, takes the name of the driver to be used to communicate with the DBMS as well as other properties (user name, password, database name, and so on) needed by this driver, then passes this information to the T3 server. The server loads the passed-in driver class and uses it to talk to the database(s). The server still has to be loaded with the database vendor libraries and/or ODBC drivers, in case it is passed a Type I or Type II driver class name. See Figure 4.

Type III drivers are best suited for Internet/intranet-based, multiuser data-intensive applications, where a large number of concurrent data operations such as queries, searches, and so on, are expected and scalability and performance is a major factor. The server can handle multiplexing management among several databases, can provide logging and administration facilities, can load balancing features, and can support catalog and query caches. In addition to the aforementioned issues, most three-tier web database applications involve security, firewalls, and proxies. Type III drivers address these issues.

A major disadvantage of network-centric drivers is that the server component is proprietary middleware; each vendor uses their own middleware for communication over the network. Thus, enterprise users fail to take advantage of capabilities provided by CORBA products and future upgrade paths.

Openjdbc from I-Kinetics is a network-centric driver that uses CORBA as middleware glue to bind the JDBC client and server. This tool takes advantage of CORBA's distributed computing infrastructure (an industry-accepted open standard) to handle all communication requirements. The driver becomes an assembly of cooperating objects rather than a single server. IKinetics is the only vendor I was able to locate that currently takes this approach.

Native-Protocol,All-Java Driver (Type IV)

Type IV, native-protocol, all-Java drivers convert JDBC calls directly into the network protocol used by the specific database vendor. These drivers can be written entirely in Java and (like Type III drivers) can provide for just-in-time delivery of applets. Because these drivers translate JDBC directly into the native protocol without the use of ODBC or native APIs, they can provide for very high-performance database access. These drivers can only be made available from the DBMS vendors due to the fact that the knowledge of protocol lies with the vendor. There are few Type IV implementations available today, but the number should increase in the coming months; see Figure 5.

Performance Issues

There are a number of ways to test different types of drivers. My purpose here is not to do a comprehensive benchmark of the various drivers, but to provide insight as to how the different categories of drivers perform, from the application perspective.

Comparing the Type I and Type II drivers with Type III and Type IV drivers is not informative, since these two groups have different deployment requirements. Consequently, I'll provide a comparison of Type I versus Type II, and Type III versus Type IV, using the following parameters as benchmarks:

• Connection times.
• Result-retrieval time.
• Stored-procedure execute time.
• Transaction-execute time.
• Concurrent-query execute time.

The client machine on which I loaded all the drivers was a 200-MHz Pentium with 24 MB of RAM running Sybase OpenClient Release 11 under Windows NT 4.0. The server machine (database) I used in the tests was a 200-MHz Pentium with 32 MB of RAM running Sybase SQL Server Release 11 under Windows NT 4.0. The network was a ten-Mbits/sec Ethernet, and the Java platform used for developing the test applet was Sun's JDK 1.1. The drivers I tested included Intersolv/JavaSoft's JDBC/ODBC Bridge, WebLogic's JdbcT3 Server, Symantec's dbAnywhere Server, Intersolv's SequeLink JDBC Driver, and Connect Software's FastForward for Sybase SQL Server.

Example 1(a) is the database table used in the tests, which contained 10,000 rows, each with six fields. Example 1(b) is the query statement I used.

For the static-query test, I used a Statement object and the parameter values were hardcoded in the query string, which was passed to the Statement.executeQuery() method. In the case of the dynamic query, I used a PreparedStatement object and acct_id and balance were passed as parameters using setXXX methods of the PreparedStatement class. For the static-transaction test, I used the SQL code in Example 2.

For the dynamic-transaction test, I again used Example 2, but the parameters balance and acct_id were passed to a PreparedStatement object using setXXX methods. The parameter values ranged between 100 to 1000. Example 2 was also used for the stored-procedure test. In the case of the dynamic stored procedure, I used a CallableStatement object.

Except for Type I, I tested two different driver vendors for each type. The ODBC driver I used with the JDBC/ODBC Bridge was the Intersolv ODBC 3.01 driver for Sybase.

The average connection time was lowest for Type IV drivers (~1.1 seconds) and highest for Type III drivers (~8.4 seconds). The Type II driver was second best, with an average connection time of ~2.3 seconds, followed by Type I (~6.5 seconds).

The retrieval test returned varying times for varying sizes of results. Figures 6 and 7 show the number of rows returned against the time (in seconds). The times measured are actual data-retrieval times, which include the time taken to scroll through the result set.

The transaction-execute test measured times for varying numbers of transactions. Figures 8 and 9 show the number of transactions against the time.

As Figures 10 and 11 illustrate, the stored-procedure test measured execution time for a stored procedure, while varying the number of times it was called. The static test used a Statement object to invoke the stored procedure. The dynamic test used a Callable statement object and passed in two parameters -- an int and Date type.

The concurrent-query test started a predefined number of threads -- with the connection object being shared by all threads -- and each of the threads created a statement object and then executed a query. Figure 12 shows the result of this test. There is scheduling overhead consisting of the time spent by the thread in a wait state, which increases in proportion to the number of threads created. However, using an indiscriminately large number of threads severely degrades performance.

The best drivers appear to be Type II. Both the Intersolv and Weblogic Type II implementations gave very good results. Type II drivers are a good choice for intranet-based applications, where there is administrative control on client configuration.

For Type III drivers, Weblogic T3 gave a middling performance, though this could be due to the fact that the T3 server is a pure Java implementation and interpreting Java makes it slower than a C/C++ implementation. There are advantages to this approach. Being Java based, the T3 server can use any JDBC driver to connect to the target database(s). In the case of Intersolv's Type III driver (SequeLink 1.0), the server has to be installed for the target database(s). The driver uses this server to talk to the target database(s). This improves performance since the server uses native libraries to talk to the target database(s).

Type III drivers work well for Internet/intranet-based applications with a large number of concurrent users. It is usually suitable for applications that require access to multiple databases, have stringent performance requirements, and need security features.

Type IV drivers had the largest footprint (700-800 KB) and large download times -- factors that must be considered for Internet-based applications. In terms of performance, they are faster than Type III. Type IV drivers appear to be the best choice for both intranet-based and Internet-based applications if the driver is available for the data store in use and the network connection offers good bandwidth. These drivers can be used by a pure Java Type III implementation (the server can use this to connect to the target database).

There are limitations in using JDBC, such as synchronous row retrieval, no backward scrolling in the result set, no support for high-speed data copying (bulk copy), and so on. The JDBC Standard is evolving, and the next release of the specification may fix these problems. Until then, the best way to get maximum performance from JDBC would be to replace SQL logic with stored procedures. This has the side effect of making the application less portable, but produces optimal performance.

Conclusion

There are a variety of possible architectures for web-based database applications. The one you choose should be determined by your application requirements. Each JDBC driver type is better suited to a certain architecture. Choosing the right driver is important because it has a direct impact on the application performance.

DDJ