Discussions

Performance and scalability: Prepared Statement/Statement comparison

Bookmark and Share Get thread feed

  1. Prepared Statement/Statement comparison (7 messages)

    • Posted by: T J
    • Posted on: October 15 2002 13:47 EDT
    Does anyone have a really good benchmark comparison between using a PreparedStatement and not? I'm trying to figure out how much a PreparedStatement is worth. Thanks
    Reply to this

    Threaded Messages (7)

  2. Prepared Statement/Statement comparison[ Go to top ]

    Tracy,

    The things you need to think about are

    1) Will you be using this same Statement over and over. An example would be using it within a loop.
    2) You "can" cache PreparedStatements between connections (for instanbce as a member in a bean where each bean would pull a connection from the cache) but only if your Driver supports it. For instance the latest Oracle driver now supports caching PreparedStatements across connections from a DataSource pool. Not all drivers do.

    If you are not

    1) Using the same Statement across a loop or
    2) Going to cache across Connections using a Driver which supports it

    Then there is little to no gain from the PreparedStatement. If you are doing any of the above then the PreparedStatement will help out considerably.

    Dave Wolf
    Personified Technologies LLC
    Reply to this Reply to original

  3. Prepared Statement/Statement comparison[ Go to top ]

    Prepared Stametent and Statement also has effects on the db size.
    Statements are parsed at the db level every time but prepered statements are parsed once and bind variables are plugged and executed every time.
    Reply to this Reply to original

  4. Prepared Statement/Statement comparison[ Go to top ]

    With Prepared Statement you don't need to worry about qoutation in String parameters.
    Reply to this Reply to original

  5. Prepared Statements are more portable and easier to use[ Go to top ]

    Because you do not have to remember how to quote Strings (or even deal with it), nor any other type (like the dreaded Date). The driver handles creating the query for you. Seems to me there is no reason not to use PreparedStatement.
    Reply to this Reply to original

  6. Prepared Statements are more portable and easier to use[ Go to top ]

    My general policy here is:

    1) if i am going to use a plain SQL statement without any parameter and I'm gonna use it just once, I'll go for the Statement.
    2) if i am going to use a plain SQL statement without any parameter and I'm gonna use it in a loop, I'll go for the PreparedStatement.
    3) if i have parameters, there's no question about it, PreparedStatement.

    There is just a bit of a problem with the PreparedStatement: if you pass a null object into some of its setXXX methods, e.g., pStmt.setObject(1, myObj) and myObj is null, you'll get SQLException errors (Invalid column, or something like that). You must use the setNull(...) method to overcome that.

    Here's what we did to overcome that:

    public void setPrepStmtObject(PreparedStatement pStmt, int col, Object data, int type){

       if (data == null){
          pStmt.setNull(col, type);
       }
       else {
          pStmt.setObject(col, data);
       }

    }

    We use this everywhere we know that a particular object could be null.

    By the way, whatever you choose, be consistent.

    Regards,

    Marco
    Reply to this Reply to original

  7. Prepared Statements are more portable and easier to use[ Go to top ]

    Thanks guys, that's what I understood as well. Esp. in a pooling environemnt where the conns stay around, you know darned good and well probably every sql staement you have will be executed thousands of times before the server is bounced.
    Reply to this Reply to original

  8. Prepared Statement/Statement comparison[ Go to top ]

    Comparation for Oracle
    Reply to this Reply to original