Results 1 to 7 of 7
  1. #1
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

    Question Proper use of PreparedStatment

    Hello,

    I find myself working with databases often in my code... I used to use Statement when I was a real noob, but after running into performance issue one after another, I discovered PreparedStatement and life has been great since.

    Now my question is, how to properly/best-practice a PreparedStatement? I have done lots of research, and most examples of a PreparedStatement show it used as such:

    Java Code:
     try {
            updateSales = con.prepareStatement(updateString);
            updateTotal = con.prepareStatement(updateStatement);
    
            for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
                updateSales.setInt(1, e.getValue().intValue());
                updateSales.setString(2, e.getKey());
                updateSales.executeUpdate();
                updateTotal.setInt(1, e.getValue().intValue());
                updateTotal.setString(2, e.getKey());
                updateTotal.executeUpdate();
            }
    Now, if this code were in a method, and I call this method say, 3 times in my program execution. Will this create the preparedstatement, execute the query, then destroy the preparedstatement when the method is complete?

    so if I were to do something like:

    Java Code:
    public static void main(String[] args) {
          TestQuery query = new TestQuery();
          for (int i = 0; i < 3; i++) { query.runQuery(); }
    }
    would this tell the database to compile the statement, run it, destroy the statement, recompile the statement, run it, destory, etc... essentially negating the benefits of PreparedStaetment?

    what about if I were to do this:

    Java Code:
    public static void main(String[] args) {
          for (int i = 0; i < 3; i++) {
             TestQuery query = new TestQuery();
             query.runQuery(); 
          }
    }
    My thoughts are that when you initialize a class containing a method that has preparedstatements, it will compile that statement on the database? Or does it only compile the statement once that method that prepares the statements have been called? -- that would mean calling the method repeatedly would recompile the statement repeatedly... causing a lot of overhead...

    Thanks for any advice!

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default Re: Proper use of PreparedStatment

    Since each database does things slightly differently take the next bit as a generalisation based on Oracle.

    The database handles SQL as follows:
    1. 'Soft' parse the SQL.
    2. 'Hard' parse the SQL (this is the compilation side of things).
    3. Develop execution plan.
    4. Run it.

    When you create a PreparedStatement that has never before been executed on the database, ie does not exist in the pool of "recent" SQL statements, it will do 1 and 2. When you execute it it will do 3 and 4.

    If the SQL already exists (and this is a simple search on the SQL text) then the database will do step 1, which will find the SQL in the pool. And on execute() it will do step 4.

    So reusing a PreparedStatement is advisable within reason as you can save on step 1. However don't go out of your way as it's not a massive saving.

    In your case above, if that was your real code, then it should use a single PreparedStatement.
    But you would see a bigger gain if the first piece of code batched your calls. Now, if you needed both calls in order then that's not actually possible, but that does not look to be the case. Indeed, I would say there's a problem with the table structure...but I'm guessing it's just a cobbled together example.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

    Default Re: Proper use of PreparedStatment

    thanks Tolls!

    The first code snippet is actually from Oracle's section on using prepared statements lol!

    So when/how does the database decide to destroy/forget the prepared statement? If I have a method that creates a prepared statement, then loops a bunch and does stuff with it, then completes and returns to the calling method, which does some stuff then calls the same method again -- will the database have to re-create/compile again even though its the same statement, or will it still have that statement around and reuse it even though the original method that created the prepared statement ended? What would happen if I called a different method that prepared a statement that was identical to the other -- the databse would have to compile it since its a different method creating the statement? That may be database specific... not sure... Should I instead create a method that prepares all statements and returns them in some object so that it can be passed around and used without having to re-create the statements a bunch of times? That seems like it would also create a lot of overhead by passing-by-value the object repeatedly...

    Also, you mentioned that from the example code, you woudl have gone with a single prepared statement... is it advantageous to reuse a single prepared statement, as in create a statement, use it, then destroy/set to null and then re-initialize as a new prepared statement? Or does this not matter..

    so like:

    Java Code:
    String sql1 = "SOME EXAMPLE SQL QUERY";
    String sql2 = "ANOTHER SOME EXAMPLE SQL QUERY";
    PreparedStatement ps1 = conn.getConnection().prepareStatement(sql1);
    
    while (something) {
    	ps1.setString(1, someData);
    	ps1.execute();
    }
    
    ps1 = conn.getConnection().prepareStatement(sql2);
    
    while (something_else) {
    	ps1.setString(1, someMoreData);
    	ps1.execute();
    }
    instead of like:

    Java Code:
    String sql1 = "SOME EXAMPLE SQL QUERY";
    String sql2 = "ANOTHER SOME EXAMPLE SQL QUERY";
    PreparedStatement ps1 = conn.getConnection().prepareStatement(sql1);
    PreparedStatement ps2 = conn.getConnection().prepareStatement(sql2);
    
    while (something) {
    	ps1.setString(1, someData);
    	ps1.execute();
    }
    
    while (something_else) {
    	ps2.setString(1, someMoreData);
    	ps2.execute();
    }
    (again, just academic code example)...

    Also, is there any good reading on optimizing interaction with databases via Java (JDBC) ? As mentioned before, I find myself doing a lot of work with databases in my software and am interested in the Best Practices for the industry as well as any optimizations I can eek out any performance from.

    Thanks!

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default Re: Proper use of PreparedStatment

    Quote Originally Posted by SnakeDoc View Post
    thanks Tolls!

    The first code snippet is actually from Oracle's section on using prepared statements lol!

    So when/how does the database decide to destroy/forget the prepared statement? If I have a method that creates a prepared statement, then loops a bunch and does stuff with it, then completes and returns to the calling method, which does some stuff then calls the same method again -- will the database have to re-create/compile again even though its the same statement, or will it still have that statement around and reuse it even though the original method that created the prepared statement ended?
    Oracle has a pool that it sticks the statements in along with the execution plans.
    When the pool is full then the oldest (in terms of use, not in terms of adding to the pool) SQL is ejected.
    In general, in the sort of things I write (eg airline booking stuff at the moment), that should only be reporting SQL. The stuff the main app uses should be commonly used enough (and the pool big enough) not to be ejected.

    Anyway, it's this pool it will look for the statement from. Note, it does this for all SQL. Not just prepared statements.

    Quote Originally Posted by SnakeDoc View Post
    What would happen if I called a different method that prepared a statement that was identical to the other -- the databse would have to compile it since its a different method creating the statement? That may be database specific... not sure... Should I instead create a method that prepares all statements and returns them in some object so that it can be passed around and used without having to re-create the statements a bunch of times? That seems like it would also create a lot of overhead by passing-by-value the object repeatedly...
    If the SQL is the same then it will pull it from the pool.
    In a pooled connection app you would create the prepared statement when you used it. After all, it's associated with the connection.
    If it's going to be used a lot in a single chunk of processing then try and create it as soon as possible. You don't really want to be passing around statement objects, IMO. That can lead to a loss of responsibility as to who should be closing it off.

    Quote Originally Posted by SnakeDoc View Post
    Also, you mentioned that from the example code, you woudl have gone with a single prepared statement... is it advantageous to reuse a single prepared statement, as in create a statement, use it, then destroy/set to null and then re-initialize as a new prepared statement? Or does this not matter..
    I was simply guessing at the table contents. That code is inserting the same value into two tables. Strikes me (and it's just an example, so overanalysis is a bit silly on my part!) as it should in one of those tables and referred to by the other.
    But looking at it again (and your latest code) unless there are triggers on the tables ("magic") your two loops make more sense...because then you can batch. Batching is good because each of those execute calls is traffic across the network to the db and back. A single executeBatch for each of those PreparedStatements will cut that down to one for each statement covering all the Entries.

    I would probably stick to two PreparedStatements...chiefly because I would probably have written this in two methods. But that's just me.
    Looking a bit like this:
    Java Code:
    String sql1 = "SOME EXAMPLE SQL QUERY";
    String sql2 = "ANOTHER SOME EXAMPLE SQL QUERY";
    PreparedStatement ps1 = conn.getConnection().prepareStatement(sql1);
    PreparedStatement ps2 = conn.getConnection().prepareStatement(sql2);
    
    while (something) {
    	ps1.setString(1, someData);
    	ps1.addBatch();
    }
    ps1.executeBatch();
    
    while (something_else) {
    	ps2.setString(1, someMoreData);
    	ps2.addBatch();
    }
    ps2.executeBatch()

    Quote Originally Posted by SnakeDoc View Post
    Also, is there any good reading on optimizing interaction with databases via Java (JDBC) ? As mentioned before, I find myself doing a lot of work with databases in my software and am interested in the Best Practices for the industry as well as any optimizations I can eek out any performance from.

    Thanks!
    Most of the performance stuff (note, beware of premature optimisation) is on the database side.
    Know your database.
    Ensure you have indexes in the correct places.
    Bind variables (ie use PreparedStatements) where possible as, on those database that hold execution plans, you skip the whole hard parse step. And it's more secure.
    When binding always use the correct types. Dates should be Dates, not Strings. That's a common one.
    Close resources when you're finished with them (statements, resultsets, connections). That does sometimes depend on the type of system (I'm thinking web apps here and pooled connections).

    That really covers it...the main thing there, though, is actually understand the database that sits underneath all this. The other option is to just go the Hibernate route, of course...:)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

    Default Re: Proper use of PreparedStatment

    Oracle has a pool that it sticks the statements in along with the execution plans.
    When the pool is full then the oldest (in terms of use, not in terms of adding to the pool) SQL is ejected.
    In general, in the sort of things I write (eg airline booking stuff at the moment), that should only be reporting SQL. The stuff the main app uses should be commonly used enough (and the pool big enough) not to be ejected.

    Anyway, it's this pool it will look for the statement from. Note, it does this for all SQL. Not just prepared statements.
    does this mean that my preparedstatement can/will "live" in the database statement pool even after my method and/or program terminates? Meaning if I had an identical query preparedstatement in two different classes for some reason, the statement would be reused and skip the compilation step so long as the query lived in the pool still? Is there a way to tell the DB to destroy/forget that statement (if needed for some reason)... or is that part of the database "magic" ... I'm really not a DBA nor have a desire to be... lol... so getting in an super optimizing the database isn't something i jump at the chance to do... *sigh*...

    i know preparedstatement is much better than just statement for more than performance reasons, as it auto-escapes the input for you... (nice!).

    I was simply guessing at the table contents. That code is inserting the same value into two tables. Strikes me (and it's just an example, so overanalysis is a bit silly on my part!) as it should in one of those tables and referred to by the other.
    But looking at it again (and your latest code) unless there are triggers on the tables ("magic") your two loops make more sense...because then you can batch. Batching is good because each of those execute calls is traffic across the network to the db and back. A single executeBatch for each of those PreparedStatements will cut that down to one for each statement covering all the Entries.
    Batching has worried me in the past due to what happens if one statement fails within the batch... roll back or commit anyways...and you dont have the ability to handle the exception (if thrown) right away... right?

    It seems that always batching even if its only two statements in the batch would be adventageous due to the significant reduction of network overhead... if one can handle failed statement or be notified of a failed statement...

    When binding always use the correct types. Dates should be Dates, not Strings. That's a common one.
    Is this purely for escaping/data integrity purposes, or does this impact performance?


    Tolls, thanks a lot so far! Your knowledge is much appreciated!

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default Re: Proper use of PreparedStatment

    You need to think of the database as separate from your app (embedded databases aside).
    It's an app in its own right.
    So yes, it maintains the execution plan, so long as it hasn't had to make space.
    Basically it receives the SQL statement and compiles it and plonks it in its pool ("I'll save that in case I need it later"), and then executes it.
    You can clear the pool, but that's usually quite drastic. You're essentially lobotomising the db.

    Batching is another db dependent thing. The implementation can vary. The "normal" thing is to halt on an error and inform you which bit of the batch the error occured on, some will keep processing and return a list of failed batches. You then have the choice of commit or rollback. This is one where you need to know the driver, as that's where the logic tends to sit.

    And finally, using the correct datatypes in a PreparedStatement means the database doesn't have to do any conversions itself, which can mean an index will not get used. As an example.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

    Default Re: Proper use of PreparedStatment

    Hello Tolls,

    amazing and great info. Thank you very much for your knowledge, I think I have learned a bit more about working with databases and java now. Thanks!

Similar Threads

  1. Replies: 7
    Last Post: 10-09-2012, 06:02 PM
  2. Proper if Statements: Help
    By socboy6579 in forum New To Java
    Replies: 3
    Last Post: 12-10-2010, 01:48 PM
  3. while and proper conditions for if
    By Saletra in forum New To Java
    Replies: 11
    Last Post: 08-25-2010, 10:37 AM
  4. How to print PreparedStatment object in console
    By sasi.tati in forum AWT / Swing
    Replies: 1
    Last Post: 08-21-2010, 06:16 AM
  5. proper use of IllegalArgumentException
    By vendetta in forum New To Java
    Replies: 1
    Last Post: 01-16-2010, 07:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •