re: jdbctemplate.query appears to be very slow

[jblack1395]

Hello,
I am in the process of moving my data access layer over to the Spring framework, instead of just making the jdbc calls myself.

I am using JDK1.5 and Mysql5.0 for my development and testing.

It appears that my program now spends about 91% of the time in JdbcTemplate.query and that seems to be an inordinate amount of time.

Should I take a large performance penalty by moving my program to the Spring Framework? Time is very important to my app, so if it will be a lot slower then I may have to go back to a pre-Spring version.

I am also using JProfiler to profiler my unit tests.

Thank you.

[irbouho]

jblack1395,

AFAIK, Spring FrameWork JDBC abstraction layer performance overhead is really minimal.

Could you provide an example where you encountered such a decrease in performance.

[julien]

Hello,
I am in the process of moving my data access layer over to the Spring framework, instead of just making the jdbc calls myself.

I am using JDK1.5 and Mysql5.0 for my development and testing.

It appears that my program now spends about 91% of the time in JdbcTemplate.query and that seems to be an inordinate amount of time.

Should I take a large performance penalty by moving my program to the Spring Framework? Time is very important to my app, so if it will be a lot slower then I may have to go back to a pre-Spring version.

I am also using JProfiler to profiler my unit tests.

Thank you.

What's the performance without using spring ?

What's your transaction policy ?

Remember that data access is often (and by far) the costliest operation of a request... If you have nothing special around data access, it's logical that it takes 91% of your execution time.

[jblack1395]

It is taking about 200ms to do simple queries, and it should be at least 4x faster than that.

if (template == null) {
template = new JdbcTemplate(getDataSource());
}
template.query(querystr, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
item.setDirectorID(rs.getString(1));
item.setTechID(rs.getString(2));
}
});

public DriverManagerDataSource getDataSource() {
switch (epicDBType) {
case LMS:
//System.out.println("database is coms");
dataSource.setUrl("jdbc:mysql://" + auth.getHostname(epicDBType) + "/"
+ "coms");
break;
case NAMS:
//System.out.println("database is nams");
dataSource.setUrl("jdbc:mysql://" + auth.getHostname(epicDBType) + "/"
+ "nams");
break;
}
return dataSource;
}


I am not doing anything special, but on every test I am instantiating a new template, but still, it shouldn't take this long.

I have just started to profile my program with the new changes, so I will know more in a day or so, as I continue to do more testing.

[Colin Sampaleanu]

Standalone tests will take longer, since on the initial jdbcTemplate creation the database type and error information will be queried. But this information is cached, so within one app with multiple jdbcTemplate invocations (whether on the same one or new instances), you will only take this hit once.

Colin

[trisberg]

You could set up a connection pool using Apache DBCP. The BasicDataSource is easy to use. If you create the pool upfront, there should be less time wasted configuring the data source and connecting to the database during the tests.

[jblack1395]

Thank you for your response.

I am toying with the idea of using connection pooling, but that will also make my testing harder to determine, as the pools will need to be created, since each unit test has to build up the environment from scratch.

It just struck me as odd that it would take 200ms for a query. I will just unit junitperf next to see if I can see any improvement by running the tests many times.

[Martin Kersten]

since each unit test has to build up the environment from scratch.

Watch your thoughts carefully. Unit tests should be running in high isolation and using a lightweight configuration. It is best to use several contexts (I have three over here, extending each other). So you should add pooling to your unit tests unless you want to specially test those functionality.

Acceptence tests may be using connection pooling, but again by definition a pooled connection is as good as a new one. So I would drop pooling also for the acceptence tests except one or two tests.

And you never should use your normal unit tests for testing performance.

[trisberg]

I did a little testing to see where the time is spent. It appears that most of the time is spent creating the first JdbcTemplate - this is where the error code translation and database metadata lookups take place. After that, the next JdbcTemplate for the same DataSource is instantaneous. Here is the printout from the timings using Rod's StopWatch (part of Spring - org.springframework.util.StopWatch). The JDBC driver also takes a little bit of time to load.

StopWatch 'JDBC Tests': running time (seconds) = 2.103
-----------------------------------------
ms     %     Task name
-----------------------------------------
00631  030%  Pre-Load JDBC Driver
00310  015%  Spring DataSource
01122  053%  Spring JdbcTemplate
00000  000%  Spring JdbcTemplate2
00030  001%  Spring Connection/Query
00010  000%  JDBC Connection/Query

Bottom line is that the query overhed is low (10-20 ms) and almost all overhead is in creating the first JdbcTemplate.

[Rod Johnson]

A JdbcTemplate instance is threadsafe. As Thomas's profiling shows, creating a JdbcTemplate has a performance cost because it needs to obtain the database metadata to support automated exception translation based on the database's error codes.

There's also a high cost in obtaining a connection from using DriverManager. Hence I always use Commons DBCP in integration tests. Note that in unit tests I try not to connect to the database at all: use mock objects wherever possible.

I can confirm that the overhead of using Spring JDBC correctly for query or update is insignificant. I've recently done a lot of extremely demanding work with it for two clients--querying for huge result sets and doing huge batch updates, and done extensive profiling of that application.

Rgds
Rod