PreparedStatements and performance

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.

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