icrosoft’s recent release of the SQL Server 2000 Driver for Java Database Connectivity (JDBC) reminded me of my first big Java project. I worked on a basic two-tiered system in which one component wrote file information to a database and another component displayed the file information to the user.
A major constraint of the project was that we needed to support Microsoft’s SQL Server DBMS. Our clients used SQL Server so we couldn’t very well debate whether it was better or worse than Oracle, MySQL, Cloudscape, or a plain text file. The customer was king, and if they had SQL Server, that is what we had to use.
The project started in 1997, when JDK 1.1 had just been released. Very few JDBC drivers were available for SQL Server then, and particularly unfortunate was the lack of Type 4 drivers. To understand why the Type 4 driver is preferable to the others available, examine the different types of driver Sun Microsystems lists:
- Type 1: Uses a bridging technology to access the database (e.g., Sun’s JDBC-ODBC Bridge driver)
- Type 2: Uses native API drivers; requires software on the client machine
- Type 3: Translates JDBC calls into a database-independent network protocol, which a server then translates into a database protocol
- Type 4: Uses the network protocols built into the database engine
Type 1 and 2 drivers require native code to be installed on the client’s machine, so they are suitable for a corporate network or an application server in a three-tier architecture. They are not suitable for machines connecting to the database via the Internet. Type 3 drivers do not require native drivers on the client’s machine, but they require additional security to work over the Internet. In general, Type 4 drivers are preferable because they use the network protocol of the database without needing any special native code on the client machine.
Conventional wisdom says that you should not use the Type 1 JDBC-ODBC Bridge for any commercial application. Why? Because not only do you have to set up an ODBC source on the client machine, the bridge is slower than a Type 4 driver would be.
Of course, how much the speed of the bridge matters depends on your application. If you have an application that is using the database continuously, you need to have as little overhead in your driver as possible. On the other hand, if your database access is not a bottleneck, it may not really matter. Isn’t it far more important that the database driver executes your requests correctly without leaking memory? For example, each time you call the
ResultSet.getTimestamp() method on the bridge, you leak 16 bytes of native memory.
The Performance Evaluation
I downloaded several Type 4 drivers to compare with the new Microsoft SQL Server 2000 Driver for JDBC. The Microsoft driver is basically an old-ish version of the DataDirect Type 4 driver, so the performance results between the two are quite similar. I tested the drivers by writing some very basic insert and select query code. I purposely avoided “advanced” JDBC functions because when I tried them at least one of the drivers would fail each time. Table 1 shows the test results.
|Editor’s Note: This article was published in June of 2002. The data in this table reflects conditions as they were at that time.|
|Driver||Type||Insert 1,000 Rows (Average Time in ms)||Select 100,000 Rows (Average Time in ms)||Total Time (ms)||Factor Faster Than JDBC-ODBC|
|Microsoft SQL Server 2000 Driver for JDBC||4||6,908||4,022||10,930||3.2|
|i-net SPRINTA 2000||4||6,595||2,636||9,231||3.8|
|JDBC-ODBC Bridge JDK 1.3.1||1||8,476||26,328||34,804||1|
|JDBC-ODBC Bridge JDK 1.4.0||1||8,664||24,317||32,981||1.06|
The important column to look at is Factor Faster Than JDBC-ODBC, which shows how many times faster each driver is than the Type 1 JDBC-ODBC Bridge.
Listing 1 shows the code I used to test the drivers. You can use this test code to compare any other drivers as well. I cannot emphasize enough how important it is to run your own performance tests in a real-world test environment. Depending on what you are doing, the JDBC/ODBC bridge may actually be better than a Type 4 driver. For example, if you measure how fast a driver inserts large amounts of data into a database via the
setBytes() method (e.g., 1MB blocks into an Image column), you’ll discover that some of the Type 4 drivers are almost 10 times slower than the JDBC/ODBC bridge (see Table 2).
|Driver||Time (ms)||Factor Slower Than JDBC-ODBC|
|Microsoft SQL Server 2000 Driver for JDBC||12,515||8.28|
|i-net SPRINTA 2000||3,915||2.59|
|JDBC-ODBC Bridge JDK 1.3.1||1,512||1.00|
|JDBC-ODBC Bridge JDK 1.4.0||1,362||0.90|
Testing Drivers for J2EE Compatibility
One of the ways in which you can test that the JDBC driver conforms to Sun’s J2EE specification is to test it against a JDBC API Test Suite. Although this test won’t demonstrate the driver’s performance in relation to other JDBC drivers, it does indicate whether you will be able to use the driver in J2EE environments.
The Microsoft Web site states that Microsoft is in the final stages of getting certification to make its JDBC driver J2EE-compatible, which it likely will because the driver is basically just a repackaged DataDirect driver. DataDirect not only built the “Microsoft” driver, it also provides all the customer support for it. In plain English, it is a DataDirect driver.
At first glance, this may seem like a foolish decision by DataDirect. How is it going to sell its drivers if customers can just download the free driver from Microsoft? But look more closely. The free Microsoft JDBC driver works only with SQL Server 2000, not the older versions. As new versions of the DataDirect drivers come out, customers will make the natural move from Microsoft to DataDirect in order to get the new functionality.
The Devil You Know
“Better the devil you know” is an English expression meaning trust the person or thing you are familiar with rather than risking the unknown (according to the Wordsworth Dictionary of Cliche). Every database driver has quirks, funny bugs that cause certain JDBC functionalities to fail. I have yet to see a driver that works completely for everything that I throw at it, but at least you know what your current driver’s quirks are.
If you’re currently using the JDBC-ODBC Bridge to connect to SQL Server 2000, I recommend you switch to Microsoft’s free Type 4 driver. If you are using some of the other drivers (i-net SPRINTA, Atinav AveConnect, etc.), I recommend that you make sure your applications will still run with the Microsoft driver before switching. Once you start using the Microsoft driver, you can easily switch to the DataDirect driver, which you might have to do if you need JDBC 3.0 functionality.
Herein lies the catch: by going for the Microsoft driver you are pretty much binding yourself to DataDirect drivers. That may not be a bad choice, howeverlook at the performances of the various drivers. Ask yourself if that is what your business wants to do before migrating to the “new” Microsoft driver.