• 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

Prepared Statement Doubt!!

 
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi java Gurus,
I've just started JDBC and its quite interesting in some ways like transactions and also the statements part too.. well after reading a prepared statement i came through these two phrases quite often, they are 'PREPARSED' and 'PRECOMPILED'. what exactly do we mean by these words. does they mean that SQl is embedded into the statement object so thats why we say it preparsed and precompiled and how this is helpful, if they are embedded in statement object as the statements basically communicate with DB, so how does this helps?
Thanks in advance.
Regards,
Harry
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Every SQL statement, before executed, needs to be "compiled" by the database server - that is, the server needs to decide how to exactly execute the statement.
As this is a costly operation, most databases cache those compiled statements. That is, when you want to execute that statement for a second time, it doesn't need to be compiled again, therefore leading to better performance.
Now imagine the following two statements:
select * from table where id=1
select * from table where id=2
For the database, those are two different statements, so both get compiled independently from each other.
Now, if you use a PreparedStatement, it should look like
select * from table where id=?
In this statement, you can replace the value for the question mark, without the database needing to recompile the whole statement every time.
Did that help?
 
Ranch Hand
Posts: 3178
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also in the java.sql.PreparedStatement API, it says
An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You guys might want to check out tips #3 and #5 from this article.
In short, PreparedStatements are faster for repeated queries but not necessarily for rare queries. Also, this excerpt from O'Reilly's Java Programming with Oracle JDBC presents some interesting performance benchmarks.
 
Harry Singh
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks guys!! yeps it helped a lot..
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd like to add two things.
First, I don't buy tip #3 linked to above as it stands. It reads Use the Statement object for time-critical [...] SQL statements. Your SQL statement may be time-critical because it (a) either takes a long time to run, (b) or it doesn't take long but is executed many times over. In case (a) the difference between a PreparedStatement and a Statement is likely to be insignificant compared to the execution time of the statement itself. In case (b) a PreparedStatement is generally faster.
Second, there are other reasons to use PreparedStatement apart from execution speed. The naive approach to include String parameters in a Statement is often a source of application bugs or, worse, a source of grave security holes. Likewise, Date parameters can land you in database date format hell unless your environments are exactly the same (or unless you use SQL escapes -- anyone?). Parameters in a PreparedStatement exhibit none of these problems.
Bottom line is, in my experience the PreparedStatement represents a solid default choice in most cases, and a Statement is best used only if you have a specific reason to do so. To advise the other way around opens too many pitfalls for the unwary.
- Peter
[ October 14, 2003: Message edited by: Peter den Haan ]
 
Lasse Koskela
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Peter has a point here. The only case where I tend to use regular Statements is when I need to construct the SQL statement dynamically in a way that would be too complicate to do with a PreparedStatement and setXXX() methods.
 
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
few more Q's,
When I call close() on prepared statment object will the compiled version of the sql be discarded or the database overrule's this call and still retains it. I remotely remember reading that it depends on the driver/database/ app server ?? (think Weblogic cache the statement even if an explicit close is called), if so what does the call to close does, other than closing the result set
(Or)
you guy recommend never to close the prepared statement.
 
Lasse Koskela
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You should always close the PreparedStatement. If the container vendor wants to implement optimizations, it can do it transparently to your code.
 
Peter den Haan
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Vinod John:
When I call close() on prepared statment object will the compiled version of the sql be discarded or the database overrule's this call and still retains it.[... ] (Or) you guy recommend never to close the prepared statement.

It is best to close the prepared statement. To do anything else can cause resource leaks (having to troubleshoot an Oracle "maximum number of open cursors exceeded" is no fun). As for your first question, that is implementation dependent. The database I know best is Oracle, so I'll talk a bit about that.
Oracle does not quite work the way you might expect. It maintains a database-side statement cache where recently executed statements are cached together with their execution plans. This cache is entirely independent of the lifecycle of the JDBC objects you're using: even after you close() your PreparedStatement, if you create a new one with the same SQL text immediately afterwards, Oracle will be using a cached plan and you'll get the benefits of prepared statements. Even if another client creates it, on a completely different connection.
Confusingly perphaps, this Oracle cache works for every SQL statement, including those that you create using a Java Statement object. So does that mean it won't make a difference which one you use? False -- it certainly will. When you use a Java Statement object, the SQL text will be different for every different value of your parameters. With PreparedStatements, the same SQL is used regardless of parameter values. The effectiveness of Oracle's cache will therefore generally be much lower for Statements than for PreparedStatements, and the latter will still be faster for anything executed a fair number of times.
- Peter
[ October 14, 2003: Message edited by: Peter den Haan ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic