• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Spring JdbcTemplate and ResultSet is too slow

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch, Rajukpo.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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..

 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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.
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic