Announcement Announcement Module
Collapse
No announcement yet.
Prepared Statements in a Loop Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Prepared Statements in a Loop

    Hi,

    I know this is a bit of a newbie question but I could not find any clues on the net or in the reference guide. I want to set up prepared statements to do database queries but have a question on how/where in the code to do it.

    I'll use the example from the reference guide to frame the question. Say I have an app that loops over 1000 user IDs.

    For my DB query, I have a custom mapping query like what is shown in the reference guide:

    private class CustomerMappingQuery extends MappingSqlQuery {
    public CustomerMappingQuery(DataSource ds) {
    super(ds, "SELECT id, name FROM customer WHERE id = ?");
    super.declareParameter(new SqlParameter("id", Types.INTEGER));
    compile();
    }
    public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
    Customer cust = new Customer();
    cust.setId((Integer) rs.getObject("id"));
    cust.setName(rs.getString("name"));
    return cust;
    }
    }

    Here's the question. In my loop over each user, I do something like call this getCustomer routine for every user id:

    public Customer getCustomer(Integer id) {
    CustomerMappingQuery custQry = new CustomerMappingQuery(dataSource);
    Object[] parms = new Object[1];
    parms[0] = id;
    List customers = custQry.execute(parms);
    if (customers.size() > 0)
    return (Customer) customers.get(0);
    else
    return null;
    }

    So each of these 1000 times, I am instatiating a new CustomerMappingQuery. On the surface, it looks like this will define and compile a new prepared statement every time I new the query.

    Is that the case? Or does Spring realize that I am trying to define and compile a statement that I have already defined last time in my loop.

    I'm wondering if I get any real benefit or if I have to somehow define the statment at the beginning, then just call with parameters each time in the loop. Would I have to create all of the custom mapping queries in the custructor of my loop class??

    Thanks!
    Steve

  • #2
    My (not very deep) understanding is that the "cache" of prepared statements are actually on the Connection object, and as you will be using the same connection object it should only compile once.

    But by no means take this as a definitive answer

    Comment


    • #3
      From my prior Oracle/JDBC experience (but not Spring/Hibernate until recently).

      The best way to use prepared statements is to define them once and only once. Each call to the prepared statement with params will then corectly use the bind variables (on Oracle).

      If inside your loop you are creating a new prepared statement object, I'm pretty sure that at the DB level, you'll be generating unneccessary work. (ie ignoring bind variables - reparsing the whole statement instead of just re-execute with the new params, on Oracle at least, many performance problems stem from people forcing the DB to re-parse their statements).

      How this translates through Spring/Hibernate I'm not exactly sure, but just looking at the code, I'd prefer to create the query only once and then just setParams and execute. Another thing to consider is the fact that you're creating many short-lived objects (custQry), these will be fairly large and will rapidly fill up the part of the heap reserved for short-lived objects (Eden?) and be pushed into the long-lived area of the VM. This will leave the VM short of space for the real short-lived objects (parms) and you'll have to wait for GC to collect all the old custQry objects

      You may also get away with defining the query statically in a static initializer and then just calling it when appropriate. This depends on the nature of your app and how threaded etc it is, but I've seen massive performance improvements from careful use of this.

      But with all things YMMV, caveat emptor etc

      Kev

      Comment


      • #4
        Originally posted by foamdino
        If inside your loop you are creating a new prepared statement object, I'm pretty sure that at the DB level, you'll be generating unneccessary work. (ie ignoring bind variables - reparsing the whole statement instead of just re-execute with the new params, on Oracle at least, many performance problems stem from people forcing the DB to re-parse their statements).
        AFAIK if you create the same PreparedStatement again it can be handled by the database cache. For Oracle I know that if the statement is already in the cache, only the parameter binding is necessary.

        Of course if one is able to reuse the same statement again, this should be preferred.

        Regards,
        Andreas

        Comment

        Working...
        X