Discuss: PreparedStatement and Connection Pooling

Discussion in 'Java' started by Raj, Aug 25, 2003.

  1. Raj

    Raj Guest

    I've looked high and low for a satisfactory answer but could not find
    one.

    I've always thought PreparedStatements were more efficient than
    Statements since they are precompiled. Then I read somewhere that if
    the preparedstatements were being closed, the database will have to
    recompile the preparedstatements again. Somebody tell me that this is
    not true since the connections are only being returned to the
    connection pool and not actually being closed, the precompiled
    preparedstatement is still out there.

    Or if I am wrong, please correct me.

    Thanks,
    Raj
    Raj, Aug 25, 2003
    #1
    1. Advertising

  2. Raj

    Adam Maass Guest

    "Raj" <> wrote:
    > I've looked high and low for a satisfactory answer but could not find
    > one.
    >
    > I've always thought PreparedStatements were more efficient than
    > Statements since they are precompiled. Then I read somewhere that if
    > the preparedstatements were being closed, the database will have to
    > recompile the preparedstatements again. Somebody tell me that this is
    > not true since the connections are only being returned to the
    > connection pool and not actually being closed, the precompiled
    > preparedstatement is still out there.
    >


    Depends on the database and driver.

    My understanding of Oracle is that *any* SQL text sent to it for execution
    is first checked against a list of recently-executed SQL so that the
    execution plans, if any, could be re-used. Some Oracle design books make a
    big deal out of standardizing your SQL indentation style to increase the
    chances of a match during this process. But of course, if your
    PreparedStatement objects use the same SQL, then you will have matches after
    the first one.

    Of course, if you're using WebLogic connection pooling, there's an awful lot
    that happens behind-the-scenes to avoid "premature" closing of database
    resources. (IE, closing a PreparedStatement doesn't actually release
    anything in the database except any open ResultSets -- and closing a
    Connection simply returns it to the pool.)

    Normally, I wouldn't worry about these kinds of things. There is one
    advantage to PreparedStatement even if the driver does nothing more than
    turn it into a regular plain-vanilla Statement behind your back: The
    PreparedStatement contract handles string escape syntax for you. That is,
    you don't have to worry about escaping strings with embedded apostrophes in
    them; you let the driver do it for you.

    -- Adam Maass
    Adam Maass, Aug 25, 2003
    #2
    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. William \(Bill\) Vaughn

    Re: SqlConnection and connection pooling

    William \(Bill\) Vaughn, Nov 14, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    486
    William \(Bill\) Vaughn
    Nov 14, 2003
  2. Edward W.
    Replies:
    5
    Views:
    3,249
    =?Utf-8?B?SEFSSSBQUkFTRCBCQVJV?=
    Dec 15, 2004
  3. Rob Nicholson

    ADO.NET, connection pooling and ASP.NET

    Rob Nicholson, Aug 31, 2005, in forum: ASP .Net
    Replies:
    18
    Views:
    4,473
    Rob Nicholson
    Sep 17, 2005
  4. Justin M
    Replies:
    0
    Views:
    414
    Justin M
    Dec 8, 2003
  5. Baba
    Replies:
    3
    Views:
    4,469
Loading...

Share This Page