Performance issues with jdbctemplate.query

[nadhimoolam]

Guyz,

We are facing extremely slow performance with jdbctemplate.query whereas the usual plain jdbc statements brings the records at once.


Blow is our code which tries execute simple select statement without any conditions.
-------------------------------------------
System.out.println(new Date()+"-Before creating JdbcTemplate instance");
JdbcTemplate jt = new JdbcTemplate(getDataSource());
System.out.println(new Date()+"-After creating JdbcTemplate instance and Before executing jt.query");
List MrpList = jt.query(MrpConstants.MRP_DATA_VW, new MrpMapper());
System.out.println(new Date()+"-After jt.query:"+MrpList.size());
OrganizationMaintenanceDTO[] retMrp = new OrganizationMaintenanceDTO[MrpList
.size()];
if (MrpList.size() > 0)
{
for (int i = 0; i < MrpList.size(); i++)
{
System.out.println(new Date()+"-After jt.query: looping records:"+i+1);
retMrp[i] = (OrganizationMaintenanceDTO) MrpList.get(i);
}
}
-------------------------------------------
20,000 records - 2 hrs

Wed Jul 10 11:09:29 EDT 2007-Before creating JdbcTemplate instance
Wed Jul 10 11:09:29 EDT 2007-After creating JdbcTemplate instance and Before executing jt.query
Wed Jul 10 13:12:20 EDT 2007-After jt.query:20551
------------------------------------
1400 records - 11 mins

Wed Jul 11 12:15:29 EDT 2007-Before creating JdbcTemplate instance
Wed Jul 11 12:15:29 EDT 2007-After creating JdbcTemplate instance and Before executing jt.query
Wed Jul 11 12:26:20 EDT 2007-After jt.query:1448
------------------------------------

Any help?

Thanks,
Nambi

[karldmoore]

There have been lots of posts like this, it's usually that the test code doesn't compare like for like. It would be useful if you could post both of the tests you are using. If you wrap this in [code] [ /code] tags, it's soooo much easier to read!

[nadhimoolam]

System.out.println(new Date()+"-Before creating JdbcTemplate instance");
JdbcTemplate jt = new JdbcTemplate(getDataSource());
System.out.println(new Date()+"-After creating JdbcTemplate instance and Before executing jt.query");
List MrpList = jt.query(MrpConstants.MRP_DATA_VW, new MrpMapper());
System.out.println(new Date()+"-After jt.query:"+MrpList.size());
OrganizationMaintenanceDTO[] retMrp = new OrganizationMaintenanceDTO[MrpList
.size()];
if (MrpList.size() > 0)
{
for (int i = 0; i < MrpList.size(); i++)
{
System.out.println(new Date()+"-After jt.query: looping records:"+i+1);
retMrp[i] = (OrganizationMaintenanceDTO) MrpList.get(i);
}
}

I am using DBCP connection pool and there are no issues in getting connection ..etc

[karldmoore]

Ok but what about the plain JDBC code?

[Chris Lee]

This thread may be relevant; the root cause was a (hidden) data type mismatch resulting in poor index selection.

[Marten Deinum]

Most of the time it is that what is being tested isn't the same.

Make sure that when testing your jdbc stuff that you include
- obtain connection
- create/fill prepared statement
- execute prepared statement
- resultset conversion into objects
- close connection/resultset

I've seen a lot of comparisons and they forget to transform the resultset into objects. That makes a major difference but it is like comparing apples and oranges.

[nadhimoolam]

The metrics are derived just before converting the resultset to object. Even to populate the List, the JdbcTemplate takes so much time.

[nadhimoolam]

Also, I am using plain select query without any bindings

select * from user order by user_id

hence I dont think there are issues with any indexing

[Marten Deinum]

The metrics are derived just before converting the resultset to object. Even to populate the List, the JdbcTemplate takes so much time.

Before converting? That means that you don't compare the same things. For me this states that

1. plain Jdbc you measure the time it takes for a query to return (a resultset)
2. JdbcTemplate measue time to take query + return + create objects from resultset

Also JdbcTemplate isn't doing anything special. It is just a convenience wrapper around your jdbc. Take a look at the code if you don't believe that.

However to tell something more about your metrics (and if they are correct or not) we need to see both of the testcases, including the RowMapper and constants you use.

[nadhimoolam]

Thanks!

The issue is with complex mapper.