PreparedStatement performance

Performance of PreparedStatementu

The received wisdom is that when working with JDBC, you should use prepared statements instead of ordinary statements. The reasons are obvious. Prepared statements are supposed to be faster and more secure, and plenty of people will beat you over the head with those arguments. The question is what really happens to performance. For a long time I believed that a PreparedStatement paid off only when you ran the same statement several times, because of the overhead of preparing the query.

This week we discussed the question on the builder.cz forum. After I asked for a test that would show how it really worked, a very good presentation surfaced, Understanding Prepared Statements in Oracle, and it changed my mind, at least for Oracle. The recommendations are clear.

• Prefer prepared statements
• Parse once — execute often
• Use bind variables where appropriate (Don’t over-bind)
• Do not rely on Cursor Sharing
• Use JDBC prepared statement caching

I highly recommend reading it even if Oracle isn’t your database. Whether we can take these recommendations at face value for other databases is another question…