CODEWITHSUNDEEP
sqlsql-serverjdbc
s5s
s5s

Reputation: 12134

PreparedStatement, CallableStatement vs Statement - is Statement always slower

Would executing a PreparedStatement or CallableStatement result in different query optimization than if the exact same query was just executed as a Statement? That is, does the query optimisation depend on the class used to execute the statement?

Would a PreparedStatement or CallableStatement always be faster than a Statement?

I'm strictly interested in performance and not really in other benefits such as portability.

Upvotes: 0

Views: 240

Answers (1)

Codo
Codo

Reputation: 78835

The main performance related issue is that parsing an SQL statement (incl. checking that the tables and columns exist) and creating an optimized execution plan is expensive. If a cached execution plan can be reused, the execution is usually faster.

So the questions can also be rephrased as: Do I need to use PreparedStatement (and CallableStatement) in order to reuse a cached execution plan?

The answer depends on your database system.

Some systems like Oracle (in its default and recommended settings) requires PreparedStatement to reuse an execution plan. Otherwise it will reparse the entire query (aka as hard parse) whenever a single value in the query changes. Even worse, as the new query and its plan will be added to the cache, something else will need to be evicted from the cache. So it can also negatively affect other database clients using prepared statements.

Other systems such as SQL Server do not fully rely on prepared queries. For non-prepared queries, they will guess which values should be turned into parameters, will create an execution plan and cache it. If the another non-prepared query matches a previously parametrized query, the plan can be reused. This approach is very beneficial when lazy programmers do not consistently use prepared queries. But it does not reach the full speed of prepared queries as the parametrization and matching take more time and as the generated execution plan might have too many parameters, be too generic and therefore suboptimal.

And you don't want to hear it but I'll say it anyway: Prepared statements are the best recipe against SQL injection attacks.

Upvotes: 1

Related Questions