• 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

pre-compile and prepared statement

 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nilesh - he's using a prepared statement.
 
Nilesh Pereira
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
reply
    Bookmark Topic Watch Topic
  • New Topic