aspose file tools*
The moose likes JDBC and the fly likes pre-compile and prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "pre-compile and prepared statement" Watch "pre-compile and prepared statement" New topic
Author

pre-compile and prepared statement

Hari babu
Ranch Hand

Joined: Jun 25, 2001
Posts: 208
When i use a prepared statement as shown below

the statement is pre-compiled, when we say the statement is pre-compiled what internally happens because i believe when the query is fired to the database, the database tries to compile the statement, which will be extra work as the sql statement is pre-compiled at java layer and also at the database layer
Hari
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
All SQL statements that are sent to the database whether through JDBC or any other mechanism always go through 2 stages:
i) Prepare - Check that the SQL syntax is valid and work out an execution plan for SQL statement (i.e. sort out which indexes, tables etc are going to be used)
ii) Execute - Actually apply the SQL statement to the database to perform the update, insert, delete or selection.
The time taken on each of these steps will obviously depend on the SQL statement and the database being accessed however the first step can often take a significant amount of time especially if several DB tables are involved. Therefore it makes a lot of sense to ensure that the preparation of the SQL statement is only performed once if you are applying the same SQL statement to multiple records.
Hope this helps


Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
When you fire the query the database will first 'hash' the query & search for it in its cache (LRU).
If it finds the query it doesn't need to,
syntax check it.
figure out the execution plan.
probably some other stuff I cant remember.
If your using Oracle take a look at V$LIBRARYCACHE view specifically the RELOADS column, you should be aware than ANY DDL on a table will invailate the compiled query.

You are using Oracle right?
Nilesh Pereira
Ranch Hand

Joined: Apr 14, 2003
Posts: 53
A Statement will always proceed through the four steps in the database for each SQL query sent:
(1) Parse the SQL query
(2) Compile the SQL query
(3) Plan/optimize the data acquisition path
(4) Execute the optimized query
This can be a performance issue if you are executing the same SQL over and over again. A PreparedStatement takes advantage of repeated queries by pre-executing steps (1) - (3) when it is created/prepared. Obviously, a PreparedStatement adds no value for dynamic SQL queries like those used in reports.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
Nilesh - he's using a prepared statement.
Nilesh Pereira
Ranch Hand

Joined: Apr 14, 2003
Posts: 53
I know. I was trying to explain this:
when we say the statement is pre-compiled what internally happens because i believe when the query is fired to the database, the database tries to compile the statement, which will be extra work as the sql statement is pre-compiled at java layer and also at the database layer

Also, why it pre-compiles, and when it is useful to pre-compile.
Nilesh Pereira
Ranch Hand

Joined: Apr 14, 2003
Posts: 53
when we say the statement is pre-compiled what internally happens because i believe when the query is fired to the database, the database tries to compile the statement, which will be extra work as the sql statement is pre-compiled at java layer and also at the database layer

Okay, let me try again
It is not extra work, because the Prepared Statement is NOT compiled when the query is fired. Rather, it's compiled when the PreparedStatement is created/prepared.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: pre-compile and prepared statement