aspose file tools*
The moose likes JDBC 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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Spring JdbcTemplate and ResultSet is too slow" Watch "Spring JdbcTemplate and ResultSet is too slow" New topic
Author

Spring JdbcTemplate and ResultSet is too slow

Rajukpo kumar
Greenhorn

Joined: Dec 31, 2010
Posts: 9
Hi..

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
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Welcome to the Ranch, Rajukpo.


OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

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
Greenhorn

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
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

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


  • [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Rajukpo kumar
    Greenhorn

    Joined: Dec 31, 2010
    Posts: 9
    Resolved!!!

    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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Spring JdbcTemplate and ResultSet is too slow