Tuning JDBC

I was trying to create a common method to execute querys when I start wondering which type of Statement to used, PreparedStatement, CallableStatement or Statement. So I start doing some research about it, here are my findings :

Statement is not precompiled, prepared statements are precompiled, hence prepared statement will be faster than statement”. My next question would be “What precompile means?”. When you execute a SQL query, database server will prepare an execution plan before executing the actual query, this execution plan will be cached at database server for further execution. The extra benefit you get by using CallableStatement is that allow you to execute database stored procedures.

From what I found my guess was that the PreparedStatement will be faster, so I run a test to see which of the three performs better. Here are the response times when retrieving 4000 records.

	#Samples	Avg(Millis)
Statement	25	20046
PreparedStatement	25	20261
CallableStatement	25	20174

As you can see from the previous table the difference between the three was not significant, I was expecting that the Prepared Statement will outperforms the others. But what I noticed was that the response times of the three were very poorly for retrieving 4000 records, so I keep doing research and find out that there is a setting you can change to increase performance when retrieving data, the fetch size.

The fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a ResultSet with the next() method (Usually default is 10). If you set the query fetch size to 100, when you retrieve the first row, the JDBC driver retrieves at once the first 100 rows (or all of them if fewer than 100 rows satisfy the query). When you retrieve the second row, the JDBC driver merely returns the row from local memory it doesn't have to retrieve that row from the database. This feature improves performance by reducing the number of calls (which are frequently network transmissions) to the database. To set the query fetch size, use the setFetchSize() method on the Statement (or PreparedStatement or CallableStatement) before execution.

The optimal fetch size is not always obvious. Usually, a fetch size of one half or one quarter of the total expected result size is optimal. Here are the new times for the same amount of records(4000) using a fetch size of 100, 500 and 1000.

	#Samples	Avg(Millis) Fetchsize 100	Avg(Millis) Fetchsize 500	Avg(Millis) Fetchsize 1000
Statement	25	2454	1008	874
PreparedStatement	25	2604	1018	919
CallableStatement	25	2460	988	959

As you can see from the table the response times decreased considerably by increasing the fetch size but keep in mind that If you set the fetch size much larger than the number of rows retrieved, it's likely that you'll get a performance decrease, not an increase. I did another test with 70 rows and the response times increase when I set up the fetch size to 1000.