PreparedStatements and performance

Question

So I keep hearing that PreparedStatements are good for performance.

We have a Java application in which we use the regular 'Statement' more than we use the 'PreparedStatement'. While trying to move towards using more PreparedStatements, I am trying to get a more thorough understanding of how PreparedStatements work - on the client side and the server side.

So if we have some typical CRUD operations and update an object repeatedly in the application, does it help to use a PS? I understand that we will have to close the PS every time otherwise it will result in a cursor leak.

So how does it help with performance? Does the driver cache the precompiled statement and give me a copy the next time I do connection.prepareStatement? Or does the DB server help?

I understand the argument about the security benefits of PreparedStatements and I appreciate the answers below which emphasize it. However I really want to keep this discussion focused on the performance benefits of PreparedStatements.

Update: When I say update data, I really mean more in terms of that method randomly being called several times. I understand the advantage in the answer offered below which asks to re-use the statement inside a loop.

    // some code blah blah
    update();

    // some more code blah blah 
    update();

.... 

public void update () throws SQLException{
 try{
      PreparedStatement ps = connection.prepareStatement("some sql");
      ps.setString(1, "foobar1");
      ps.setString(2, "foobar2");
      ps.execute();
 }finally {
     ps.close();

 }

}

There is no way to actually reuse the 'ps' java object and I understand that the actual connection.prepareStatement call is quite expensive.

Which is what brings me back to the original question. Is this "some sql" PreparedStatement still being cached and reused under the covers that I dont know about?

I should also mention that we support several databases.

Thanks in advance.

Answer 1

Prepared statements are indeed cached after their first use, which is what they provide in performance over standard statements. If your statement doesn't change then it's advised to use this method. They are generally stored within a statement cache for alter use.

More info can be found here:

http://www.theserverside.com/tt/articles/article.tss?l=Prepared-Statments

and you might want to look at Spring JDBCTemplate as an alternative to using JDBC directly.

http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html

Answer 2

Prepared statements can improve performance when re-using the same statement that you prepared:

PreparedStatement ps = connection.prepare("SOME SQL");

for (Data data : dataList) {
  ps.setInt(1, data.getId());
  ps.setString(2, data.getValue();
  ps.executeUpdate();
}

ps.close();

This is much faster than creating the statement in the loop.

Some platforms also cache prepared statements so that even if you close them they can be reconstructed more quickly.

However even if the performance were identical you should still use prepared statements to prevent SQL Injection. At my company this is an interview question; get it wrong and we might not hire you.

Answer 3

Anecdotally: I did some experiments with prepared vs. dynamic statements using ODBC in Java 1.4 some years ago, with both Oracle and SQL Server back-ends. I found that prepared statements could be as much as 20% faster for certain queries, but there were vendor-specific differences regarding which queries were improved to what extent. (This should not be surprising, really.)

The bottom line is that if you will be re-using the same query repeatedly, prepared statements may help improve performance; but if your performance is bad enough that you need to do something about it immediately, don't count on the use of prepared statements to give you a radical boost. (20% is usually nothing to write home about.)

Your mileage may vary, of course.

Answer 4

The notion that prepared statements are primarily about performance is something of a misconception, although it's quite a common one.

Another poster mentioned that he noted a speed improvement of about 20% in Oracle and SQL Server. I've noted a similar figure with MySQL. It turns out that parsing the query just isn't such a significant part of the work involved. On a very busy database system, it's also not clear that query parsing will affect overall throughput: overall, it'll probably just be using up CPU time that would otherwise be idle while data was coming back from the disk.

So as a reason for using prepared statements, the protection against SQL injection attacks far outweighs the performance improvement. And if you're not worried about SQL injection attacks, you probably should be...

Answer 5

Parsing the SQL isn't the only thing that's going on. There's validating that the tables and columns do indeed exist, creating a query plan, etc. You pay that once with a PreparedStatement.

Binding to guard against SQL injection is a very good thing, indeed. Not sufficient, IMO. You still should validate input prior to getting to the persistence layer.