Announcement Announcement Module
No announcement yet.
Setting the fetch size using Simplejdbctemplate Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Setting the fetch size using Simplejdbctemplate

    Hi Everyone,

    I am completely new to the Java web service and spring world. I am trying to modify a web service that this calling an Oracle database. The performance of the web service is very slow due to Oracle's default fetch size of 10. I have researched and tried some of the methods posted by other, but none of them seems to work. I need a very simple way of setting the fetch size while using Simplejdbctemplate. Here is a sample of my code:
    List listResults = getSimpleJdbcTemplate().queryForList(sql, searchMap);

    Any help will be greatly appreciated!

  • #2
    1. Don't use SimpleJdbcTemplate (at least as of Spring 3.0, simply use JdbcTemplate)
    2a. Simply call setFetchSize on the template to set the fetchsize (warning this sets it globally not only for this call)
    2b. You can probably (re)configure the fetchsize on the datasource implementation you are using, which would eliminate 2a.

    Edit: To configure the datasource add the 'defaultRowPrefetch' property to your jdbc properties, how to do this depends on your datasource.


    • #3
      Thank you for your help! I changed the logic to use JdbcTemplate and added the 'defaultRowPrefetch' property. It is working now. I truly appreciate the help.


      • #4
        Hi ..
        I tried both by using the setFetchsize and defaultRowPrefetch but my timings are still the same.
        Could there be any other reason or any other restrains on oracle side. I tried the query through sql developer and it returns results in less than half a second but at java it takes 20 secs for the same 600 records. Its a function in a package that its calling in oracle. would that make any difference? Im using jdbcTemplate.query(sql, rowMapper);

        Database is oracle 11g
        Last edited by idrismca; Nov 20th, 2013, 03:13 PM.


        • #5
          So I used prepared statement and fetched the query result separately, iterating through it and assigned the result set to java objects.. and realized that fetching of the query is the same half a second as firing at the oracle side.. but its the assignment to java objects that's taking 19+ seconds for the same query...