• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Spring JdbcTemplate and ResultSet is too slow

 
Rajukpo kumar
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch, Rajukpo.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34656
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  •  
    Rajukpo kumar
    Greenhorn
    Posts: 9
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic