Statement vs Prepared Statement in terms of Precompilation

Question

I understand the main difference between using Statement and PreparedStatement (PreparedStatements allow passing in parameters). But I read about a subtle difference between the two - namely that PreparedStatements can be quicker than generic Statements, because PreparedStatement SQL is precompiled.

What exactly does being precompiled mean, and why does it make a difference?

Answer 1

A prepared statement performs the following checks:

• Makes sure that the tables and columns exist
• Makes sure that the parameter types match their columns
• Parses the SQL to make sure that the syntax is correct
• Compiles and caches the compiled SQL so it can be re-executed without repeating these steps

Answer 2

The most important part of the compilation process is the creation of the query plan.

The query plan, as the name suggests, indicates how the database should execute the query (e.g. which indexes to use, the join types to use such as nested-loop/hash/merge join etc.). This can take time as the database needs to analyze information on the tables to make a good guess at what is optimal (e.g. table sizes, indexes available, how specific are the indexes etc.).

Whilst prepared statements can save time you have to be careful as they can sometimes can make your program slower. Why? Because by not providing the where clause values as part of the compilation, the database has to guess at the values you are going to provide and it may choose values which lead to a suboptimal query plan for you.

This may be most apparent for range queries. For example, say you create a prepared statement with minimum and maximum dates as parameters. This query you will use to retrieve a few days of transactions. Given the small date range, this query may be most efficient to execute if the database uses the date non-clustered index.

However, since the database does not know what parameters you will supply the database may optimize for a much larger date range (e.g a year or more), which may be most efficient to execute using a table scan. In this case using the prepared statement would slow down your program (unless you provide a hint to the database on what parameters to optimize for or explicitly indicate which indexes to use).

Of course if in your prepared statement the parameters you will supply will point to individual records (e.g. unique keys on tables), the database really can't go wrong in creating the query plan.

I guess I am trying to emphasize that there are pros and cons here. PreparedStatements can be quicker in some situations but you have to be careful.