A Comparison of the CData and Sun JDBC-ODBC Bridges

With the deprecation of the JDBC-ODBC bridge in JDK 8, there is a gap in support for ODBC connectivity in Java-based applications. The CData JDBC-ODBC Bridge is a seamless replacement for the Sun JDBC-ODBC Bridge. Simply drop the CData JDBC-ODBC Bridge in place and connect to ODBC data sources in exactly the same way. There is no need for additional applications or installations.

Preparation

This article will serve to compare the Sun JDBC-ODBC Bridge (available in JDK 7) to the CData JDBC-ODBC Bridge¹. In our comparison, we used the open source MySQL Connector/ODBC², largely to avoid any bias in using our own proprietary ODBC Driver for MySQL. In order to provide a benchmark, we installed MySQL Server 5.7 on a test machine and inserted a relatively large dataset into the database.

The test machine specifications are as follows:
Operating System: Windows 7 Ultimate, SP1
Processor: Intel® Core^TM i3-2120 CPU @ 3.30GHz
Installed Memory (RAM): 8.00 GB
System type: 64-bit Operating System

Since the bridges are being compared side-by-side, the performance of the machine itself is relatively unimportant; what matters is how the bridges compare relative to one another.

Comparison

To begin the comparison, we created a new database in MySQL and then created a table called "restaurant", described below:

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| _id             | int(10)       | NO   | PRI |         |       |
| grades          | varchar(2000) | YES  |     | NULL    |       |
| address_street  | varchar(255)  | YES  |     | NULL    |       |
| address_zipcode | int(11)       | YES  |     | NULL    |       |
| address_coord   | varchar(255)  | YES  |     | NULL    |       |
| building        | varchar(127)  | YES  |     | NULL    |       |
| name            | varchar(255)  | YES  |     | NULL    |       |
| restaurant_id   | bigint(20)    | NO   |     | NULL    |       |
| borough         | varchar(255)  | YES  |     | NULL    |       |
| cuisine         | varchar(255)  | YES  |     | NULL    |       |
| avg_score       | double        | YES  |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+

The dataset consists of 1,026,779 rows of data (3.45 GB) where each row contains information about a restaurant.

The main goal of this investigation was to compare the related performance of the bridges. We did this by running the same queries on the same DSN with each bridge. The queries are listed below:

1. SELECT * FROM restaurant LIMIT 250000
2. SELECT * FROM restaurant LIMIT 500000
3. SELECT * FROM restaurant LIMIT 1000000

Results

We connected to the MySQL database using the same DSN in each JDBC-ODBC Bridge from a simple Java application and executed the above queries repeatedly. The JDBC URLS used with each bridge are detailed below:

• CData JDBC-ODBC: jdbc:jdbcodbc:DSN='MySQL Source';
• Sun JDBC-ODBC: jdbc:odbc:MySQL Source

The results were read and stored in a new string variable for each row. The times found in the chart below are averages based on multiple queries, which should serve to level out any outliers due to spikes in network traffic, etc.

As can be seen in the results, the CData JDBC-ODBC Bridge provides a significant improvement over the Sun JDBC-ODBC Bridge, regularly retrieving and processing results over three times faster. It is noteworthy that the CData Bridge only improves its gains as the data sets increase in size.

The average runtime for each query is compared in the chart below:

Results