Spring JdbcTemplate: The Phantom Performance Problem

Over the past few years there have been numerous posts on Spring Forum regarding the performance of JdbcTemplate. The majority of these are complaining about poor performance of JdbcTemplate in comparison to pure Jdbc. I thought this topic had been done to death already, but a friend of mine told me his developers were having performance problems with JdbcTemplate as recently as last week. They had switched back to Jdbc after some performance comparisons had proven JdbcTemplate was significantly slower.

So what makes JdbcTemplate so much slower than pure Jdbc? In a word…….. nothing. JdbcTemplate does a great job of removing the try/catch/finally/try/catch code we used to write with Jdbc, but under the covers it’s just doing the same old stuff we used to do. It’s getting hold of a connection, executing a statement, possibly mapping a result set to an object, converting any exceptions for us and releasing the connection. That’s pretty much it, it’s a thin wrapper around Jdbc that makes it usable in the real world.

So why do people report performance problems with JdbcTemplate? I took the opportunity to review the comparison that had reported such terrible performance compared to Jdbc. After a couple of minutes reviewing the comparison it was quite obvious it was fatally flawed, not only was it configured differently, it wasn’t testing the same thing. Looking at the comparisons that have been posted in the past on Spring Forum, they typically suffer from the same problems. The most common is they generally don’t perform a fair test. Lets look at the typical problems the comparisons face before seeing the comparison results once the problems were fixed.

1. Connection Pool vs Single Connection

One test uses a connection pool and the other will acquire a new connection every time a statement is executed. This can have differing effects on the test results depending on the initial pool size. If this is set to a high value then typically the connection pool tests will perform slower. Either way, use the same data source for both tests with the same settings.

2. Transactions

One test uses transactions and the other executes non-transactionally, or the transactions are applied at different levels. There’s no point wrapping each query execution in a transaction in one test and in the other test applying it to the service layer. This was actually one of the problems with the original performance comparison. The transactions were applied at the service layer in one test and the DAO in the other.

3. Jdbc RowMapper equivalent doesn’t do anything

This is the most common problem I’ve seen. Although the pure Jdbc version of the code executes a query, the result set is ignored as the results are not processed and mapped into the object representations. This obviously shows a massive improvement over JdbcTemplate as it’s not actually doing anything. If it isn’t processing the results, it’s going to be quicker!

4. Prepared Statements vs String-based queries

Prepared statements are potentially going to be more efficient than string-based queries (depending on platform). Initially the prepared statement is going to be more expensive to create, but if it’s reused it should produce improved performance as it should be available from the statement cache.

5. Batch Statements

One test uses batched prepared statements and the other executes a separate prepared statement.

6. Creating a new ApplicationContext for every test run

Although this is the least common problem, it was actually one of the problems in the aforementioned comparison. If one test uses Spring and the other doesn’t, either create the ApplicationContext outside of the test or at least ensure it’s construction isn’t included in the time taken to complete the test.

Let’s have a look at the performance comparison that was discussed at the beginning of the blog and also the real statistics it produced. The test consisted of creating, updating, deleting and retrieving a number of users. In this example the number of users was set to one thousand, and the test was run one hundred times to give an average time for each operation. For the sake of brevity I’ve only included the code here for retrieving and updating the users, the code for creating and deleting them however followed exactly the same pattern.

This is the pure Jdbc code that retrieves all the users and update the user’s password.

public List<User> getUsers() {
	return jdbcTemplate.query("SELECT * FROM user_details", new RowMapper() {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			String username = rs.getString("username");
			String password = rs.getString("password");
			return new User(username, password);
		}
	});
}

public void updateUsers(final List<User> users) throws Exception {
	this.jdbcTemplate.batchUpdate("UPDATE user_details SET password = ? WHERE username = ?", 
				new BatchPreparedStatementSetter() {
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			User user = users.get(i);
			ps.setString(1, PasswordObfuscator.obfuscate(user.getPassword()));
			ps.setString(2, user.getUsername());
		}

		public int getBatchSize() {
			return users.size();
		}
	});
}

And finally let’s have a look at those all important numbers.

Jdbc DAO
insert: 360ms, fetch: 7ms, update: 276ms, delete: 136ms

JdbcTemplate DAO
insert: 363ms, fetch: 5ms, update: 278ms, delete: 133ms

Transactional Jdbc DAO
insert: 963ms, fetch: 6ms, update: 276ms, delete: 131ms

Transactional JdbcTemplate DAO
insert: 948ms, fetch: 6ms, update: 274ms, delete: 131ms

As expected there’s really not that much in it. Once the issues were fixed in the original comparison they come out pretty much the same. JdbcTemplate doesn’t have the performance problems the original comparison reported and actually shows pretty much the same performance as pure Jdbc. So the moral of the story……. if you are doing a comparison make sure you are comparing the same things and most importantly whatever you tweak, make it a fair test.