This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit. If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database. Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application.
Raja Sagar Panamgipalli
Joined: Aug 13, 2003
I some what get it but don't get it.. can u do a favor by expalianing the following piece of code..
PreparedStatement pstmt=con.prepareStatement("INSERT INTO EMP(NAME,PHONE)VALUES(? ,?)"); then i give pstmt.setString(1,"Jim"); pstmt.setString(2,"274 7071"); pstmt.executeUpdate();
what exactly happens here... In a preparedstatment we are specifying the arguments while creating an object of PreparedStatement from Connection Obj.when unlike in a createStatement method of the Connection we don't do that. Have i messed up...sorry...plz elaborate..
You are on the right track, Raja. The basic "createStatement()" method returns a "Statement" instance that can be used to send SQL to the database for execution. Every SQL you send will get validated, compiled, and executed. With the "prepareStatement(...)" method call you pass in the SQL once (at creation time) where it can be validated and compiled. It will throw an exeception if the SQL is syntactically invalid, or references tables or columns that don't exist, or if there number of parameters (?) don't match up with the number of values specified. That's what happens when you do:
With this prepared statement you can do only one thing: insert records into the "EMP" table. You have now told the database what you want to do, but not what values to insert. That comes with the second set of statements:
This means that using the given prepared statement (pstmt), you want to use "Jim" as the first parameter and "274 7071" as the second parameter, and then execute the SQL against the database. There is overhead in using prepared statements, and if you only insert a few records it will probably take a little more time. However if you insert many records you will see a performance gain. There is another advantage to using prepared statements, and that is with handling String and Date/Time data. Some databases use single quotes as text delimiters, others use double quotes, and some recognize either. However if you want to insert character data containing single- and/or double-quotes, this can be tricky using a regular Statement. And if you move from one database to another this can force you to modify your SQL. The same can be said about inserting dates and times: each database has a different format that you have to follow. But when you use a PreparedStatement, the values you specify with "setString(...)" can contain single quotes or double quotes and the underlying JDBC classes will take care of the conversion for you. This is a big advantage of using prepared statements. Good luck!
Raja Sagar Panamgipalli
Joined: Aug 13, 2003
Hey Thanx John, That was elaborate..and can go into my notes.. Thanx buddy
One advantage here is that having pre-compiled and cached the SQL command using bind variables (?, ?, ...), you can call the same SQL multiple times with different values for the bind variables, but there is no need to re-compile the SQL as the DB can just re-use the cached statement. This can make a significant difference to performance when you are executing a given SQL command many times with different values.
I have some doubts regarding preparedstatement.
1. Who will do the pre compilation for this kind of statement.
2. below is some code snippet:
PreparedStatement ps=conn.preparedStatement("insert into tableA values(?,?)");
some java code
PreparedStatement ps1=conn.preparedStatement("insert into tableA values(?,?)");
Now as both ps and ps1 contain the same SQL query so is there any compilation for ps1?
3. if we call close() method of preparedStatement then will that precompile statement also wipeout from Database.
All these things generally depend on the database and JDBC driver. You should consult your database and JDBC driver documentation if you want to go into such details.
1. Precompilation is done by the database. Some simpler databases don't precompile statements at all. Others might precompile it on the prepareStatement call, and yet others might do it when execute is first called on the statement, taking values of the parameters into account when compiling (creating a plan for) the statement.
2. Databases that do precompile statements usually cache them, so in all probability ps1 won't be compiled again. Some JDBC drivers (eg. Oracle's) even cache prepared statements, so they haven't actually closed it when ps.close() was called.
3. Databases generally cache statements until something evicts them from the cache. Remember that yours is very probably not the only application on the database, so your application can negatively impact other (if it hoards database resources) and similarly, other applications can negatively impact your.