PreparedStatements and performance

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.

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...

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.

PreparedStatements and performance

Related

Recent Posts