Back to Basics: Statement vs. Prepared Statement
October 23, 2008 36 Comments
“What is the difference between Statement and Prepared Statement?” this is one of my favorite interview question. Most of the interviewee will say “Statement is not precompiled, prepared statements are precompiled, hence prepared statement will faster than statement”. My next question would be “What do you mean precompile”, the answer will be “the SQL gets compiled and reused”. My last question would be “In which layer the statement gets compiled? (like DB, JDBC, Java Apps.)”, most of them will answer JDBC. Since the answers are abstract, it could be interpreted in either way. Though the prepared statement concept are there for while very few provides in-depth answer. I did googling, the answers are scattered in various sites let me consolidate and give you a comprehensive view.
The prepared statement concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.
The advantages of Prepared Statements are:
- As the execution plan get cached, performance will be better.
- It is a good way to code against SQL Injection as escapes the input values.
- When it comes to a Statement with no unbound variables, the database is free to optimize to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.
Other than training purpose it is better to use PreparedStatement to get full benefits and close all loopholes.
I always expect “execution plan” jargon word when talking about prepared statements.