Back to Basics: Statement vs. Prepared Statement
October 23, 2008 38 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.
Delicious
Digg
reddit
Facebook
StumbleUpon
Venkat Sadasivam 
Venkat,
I accept Prepared Statement is better w.r.t SQL Injection and repeated execution on same SQL with different values…
But, I would also use plain Statement Object whenever i’m sure that current SQL is going to execute only once and SQL Injection worries are handled in other ways… I suggest using Statement Object here because there is only one network call where as Prepared Statement takes 2 one for compiling and another for execution… In this case how will a PreparedStatement is better than Statement Object?
Regards,
Muthu
Muthu, thanks for your comments. At last busy person got time to read some articles too :)
How do you say prepared statement makes two calls? If you have any reference please post.
btw, prepared statement makes one call to database like Statement; but the execution plan get cached at database so the subsequent calls uses the same execution plan which makes the query execution faster.
Venkat,
Assume the following query, “Insert into Cities (name, population) Values (‘New York’, 500000 ); ”
If I use prepare statement this would be the code
String str = “Insert into Cities (name, population) Values (?, ? )”;
Connection con = .getConnection();
PreparedStatement p = con.prepareStatement( str);
p.setString(‘New York’);
p.setInt(500000);
p.executeUpdate();
If you look at these statements, there will be 2 Server calls. First one will be during “con.prepareStatement( str);” call, At this point of time the query gets compiled ( Execution plan generation/caching) on the server end. During “p.executeUpdate();” call a second call made to server to execute it.
If we use Statement Object for these kind of trival queries, server call is made only once…
I believe this will explained my point… Hope to hear your thoughts on this soon… :)
Take Care
Regards,
Muthu
Muthu, The query and values are not send to database in a separate round trip. The actual database communication happens only during executeUpdate() or executeQuery().
To analyze the behaviour use the below code in has invalid table “User1”.
PreparedStatement preparedStatement = connection.prepareStatement(“SELECT FirstName, LastName FROM User1”);
System.out.println(“Statement prepared”);
ResultSet resultSet = preparedStatement.executeQuery();
System.out.println(“Statement executed”);
After running the above code you will only see “Statement prepared” in console. As you mentioned if it makes calls to database during prepareStatment, that line should throw error for invalid table name.
Venkat,
Yes, You are right.. I could see the “statement prepared” in console…
However, I’m 100% sure that there will be 2 n/w calls in prepared statement.. My example to explain that seems to be wrong. Please follow this link and click on Qn. No 25..
http://www.theserverside.com/tt/talks/videos/JohnGoodson/dsl/interview.html
You will see an explanation given by John Goodson on this topic.. I will also comeup with the proper example soon to explain my point asap…
~Muthu
Venkat,
I got the answer for printing “Statement Prepared” output in console even when the table doesn’t exist in DB..
The reason is while executing statement
PreparedStatement preparedStatement = connection.prepareStatement(”SELECT FirstName, LastName FROM User1″);
At this point of a server call will happen and it just parses the syntax.. It will not check for the existence of table or column.. Since SQL syntax is correct, DB will not throw any error and hence process continues to next statement…
If you have any doubt in this Copy the SQL statement and paste SQLSever Query Analyser and press CTRL+F5.. It will just parse the query and print of command executed successfully message, even when table1 doesn’t exist… If you press just F5 then only it will throw error…
Hope this clarifies my point…
~Muthu
Muthu, I am still not convinced that prepared statement makes two calls. However the below link shows some performance result of JDBC drivers’ on various scenarios.
http://oreilly.com/catalog/jorajdbc/chapter/ch19.html
Venkat, That article clearly shows what I’m talking about.. For Non-repeated inserts statements are much better option than prepared statements… You can see the time taken by P.Stmt is nearly 10 times higher that Stmt object…
We all know that one of costliest job in JDBC is network calls. This additional time taken is only because of a additional n/w call rather than anything else…
~Muthu…
Thanks Muthu, But these all answer’s don’t remove my one confusion. As i think prepare statement executes only when we run java file while plain statement executes every time when we compile java file that makes it slow than prepared statement. Am i right.
@Vivek, I don’t understand ur question. If u r asking whether Statement executes on every java compile, My answer is a big “NO”.. While compiling a source it is just getting transferred into byte code which has to be executed by ur JVM for execution. So there is no way that ur Code is making calls to DB. Compilation is just a conversion of ur source from human readable format to JVM readable format.
~Muthu
I have a doubt,
When the method ‘method()’ executes for the second time, will there be any benefit due to using preparedStatement ???
Will the query gets compiled for the second time or it won’t, because it has been compiled already during the first call ?
method(){
PreparedStatement preparedStatement = connection.prepareStatement(”SELECT FirstName, LastName FROM User1″);
preparedStatement.executeQuery();
}
}
The query won’t get compiled on second time. So second time it will execute faster that is the benefit of using prepared statement.
Just to reiterate… the prepareStatement method is used to pre-compile the sql query which does require one network call to the database.
The executeQuery method sends the precompiled query to the database in a 2nd network call.
A Statement does both of compile and execute in a single method call with a single network call.
The early pre-compile does not save you much time if you are comparing a one-time sql execution, but if you intend to run the same query multiple times, the prepared statement is great and you should see much better performance when compared to running with the normal statement object.
My doubt is
will the query get recompiled while the below method is invoked second time ?
method(){
PreparedStatement preparedStatement = connection.prepareStatement(”SELECT FirstName, LastName FROM User1″);
}
Prepared statements are getting cached at database server level. The query will get compiled only once subsequently it will use cached execution plan.
partheeban, the query will be recompiled again and again in your example.
you should call the preapreStatement only once and repeat the call to executeQuery to benefit from the prepareStatement call.
I do agree with Venkat.
I did a small experiment for statement vs prepared statement. I wrapped Oracle 10g driver with spy driver to log communication between my program and database.
Here is my findings:
1.) con.prepareStatement(“…”) does not make a database call. Along with correct and incorrect table name, I tried with right and wrong syntax and symantics of the SQL.
Driver did not make a connection to DB a single time. The only time I found exception thrown while executing prepareStatement method was when my SQL was empty string. But even at this
time DB call was not made. Exception was thrown by driver code.
2.) With the prepared statement, connection to DB was made when the executeQuery() method was executed. The very first time when executeQuery() is executed, Oracle does following
three steps:
a.) Parsing: Parse the statement’s syntax and semantics.
b.) Generating the execution plan: For each statement, generate an
execution plan and store it in a shared memory area called the shared
pool.
c.) Executing: Execute the statement using the plan generated in step 2.
And what I observed was when the SQL is executed with prepared statement very first time, query execution takes more time compared to very first time SQL is executed with java.sql.Statement.
But with Preparedstatement you have to pay this ” time penatly” only for very first time (just like on the very first request time is taken by jsp compilation). Next onwards, when you execute prepareStatement and executeQuery methods, query execution would be much
faster because now Oracle will do only query parsing and query execution based on already generated and store execution plan.
3.) With java.sql.Statement, whenever you execute query with change in only parameter like emplyeeid, city etc, Oracle considers query as new and query would be “hard parsed” meaning query parsing,
generation of execution plan and finally query execution would be done.
In, enterprise application, the method, which has the code for query execution, would be executed multple times my multiple user requests. And so, we can gain performance benefit by using PreparedStatement.
Thanks Tapshil, for sharing your findings.
If we close the preparedtatement still the execution plan exist in shared pool. How it behaves in below scenario.
method()
{
ps = connection.prepareStatement(sql)
ps.set…
ps.execute…
ps.close()
}
@Jagan Based on the database server i.e. Oracle, Sybase etc the eviction policy for PreparedStatement in the pool is decided. This may be based on the current load or some LRU algorithm or heuristics particular to database implementation.
The Explanation is nice
Thank you one and all for your valuable suggestions..
Hi Venkat,
very nice article, thanks a lot.
Even i was under impression that PreparedStatement does better than Statement till i came across one ppt released by Oracle saying this need not be necessarily true everytime…
since there is considerable overhead involved for this…
unfortunately I don’t have the link of that ppt; I will share it incase I get it…
regards,
abhijit
If you are running a SQL statement once use Statement object to execute query using java program. if its running several time with same query along with different parameters then better option is PreparedStatement.
With the Oracle Thin Driver we are experience 40 times higher execution time for one time SQL select query execution using PreparedStatement approach compare to Statement approach in an enterprise level application.
So you can not blindly reply on PreparedStatement or Statement approach without knowing what you are doing
If you are running a SQL statement once use Statement object to execute query using java program. if its running several time with same query along with different parameters then better option is PreparedStatement.
With the Oracle Thin Driver we are experience 40 times higher execution time for one time SQL select query execution using PreparedStatement approach compare to Statement approach in an enterprise level application.
So you can not blindly rely on PreparedStatement or Statement approaches without knowing what you are doing
If you are running a SQL statement once use Statement object to execute query using java program. if its running several time with same query along with different parameters then better option is PreparedStatement.
With the Oracle Thin Driver we are experience 40 times higher execution time for one time SQL select query execution using PreparedStatement approach compare to Statement approach in an enterprise level application.
So you can not blindly rely on PreparedStatement or Statement approach without knowing what you are doing
Let us consider this example, suppose we have a search screen which is taking user inputs for different parameters and at back end SQL is build. If one parameter is null and that one is not appended in WHERE clause. So, generated SQL is different for different search condiations. Here which one will give better performance, preparedStatement or Statement and why ?
Interesting take on this issue where he says you must never use statements and always use prepared or callable statements.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1993620575194
in an enterprise environment, i understand the pro’s of PreparedStatement, so, is Statement useless since it is always being parsed, doing execution plan and executed?
Yes, in my opinion you should never use Statement for any DML queries.
What if we explicitly close the PreparedStatement? Will it still be cashed in the database, and reused again to gain better performance?
Will following code have any advantage than using Statement?
pstmt = conn.prepareStatement(updatesql);
pstmt.clearParameters();
ps.setString(1, value);
…
int rel = pstmt.executeUpdate();
pstmt.close();
Hi Muthu
As per Tapshil’s findings, PreparedStatement also hits the database once contradictory to your saying that it hits the DB twice. Do you agree with him or you still have different thoughts on this.
Thanks a lot for article.
Hi Tapshi,Muthu Thanks for this wonderful article .
My question is why to use statement for single execution of sql query ,when for first time
prepared statement and statement will give same result .
I have gone throught the jdbc driver api of SQLServer and i dont see any code there while making PreparedStatement object and setting values. there is only one network call when we gor for executeUpdate.
Pingback: Error ORA-01000: Problema de punteros al hacer los INSERT con Java mediante JDBC | Apuntes de Programación
Reblogged this on A big picture of Hadi Waseem.
Hi All,
I have a different experience while using PreparedStatement and Statement.
I my application we need to implement bulk insert of records.
We are using MySql as database and the drivers are thin drivers.
I wrote the method to insert data into database using Batch insert, Prepare statement and statement. Surprisingly I found that the statement implemented code less time that the other two.
I am completely speechless. Is there any driver dependency or for MySql is there an issue.
Can someone help me in understanding this issue.
Thanks in advance…