Java JDBC Performance vs. iBatis

While working on a recent project, I worked with a database that contained 23 billion rows in one table, and several hundred million in another table. Performance retrieving information was critical for success. I have used Spring and iBatis for several years now and only had a couple of weeks to develop a prototype, so implemented the database access layer using iBatis and spring. It is quick to implement and easy to maintain. We had several use cases we wanted to test. Some use cases returned as few as one row, some returned as many as 20 million. Unfortunately, it was taking too much time to retrieve the information for most of the use cases. One downside of using iBatis, was that I couldn't determine if the time was spent in the database query, returning the results to Java, or in creating the java objects themselves. I decided to modify the code to use straight JDBC calls so that way I could time the retrieval separately from converting to Java Objects.

I did not expect to see much performance improvement converting to straight JDBC. However, just converting to straight JDBC was a substantial performance improvement over using iBatis(30-40% faster). Unfortunately this still wasn't enough. This was a web application being deployed to WebLogic. After some research a co-worker found that the default number of rows retrieved by the WebLogic data source was 10. Queries whose results were less than 10, performed very well, but most queries returned thousands or millions of rows, so performance was very poor. We initial bumped that value to 1000, and saw another 40+% performance improvement. This in turn prompted some investigation of JDBC, and I found that the default value for JDBC against an oracle database was 10. Inside the code I increased the fetch rows value to 1000, and saw another 30% performance improvement. I concluded that more was better on the fetch rows setting, so I increased it to 50,000 and found that I now had a new problem: running out of memory. After some additional modifications to the memory available to the web application, and reducing the fetch count to 5000, I was able to get a reliable result. Essentially, a higher fetch count, meant less database connections to retrieve the data.

Using iBatis is a great way to get a lot done in a short amount of time, and for smaller databases I still prefer to use it. In addition to being easy to implement, it is also easy to maintain. However, if you have a large database, then using Straight JDBC might be a good solution. Don't forget to take fetch size into account both in the application server and in the Java JDBC code when dealing with large databases. The larger the fetch size, the fewer database connections that will be used, but the more memory that will be required. I did not investigate setting the fetch size in iBatis, so do not know if that is possible.

In the Java JDBC code setting the fetch size is done as follows:

            String query = "SELECT * FROM my_table";
            Connection conn = _dataSource.getConnection();            
            PreparedStatement st = conn.prepareStatement(query);
            st.setFetchSize(5000);
            ResultSet rs = st.executeQuery();

Some other Java improvements that helped with performance, was reducing the number of java objects being created and destroyed. This includes making any strings that are reused static member variables so they are only created once and reducing the number of java objects that are being created. If you are outputting to a file or or a message, perform those operations inline to reduce creating intermediate objects.