• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

What exactly we mean by Precompiled SQL Statement

 
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Guys,
I came across an article stating the following and several books too...

PreparedStatement allows you to precompile your SQL and run it repeatedly.


Now can anyone explain me what does it really mean..."precompile your sql" i somehow don't get it.

Hey thanks for being patient..
Regards.
Sagar
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanx Dima..

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..

God bless mankind..
regards
Sagar
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey Thanx John,
That was elaborate..and can go into my notes..
Thanx buddy
 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks john,i too understand very from reading your explanation prepared statement.
 
shyam sunder prasad
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Raja ,

with my knowledge i can say the what does it really mean.

preparedStatement is compiled onlyyonce.
Ex:


above code compiled once, because java compiler checks for syntax validation.

and sql is validation will be done by database driver.

mainly we use it in loops for better performance.





if we go for Statement,this will be compiled ,validated and executed every time.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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(?,?)");
ps.setString(1,"a");
ps.setString(2,"b");
ps.executeUpdate();
ps.close;
/*
some java code
*/
PreparedStatement ps1=conn.preparedStatement("insert into tableA values(?,?)");
ps1.setString(1,"a");
ps1.setString(2,"b");
ps1.executeUpdate();
ps1.close;

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.

Please clarify and also thanks in advance!!
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

In general:

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.
 
She still doesn't approve of my superhero lifestyle. Or this shameless plug:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic