Difference between Statement and preparedStatement (for SQL databases) ?

Discussion in 'Java' started by Ken Philips, Oct 16, 2004.

  1. Ken Philips

    Ken Philips Guest

    Sometimes I can see pure Statement declaration for accessing SQL databases
    others use preparedStatements.

    What is the difference?

    Ken
     
    Ken Philips, Oct 16, 2004
    #1
    1. Advertising

  2. (Ken Philips) wrote in message news:<ckqvcr$kbm$05$-online.com>...
    > Sometimes I can see pure Statement declaration for accessing SQL databases
    > others use preparedStatements.
    >
    > What is the difference?
    >
    > Ken


    exerpt from http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

    "Statement Versus PreparedStatement

    There's a popular belief that using a PreparedStatement object is
    faster than using a Statement object. After all, a prepared statement
    has to verify its metadata against the database only once, while a
    statement has to do it every time. So how could it be any other way?
    Well, the truth of the matter is that it takes about 65 iterations of
    a prepared statement before its total time for execution catches up
    with a statement. This has performance implications for your
    application, and exploring these issues is what this section is all
    about.

    When it comes to which SQL statement object performs better under
    typical use, a Statement or a PreparedStatement, the truth is that the
    Statement object yields the best performance. When you consider how
    SQL statements are typically used in an application--1 or 2 here,
    maybe 10-20 (rarely more) per transaction--you realize that a
    Statement object will perform them in less time than a
    PreparedStatement object."
     
    Michael Fortin, Oct 18, 2004
    #2
    1. Advertising

  3. Ken Philips

    Luke Webber Guest

    Re: Difference between Statement and preparedStatement (for SQL databases)?

    Michael Fortin wrote:

    > exerpt from http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html
    >
    > "Statement Versus PreparedStatement
    >
    > There's a popular belief that using a PreparedStatement object is
    > faster than using a Statement object. After all, a prepared statement
    > has to verify its metadata against the database only once, while a
    > statement has to do it every time. So how could it be any other way?
    > Well, the truth of the matter is that it takes about 65 iterations of
    > a prepared statement before its total time for execution catches up
    > with a statement. This has performance implications for your
    > application, and exploring these issues is what this section is all
    > about.
    >
    > When it comes to which SQL statement object performs better under
    > typical use, a Statement or a PreparedStatement, the truth is that the
    > Statement object yields the best performance. When you consider how
    > SQL statements are typically used in an application--1 or 2 here,
    > maybe 10-20 (rarely more) per transaction--you realize that a
    > Statement object will perform them in less time than a
    > PreparedStatement object."


    While I find O'Reilly a very good publisher, I take some issue with the
    above. The performance of a PreparedStatement is very definitely
    implementation-dependent, and not to be dismissed so lightly.

    Luke
     
    Luke Webber, Oct 18, 2004
    #3
  4. Luke Webber <> writes:

    > While I find O'Reilly a very good publisher, I take some issue with
    > the above. The performance of a PreparedStatement is very definitely
    > implementation-dependent, and not to be dismissed so lightly.


    And it ignores the other advantages of PreparedStatement, such as
    escaping quotes in setString() plus other data conversions, and the
    security implications in just throwing together a Statement with
    String concatenation, which can let a user bypass constraints by
    "commenting them out" - or even execute malicious SQL.
     
    Tor Iver Wilhelmsen, Oct 18, 2004
    #4
  5. Ken Philips

    venkat.sadasivam

    Joined:
    May 11, 2008
    Messages:
    7
    The prepared statement concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.

    The advantages of Prepared Statements are:

    1. As the execution plan get cached, performance will be better.
    2. It is a good way to code against SQL Injection as escapes the input values.
    3. When it comes to a Statement with no unbound variables, the database is free to optimize to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

    Other than training purpose it is better to use PreparedStatement to get full benefits and close all loopholes.
     
    venkat.sadasivam, Nov 5, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jakk
    Replies:
    4
    Views:
    12,279
  2. JY
    Replies:
    11
    Views:
    25,079
    Andrea Desole
    Feb 24, 2006
  3. Ken North
    Replies:
    0
    Views:
    609
    Ken North
    Jul 14, 2005
  4. ddog
    Replies:
    1
    Views:
    2,038
    Thomas Kellerer
    Feb 7, 2007
  5. Alex Kizub
    Replies:
    10
    Views:
    1,723
    Alex Kizub
    Feb 11, 2010
Loading...

Share This Page