File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Spring JdbcTemplate and ResultSet is too slow Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Spring JdbcTemplate and ResultSet is too slow" Watch "Spring JdbcTemplate and ResultSet is too slow" New topic

Spring JdbcTemplate and ResultSet is too slow

Rajukpo kumar

Joined: Dec 31, 2010
Posts: 9

I am using spring jdbctemplate to fetch the records from oracle database.

But it is taking too long time to execute a simple select query.
The query which is having 400 records is taking around 12 to 15 seconds. I am using json to retrieve that data.

But if i execute same query in toad, response time is less than 1 second.
I am using java jdbcTemplate.query(sql, params, RowMapper object) and each record will have only 4 values.

I tried in following ways:

* using simpleJdbcTemplate object
* plain statement object
* changing driver class from BasicDataSource to c3po in bean configuration.

But i cant able to reduce the response time to 2 seconds less atleast.

Please help which will be greatly appreciated...

Thank you.
Jan Cumps

Joined: Dec 20, 2006
Posts: 2562

Welcome to the Ranch, Rajukpo.

OCUP UML fundamental and ITIL foundation
Jan Cumps

Joined: Dec 20, 2006
Posts: 2562

Hi. Toad does not return all 400 records. It executes the query and returns just enough records to fill one page of the display grid .
Have you timed how long it takes for your code to just do the query, without retrieving the data?

Rajukpo kumar

Joined: Dec 31, 2010
Posts: 9
Thanks jan for quick reply..

I have timed the java code processing time.. but it is very fast..
Only at jdbctemplate query method it is taking more time to execute and populate whole data...

Please provide me a solution which would be great help for me..

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32818

Jan is suggesting the problem is occuring in either network transfer to resultset processing. Saying it happens at "JdbcTemplate" isn't enough info as the template covers the whole operation.

To narrow things down:
  • Have you run an Oracle explain to see the execution plan of the query? It is possible there is a non0optimized join that isn't showing up in TOAD because it only gets a small number of rows.
  • Are you using all the fields you return from the database? 12 seconds for 400 rows implies some very large rows if the query is tuned well.
  • If you don't do anything with the result in Java, is it faster? If so you probably have a Java logic bottleneck

  • [OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
    Rajukpo kumar

    Joined: Dec 31, 2010
    Posts: 9

    First i have used the connection pooling so that it reduces the time to 3 seconds less. But still it is slow.

    Then the main reason behind time taking is: the java application and database are residing different servers. So i placed application and database in same server either in local or remote.

    Now the time of executing the query is less than 0.4 sec.

    Thanks all for your replies.
    It is sorta covered in the JavaRanch Style Guide.
    subject: Spring JdbcTemplate and ResultSet is too slow
    It's not a secret anymore!