karldmoore development

A development oriented blog, containing anything I've been working on, reading or thinking about.

  • Subscribe

  • Twitter Updates

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

  • Disclaimer

    The opinions expressed here are my own and are not necessarily shared by my employer, any other organization, or any other individual. Any trademarked names or labels used in this blog remain the property of their respective trademark owners. No guarantees are made regarding the accuracy or usefulness of content on this blog, though every effort is made to be accurate.
  • Meta

Spring JdbcTemplate: The Phantom Performance Problem

Posted by karldmoore on August 19, 2008

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
  2. 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.

  3. Transactions
  4. 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.

  5. Jdbc RowMapper equivalent doesn’t do anything
  6. 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!

  7. Prepared Statements vs String-based queries
  8. 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.

  9. Batch Statements
  10. One test uses batched prepared statements and the other executes a separate prepared statement.

  11. Creating a new ApplicationContext for every test run
  12. 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.

About these ads

7 Responses to “Spring JdbcTemplate: The Phantom Performance Problem”

  1. Chris Lee said

    Well put!

  2. Marten said

    Great post summing up the most common mistakes when creating a comparison test between jdbc and jdbctemplate.Worthy for a FAQ?

  3. Pramatr said

    I would say so, it’s been asked a fair number of times and as I said in the blog people are STILL reporting problems.

  4. Techdudes.blogspot said

    so it means… we should better use Spring JdbcTemplate..right? as it does all things automatically…

    but i got error sometimes…when ay app run over night..
    Error: ‘org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Error preloading the connection pool’

    can you please tell the reason for it? i try to find out and mostly they said to tweak properties in xml context for DataSource bean.

    I m using BasicDataSource…

    reply on this please.

    btw, can we have a link exchange?
    my blog is http://techdudes.blogspot.com

    if you like it :)

    reply at techdudes[dot]blog[at]gmail[dot]com

    Parth.

  5. Ramdas said

    I have this complaint about jdbcTemplate. here is my code snippet
    public void insertReqKeyInCntrlTable(String tnId, String tnName_, String _moduleId, String reqKey_) {
    // TODO Auto-generated method stub
    Object args[] = {tnId,tnName_,_moduleId,reqKey_};
    String sp_insertReqKey = “{call sp_insertReqKey(?,?,?,?)}”;
    jdbcTemplateAODB.update(sp_insertReqKey, args);

    if any arg is null it throws exception. i thought it will check for the argument and accordingly use setNull ot setString :( but it didnt

  6. Pramatr said

    If you want support then have a look at Spring Forum.

  7. […] access using Spring JdbcTemplate Preventing the n + 1 select problem when using […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: