Announcement Announcement Module
No announcement yet.
efficient usage of PreparedStatements Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • efficient usage of PreparedStatements

    Hi All,

    I want to get instructions for a proper and most efficient usage of PreparedStatements in Spring.

    I will explain the problem.

    Today we are using Spring API with support of PreparedStatements in 3 ways:
    - JdbcTemplate.query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch)
    - JdbcTemplate.update(String sql, Object[] args, int[] argTypes)
    - BatchSqlUpdate

    In JDBC a PreparedStatement is associated with a single db connection. The best way to use PreparedStatements is to define them once per connection. It will improve the performance, because application will reduce the number of calls to JDBC driver by reusing a previous PreparedStatement call.

    From the other hand, Oracle parses PreparedStatement and caches it in it's memory. This parsed code can be reused with the same statement from different connection, db will perform only a 'soft parse' in this case.

    In our application load tests we can see that 'soft parse' is performed in 30% per total number of executions with JdbcTemplate and 100% with BatchSQLUpdate.

    From a quick view on a Spring's source code it looks like PreparedStatements are closed after every execution and then recreated the next time the statement is going to be executed.
    Is this correct?
    Is there any way to avoid it?
    Should we use another implementation?
    Why do we get 100% 'soft parse' on BatchSQLUpdate and how to reduce it?


  • #2
    Can't help you with your specific questions about what Spring is doing but I do know that the latest Oracle jdbc drivers allow statement caching at the connection pool level.

    Have a look at the latest JDBC docs on OTN. I have read them and intend to implement statement caching (and connection pooling) via the jdbc drivers drivers next.

    If you find it useful and you use it, please report back so i can see how its done in practice.

    As a starter, I already asked how to Spring-configure the Oracle jdbc drivers for connection pooling (on the other Spring forum). let me know if you want that and i can email it to you. I'm guessing you need to add another configuration option for statement caching.




    • #3
      Hi Rakesh,

      I already use a Jakarta connection pool (DBCP) and it has such configuration parameter which is indicates if it's possible to pool PreparedStatement.

      If it's set to false I always get 100% 'soft parsing' on Oracle.

      I will be appreciate if you can email to me the replies and ideas you have.

      many thanks.