PreparedStatement.close() behavior question (WL 8.1) - Weblogic

This is a discussion on PreparedStatement.close() behavior question (WL 8.1) - Weblogic ; All, I'm not sure whether I need to call close() every time I execute the query for a PreparedStatement, like I would normally do for a Statement. When using a connection from the pool, and when I have room in ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: PreparedStatement.close() behavior question (WL 8.1)

  1. PreparedStatement.close() behavior question (WL 8.1)


    All,

    I'm not sure whether I need to call close() every time I execute the query for
    a PreparedStatement, like I would normally do for a Statement. When using a connection
    from the pool, and when I have room in the statement cache, does calling close()
    simply put the statement back into the cache for that connection? My tests don't
    seem to indicate this:

    All tests use 100 iterations of a simple query.
    All test close the ResultSet after each query.
    All tests obtain a single connection before beginning the test and close it afterwards.
    All tests were executed out of the container, which I believe accounts for the
    approximately ten-fold increase in time for the pooled connection tests.

    Test 1: Statements on a regular Connection
    Time: 962 ms
    Open cursors left by this query for the connection = 0

    Test 2: PreparedStatements on a Connection preparing and closing the PreparedStatement
    only once
    Time: 430 ms
    Open cursors left by this query for the connection = 0

    Test 3: PreparedStatements on a Connection preparing and closing the PreparedStatement
    each iteration
    Time: 931 ms
    Open cursors left by this query for the connection = 0


    Test 4: Statements on a pooled Connection
    Time: 7310 ms
    Open cursors left by this query for the connection = 0

    Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    only once
    Time: 4747 ms
    Open cursors left by this query for the connection = 1

    Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    each iteration
    Time: 7050 ms
    Open cursors left by this query for the connection = 1

    I think the last two leave a cursor open because the statement is left in the
    cache after calling close(). However, if this is so, shouldn't I have seen a
    dramatic (relative) decrease in the time spent 'preparing' the statement in test
    6? In other words, shouldn't test 6 have obtained the statement from the cache
    each time it called prepare(), yielding a total execution time similar to test
    4?

    Thanks,

    Brian

  2. Re: PreparedStatement.close() behavior question (WL 8.1)



    Brian Hartin wrote:
    > All,
    >
    > I'm not sure whether I need to call close() every time I execute the query for
    > a PreparedStatement, like I would normally do for a Statement. When using a connection
    > from the pool, and when I have room in the statement cache, does calling close()
    > simply put the statement back into the cache for that connection?


    Hi, no it doesn't. Closing a prepared statement that has been cached involves cleaning it
    up so it is in a known default state for it's next use:

    public static void cleanUpStatementForReUse(PreparedStatement p)
    {
    try { p.clearParameters(); p.clearBatch(); } catch (Throwable ignore) {}

    try { p.setEscapeProcessing(true); } catch (Throwable ignore) {}

    try
    {
    if (p.getFetchDirection() != ResultSet.FETCH_FORWARD)
    p.setFetchDirection(ResultSet.FETCH_FORWARD);
    } catch (Throwable ignore) {}

    try
    {
    if (p.getFetchSize() != 0)
    p.setFetchSize(0);
    } catch (Throwable ignore) {}

    try
    {
    if (p.getMaxFieldSize() != 0)
    p.setMaxFieldSize(0);
    }
    catch (Throwable ignore) {}

    try
    {
    if (p.getMaxRows() != 0)
    p.setMaxRows(0);
    }
    catch (Throwable ignore) {}

    try
    {
    if (p.getQueryTimeout() != 0)
    p.setQueryTimeout(0);
    }
    catch (Throwable ignore) {}

    try
    {
    p.clearWarnings();
    }
    catch (Throwable ignore) {}
    }

    If you have some code that may need to re-use a given PreparedStatement within a single
    user invocation, it would be best to retain and re-use the statement until it is not
    needed for the current uppermost method, at which time it should be closed.
    (ie: like your tests 2 and 5). I believe if you time the specific calls within
    a test, you will find that the statement.close() (from a pooled connection) will take
    the extra time. It's clear that the pooled test does much better with a cached
    prepared statement than with plain statements.
    I would be very interested to see your results if the tests were run in the
    container, such as in a JSP etc. This way the pooled access would not have to go
    through any more OS processes than the direct driver tests.
    thanks
    Joe

    My tests don't
    > seem to indicate this:
    >
    > All tests use 100 iterations of a simple query.
    > All test close the ResultSet after each query.
    > All tests obtain a single connection before beginning the test and close it afterwards.
    > All tests were executed out of the container, which I believe accounts for the
    > approximately ten-fold increase in time for the pooled connection tests.
    >
    > Test 1: Statements on a regular Connection
    > Time: 962 ms
    > Open cursors left by this query for the connection = 0
    >
    > Test 2: PreparedStatements on a Connection preparing and closing the PreparedStatement
    > only once
    > Time: 430 ms
    > Open cursors left by this query for the connection = 0
    >
    > Test 3: PreparedStatements on a Connection preparing and closing the PreparedStatement
    > each iteration
    > Time: 931 ms
    > Open cursors left by this query for the connection = 0
    >
    >
    > Test 4: Statements on a pooled Connection
    > Time: 7310 ms
    > Open cursors left by this query for the connection = 0
    >
    > Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > only once
    > Time: 4747 ms
    > Open cursors left by this query for the connection = 1
    >
    > Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > each iteration
    > Time: 7050 ms
    > Open cursors left by this query for the connection = 1
    >
    > I think the last two leave a cursor open because the statement is left in the
    > cache after calling close(). However, if this is so, shouldn't I have seen a
    > dramatic (relative) decrease in the time spent 'preparing' the statement in test
    > 6? In other words, shouldn't test 6 have obtained the statement from the cache
    > each time it called prepare(), yielding a total execution time similar to test
    > 4?
    >
    > Thanks,
    >
    > Brian



  3. Re: PreparedStatement.close() behavior question (WL 8.1)


    Joe:

    The important thing that I want to confirm is that calling close() on a PreparedStatement
    in-container _does_ put it back in the cache, and that the benefits of using a
    PreparedStatement are _not_ lost by calling close(). I re-performed the pooled
    connection tests in the container. The following tests seem to confirm that this
    is true:

    (I've added HTML tags to hopefully preserve the line breaks I lost last time)

    Test 4: Statements on a pooled Connection

    Time (in container): 671 ms

    Time (out of container): 7310 ms

    Open cursors left by this query for the connection = 0



    Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    only once

    Time (in container): 282 ms

    Time (out of container): 4747 ms

    Open cursors left by this query for the connection = 1



    Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    each iteration

    Time (in container): 297 ms

    Time (out of container): 7050 ms

    Open cursors left by this query for the connection = 1




    As expected, the pooled connection tests perform much better in container. It
    seems to me that the fact that the results of tests 5 and 6 are nearly identical
    when run in-container confirms my hope that the prepareStatement() and close()
    calls are intercepted. Is this your conclusion?

    Thanks for your help,

    Brian



    Joe Weinstein wrote:
    >
    >
    >Brian Hartin wrote:
    >> All,
    >>
    >> I'm not sure whether I need to call close() every time I execute the

    >query for
    >> a PreparedStatement, like I would normally do for a Statement. When

    >using a connection
    >> from the pool, and when I have room in the statement cache, does calling

    >close()
    >> simply put the statement back into the cache for that connection?

    >
    >Hi, no it doesn't. Closing a prepared statement that has been cached
    >involves cleaning it
    >up so it is in a known default state for it's next use:
    >
    > public static void cleanUpStatementForReUse(PreparedStatement p)
    > {
    > try { p.clearParameters(); p.clearBatch(); } catch (Throwable ignore)
    >{}
    >
    > try { p.setEscapeProcessing(true); } catch (Throwable ignore) {}
    >
    > try
    > {
    > if (p.getFetchDirection() != ResultSet.FETCH_FORWARD)
    > p.setFetchDirection(ResultSet.FETCH_FORWARD);
    > } catch (Throwable ignore) {}
    >
    > try
    > {
    > if (p.getFetchSize() != 0)
    > p.setFetchSize(0);
    > } catch (Throwable ignore) {}
    >
    > try
    > {
    > if (p.getMaxFieldSize() != 0)
    > p.setMaxFieldSize(0);
    > }
    > catch (Throwable ignore) {}
    >
    > try
    > {
    > if (p.getMaxRows() != 0)
    > p.setMaxRows(0);
    > }
    > catch (Throwable ignore) {}
    >
    > try
    > {
    > if (p.getQueryTimeout() != 0)
    > p.setQueryTimeout(0);
    > }
    > catch (Throwable ignore) {}
    >
    > try
    > {
    > p.clearWarnings();
    > }
    > catch (Throwable ignore) {}
    > }
    >
    >If you have some code that may need to re-use a given PreparedStatement
    >within a single
    >user invocation, it would be best to retain and re-use the statement
    >until it is not
    >needed for the current uppermost method, at which time it should be closed.
    >(ie: like your tests 2 and 5). I believe if you time the specific calls
    >within
    >a test, you will find that the statement.close() (from a pooled connection)
    >will take
    >the extra time. It's clear that the pooled test does much better with
    >a cached
    >prepared statement than with plain statements.
    > I would be very interested to see your results if the tests were
    >run in the
    >container, such as in a JSP etc. This way the pooled access would not
    >have to go
    >through any more OS processes than the direct driver tests.
    >thanks
    >Joe
    >
    > My tests don't
    >> seem to indicate this:
    >>
    >> All tests use 100 iterations of a simple query.
    >> All test close the ResultSet after each query.
    >> All tests obtain a single connection before beginning the test and

    >close it afterwards.
    >> All tests were executed out of the container, which I believe accounts

    >for the
    >> approximately ten-fold increase in time for the pooled connection tests.
    >>
    >> Test 1: Statements on a regular Connection
    >> Time: 962 ms
    >> Open cursors left by this query for the connection = 0
    >>
    >> Test 2: PreparedStatements on a Connection preparing and closing the

    >PreparedStatement
    >> only once
    >> Time: 430 ms
    >> Open cursors left by this query for the connection = 0
    >>
    >> Test 3: PreparedStatements on a Connection preparing and closing the

    >PreparedStatement
    >> each iteration
    >> Time: 931 ms
    >> Open cursors left by this query for the connection = 0
    >>
    >>
    >> Test 4: Statements on a pooled Connection
    >> Time: 7310 ms
    >> Open cursors left by this query for the connection = 0
    >>
    >> Test 5: PreparedStatements on a pooled Connection preparing and closing

    >the PreparedStatement
    >> only once
    >> Time: 4747 ms
    >> Open cursors left by this query for the connection = 1
    >>
    >> Test 6: PreparedStatements on a pooled Connection preparing and closing

    >the PreparedStatement
    >> each iteration
    >> Time: 7050 ms
    >> Open cursors left by this query for the connection = 1
    >>
    >> I think the last two leave a cursor open because the statement is left

    >in the
    >> cache after calling close(). However, if this is so, shouldn't I have

    >seen a
    >> dramatic (relative) decrease in the time spent 'preparing' the statement

    >in test
    >> 6? In other words, shouldn't test 6 have obtained the statement from

    >the cache
    >> each time it called prepare(), yielding a total execution time similar

    >to test
    >> 4?
    >>
    >> Thanks,
    >>
    >> Brian

    >



  4. Re: PreparedStatement.close() behavior question (WL 8.1)


    It looks like my attempt at using HTML line break tags just muddied things up.
    Here's another try:

    Test 4: Statements on a pooled Connection

    Time (in container): 671 ms

    Time (out of container): 7310 ms

    Open cursors left by this query for the connection = 0



    Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    only once

    Time (in container): 282 ms

    Time (out of container): 4747 ms

    Open cursors left by this query for the connection = 1



    Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    each iteration

    Time (in container): 297 ms

    Time (out of container): 7050 ms

    Open cursors left by this query for the connection = 1

    "Brian Hartin" wrote:
    >
    >Joe:
    >
    >The important thing that I want to confirm is that calling close() on
    >a PreparedStatement
    >in-container _does_ put it back in the cache, and that the benefits of
    >using a
    >PreparedStatement are _not_ lost by calling close(). I re-performed
    >the pooled
    >connection tests in the container. The following tests seem to confirm
    >that this
    >is true:
    >
    >(I've added HTML tags to hopefully preserve the line breaks I lost last
    >time)
    >
    >Test 4: Statements on a pooled Connection

    >Time (in container): 671 ms

    >Time (out of container): 7310 ms

    >Open cursors left by this query for the connection = 0

    >

    >Test 5: PreparedStatements on a pooled Connection preparing and closing
    >the PreparedStatement
    >only once

    >Time (in container): 282 ms

    >Time (out of container): 4747 ms

    >Open cursors left by this query for the connection = 1

    >

    >Test 6: PreparedStatements on a pooled Connection preparing and closing
    >the PreparedStatement
    >each iteration

    >Time (in container): 297 ms

    >Time (out of container): 7050 ms

    >Open cursors left by this query for the connection = 1

    >

    >
    >As expected, the pooled connection tests perform much better in container.
    > It
    >seems to me that the fact that the results of tests 5 and 6 are nearly
    >identical
    >when run in-container confirms my hope that the prepareStatement() and
    >close()
    >calls are intercepted. Is this your conclusion?
    >
    >Thanks for your help,
    >
    >Brian
    >
    >
    >
    >Joe Weinstein wrote:
    >>
    >>
    >>Brian Hartin wrote:
    >>> All,
    >>>
    >>> I'm not sure whether I need to call close() every time I execute the

    >>query for
    >>> a PreparedStatement, like I would normally do for a Statement. When

    >>using a connection
    >>> from the pool, and when I have room in the statement cache, does calling

    >>close()
    >>> simply put the statement back into the cache for that connection?

    >>
    >>Hi, no it doesn't. Closing a prepared statement that has been cached
    >>involves cleaning it
    >>up so it is in a known default state for it's next use:
    >>
    >> public static void cleanUpStatementForReUse(PreparedStatement p)
    >> {
    >> try { p.clearParameters(); p.clearBatch(); } catch (Throwable ignore)
    >>{}
    >>
    >> try { p.setEscapeProcessing(true); } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getFetchDirection() != ResultSet.FETCH_FORWARD)
    >> p.setFetchDirection(ResultSet.FETCH_FORWARD);
    >> } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getFetchSize() != 0)
    >> p.setFetchSize(0);
    >> } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getMaxFieldSize() != 0)
    >> p.setMaxFieldSize(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getMaxRows() != 0)
    >> p.setMaxRows(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getQueryTimeout() != 0)
    >> p.setQueryTimeout(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> p.clearWarnings();
    >> }
    >> catch (Throwable ignore) {}
    >> }
    >>
    >>If you have some code that may need to re-use a given PreparedStatement
    >>within a single
    >>user invocation, it would be best to retain and re-use the statement
    >>until it is not
    >>needed for the current uppermost method, at which time it should be

    >closed.
    >>(ie: like your tests 2 and 5). I believe if you time the specific calls
    >>within
    >>a test, you will find that the statement.close() (from a pooled connection)
    >>will take
    >>the extra time. It's clear that the pooled test does much better with
    >>a cached
    >>prepared statement than with plain statements.
    >> I would be very interested to see your results if the tests were
    >>run in the
    >>container, such as in a JSP etc. This way the pooled access would not
    >>have to go
    >>through any more OS processes than the direct driver tests.
    >>thanks
    >>Joe
    >>
    >> My tests don't
    >>> seem to indicate this:
    >>>
    >>> All tests use 100 iterations of a simple query.
    >>> All test close the ResultSet after each query.
    >>> All tests obtain a single connection before beginning the test and

    >>close it afterwards.
    >>> All tests were executed out of the container, which I believe accounts

    >>for the
    >>> approximately ten-fold increase in time for the pooled connection

    >tests.
    >>>
    >>> Test 1: Statements on a regular Connection
    >>> Time: 962 ms
    >>> Open cursors left by this query for the connection = 0
    >>>
    >>> Test 2: PreparedStatements on a Connection preparing and closing the

    >>PreparedStatement
    >>> only once
    >>> Time: 430 ms
    >>> Open cursors left by this query for the connection = 0
    >>>
    >>> Test 3: PreparedStatements on a Connection preparing and closing the

    >>PreparedStatement
    >>> each iteration
    >>> Time: 931 ms
    >>> Open cursors left by this query for the connection = 0
    >>>
    >>>
    >>> Test 4: Statements on a pooled Connection
    >>> Time: 7310 ms
    >>> Open cursors left by this query for the connection = 0
    >>>
    >>> Test 5: PreparedStatements on a pooled Connection preparing and closing

    >>the PreparedStatement
    >>> only once
    >>> Time: 4747 ms
    >>> Open cursors left by this query for the connection = 1
    >>>
    >>> Test 6: PreparedStatements on a pooled Connection preparing and closing

    >>the PreparedStatement
    >>> each iteration
    >>> Time: 7050 ms
    >>> Open cursors left by this query for the connection = 1
    >>>
    >>> I think the last two leave a cursor open because the statement is

    >left
    >>in the
    >>> cache after calling close(). However, if this is so, shouldn't I

    >have
    >>seen a
    >>> dramatic (relative) decrease in the time spent 'preparing' the statement

    >>in test
    >>> 6? In other words, shouldn't test 6 have obtained the statement from

    >>the cache
    >>> each time it called prepare(), yielding a total execution time similar

    >>to test
    >>> 4?
    >>>
    >>> Thanks,
    >>>
    >>> Brian

    >>

    >



  5. Re: PreparedStatement.close() behavior question (WL 8.1)



    Brian Hartin wrote:

    > Joe:
    >
    > The important thing that I want to confirm is that calling close() on a PreparedStatement
    > in-container _does_ put it back in the cache, and that the benefits of using a
    > PreparedStatement are _not_ lost by calling close().


    It depends on the version of the server. 7.0 and before have a fixed cache (per connection)
    which fills with the first N prepared statements made though that connection. These statements
    become the fixed population. When a user of the connection prepares the same SQL as for a
    cached, unused statement, it gets the cached one to use and close() puts it back. After the
    cache is filled, any request for a prepared statement that is not in cache gets a fresh statement
    and closing that statement is a real close, and it goes away.
    For 8.1, the cache has an LRU functionality, so any prepared statement not already in cache
    will go into the cache, ejecting the least-recently-used one, which is closed.
    Joe
    I re-performed the pooled
    > connection tests in the container. The following tests seem to confirm that this
    > is true:
    >
    > (I've added HTML tags to hopefully preserve the line breaks I lost last time)
    >
    > Test 4: Statements on a pooled Connection

    > Time (in container): 671 ms

    > Time (out of container): 7310 ms

    > Open cursors left by this query for the connection = 0

    >

    > Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > only once

    > Time (in container): 282 ms

    > Time (out of container): 4747 ms

    > Open cursors left by this query for the connection = 1

    >

    > Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > each iteration

    > Time (in container): 297 ms

    > Time (out of container): 7050 ms

    > Open cursors left by this query for the connection = 1

    >

    >
    > As expected, the pooled connection tests perform much better in container. It
    > seems to me that the fact that the results of tests 5 and 6 are nearly identical
    > when run in-container confirms my hope that the prepareStatement() and close()
    > calls are intercepted. Is this your conclusion?


    Oh, yes, prepareStatement() and prepareCall() and close() and *all* jdbc calls
    are intercepted by our wrapper objects. What we do during the intercept depends
    on what call it was.


    > Thanks for your help,
    >
    > Brian
    >
    >
    >
    > Joe Weinstein wrote:
    >
    >>
    >>Brian Hartin wrote:
    >>
    >>>All,
    >>>
    >>>I'm not sure whether I need to call close() every time I execute the

    >>
    >>query for
    >>
    >>>a PreparedStatement, like I would normally do for a Statement. When

    >>
    >>using a connection
    >>
    >>>from the pool, and when I have room in the statement cache, does calling

    >>
    >>close()
    >>
    >>>simply put the statement back into the cache for that connection?

    >>
    >>Hi, no it doesn't. Closing a prepared statement that has been cached
    >>involves cleaning it
    >>up so it is in a known default state for it's next use:
    >>
    >> public static void cleanUpStatementForReUse(PreparedStatement p)
    >> {
    >> try { p.clearParameters(); p.clearBatch(); } catch (Throwable ignore)
    >>{}
    >>
    >> try { p.setEscapeProcessing(true); } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getFetchDirection() != ResultSet.FETCH_FORWARD)
    >> p.setFetchDirection(ResultSet.FETCH_FORWARD);
    >> } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getFetchSize() != 0)
    >> p.setFetchSize(0);
    >> } catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getMaxFieldSize() != 0)
    >> p.setMaxFieldSize(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getMaxRows() != 0)
    >> p.setMaxRows(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> if (p.getQueryTimeout() != 0)
    >> p.setQueryTimeout(0);
    >> }
    >> catch (Throwable ignore) {}
    >>
    >> try
    >> {
    >> p.clearWarnings();
    >> }
    >> catch (Throwable ignore) {}
    >> }
    >>
    >>If you have some code that may need to re-use a given PreparedStatement
    >>within a single
    >>user invocation, it would be best to retain and re-use the statement
    >>until it is not
    >>needed for the current uppermost method, at which time it should be closed.
    >>(ie: like your tests 2 and 5). I believe if you time the specific calls
    >>within
    >>a test, you will find that the statement.close() (from a pooled connection)
    >>will take
    >>the extra time. It's clear that the pooled test does much better with
    >>a cached
    >>prepared statement than with plain statements.
    >> I would be very interested to see your results if the tests were
    >>run in the
    >>container, such as in a JSP etc. This way the pooled access would not
    >>have to go
    >>through any more OS processes than the direct driver tests.
    >>thanks
    >>Joe
    >>
    >> My tests don't
    >>
    >>>seem to indicate this:
    >>>
    >>>All tests use 100 iterations of a simple query.
    >>>All test close the ResultSet after each query.
    >>>All tests obtain a single connection before beginning the test and

    >>
    >>close it afterwards.
    >>
    >>>All tests were executed out of the container, which I believe accounts

    >>
    >>for the
    >>
    >>>approximately ten-fold increase in time for the pooled connection tests.
    >>>
    >>>Test 1: Statements on a regular Connection
    >>>Time: 962 ms
    >>>Open cursors left by this query for the connection = 0
    >>>
    >>>Test 2: PreparedStatements on a Connection preparing and closing the

    >>
    >>PreparedStatement
    >>
    >>>only once
    >>>Time: 430 ms
    >>>Open cursors left by this query for the connection = 0
    >>>
    >>>Test 3: PreparedStatements on a Connection preparing and closing the

    >>
    >>PreparedStatement
    >>
    >>>each iteration
    >>>Time: 931 ms
    >>>Open cursors left by this query for the connection = 0
    >>>
    >>>
    >>>Test 4: Statements on a pooled Connection
    >>>Time: 7310 ms
    >>>Open cursors left by this query for the connection = 0
    >>>
    >>>Test 5: PreparedStatements on a pooled Connection preparing and closing

    >>
    >>the PreparedStatement
    >>
    >>>only once
    >>>Time: 4747 ms
    >>>Open cursors left by this query for the connection = 1
    >>>
    >>>Test 6: PreparedStatements on a pooled Connection preparing and closing

    >>
    >>the PreparedStatement
    >>
    >>>each iteration
    >>>Time: 7050 ms
    >>>Open cursors left by this query for the connection = 1
    >>>
    >>>I think the last two leave a cursor open because the statement is left

    >>
    >>in the
    >>
    >>>cache after calling close(). However, if this is so, shouldn't I have

    >>
    >>seen a
    >>
    >>>dramatic (relative) decrease in the time spent 'preparing' the statement

    >>
    >>in test
    >>
    >>>6? In other words, shouldn't test 6 have obtained the statement from

    >>
    >>the cache
    >>
    >>>each time it called prepare(), yielding a total execution time similar

    >>
    >>to test
    >>
    >>>4?
    >>>
    >>>Thanks,
    >>>
    >>>Brian

    >>

    >



  6. Re: PreparedStatement.close() behavior question (WL 8.1)


    Brian Hartin wrote:

    > It looks like my attempt at using HTML line break tags just muddied things up.


    Those seem consistent (or at least heedable as advice) to me...

    > Here's another try:
    >
    > Test 4: Statements on a pooled Connection
    >
    > Time (in container): 671 ms
    >
    > Time (out of container): 7310 ms
    >
    > Open cursors left by this query for the connection = 0
    >
    >
    >
    > Test 5: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > only once
    >
    > Time (in container): 282 ms
    >
    > Time (out of container): 4747 ms
    >
    > Open cursors left by this query for the connection = 1
    >
    >
    >
    > Test 6: PreparedStatements on a pooled Connection preparing and closing the PreparedStatement
    > each iteration
    >
    > Time (in container): 297 ms
    >
    > Time (out of container): 7050 ms
    >
    > Open cursors left by this query for the connection = 1
    >
    > "Brian Hartin" wrote:
    >
    >>Joe:
    >>
    >>The important thing that I want to confirm is that calling close() on
    >>a PreparedStatement
    >>in-container _does_ put it back in the cache, and that the benefits of
    >>using a
    >>PreparedStatement are _not_ lost by calling close(). I re-performed
    >>the pooled
    >>connection tests in the container. The following tests seem to confirm
    >>that this
    >>is true:
    >>
    >>(I've added HTML tags to hopefully preserve the line breaks I lost last
    >>time)
    >>
    >>Test 4: Statements on a pooled Connection

    >>Time (in container): 671 ms

    >>Time (out of container): 7310 ms

    >>Open cursors left by this query for the connection = 0

    >>

    >>Test 5: PreparedStatements on a pooled Connection preparing and closing
    >>the PreparedStatement
    >>only once

    >>Time (in container): 282 ms

    >>Time (out of container): 4747 ms

    >>Open cursors left by this query for the connection = 1

    >>

    >>Test 6: PreparedStatements on a pooled Connection preparing and closing
    >>the PreparedStatement
    >>each iteration

    >>Time (in container): 297 ms

    >>Time (out of container): 7050 ms

    >>Open cursors left by this query for the connection = 1

    >>

    >>
    >>As expected, the pooled connection tests perform much better in container.
    >>It
    >>seems to me that the fact that the results of tests 5 and 6 are nearly
    >>identical
    >>when run in-container confirms my hope that the prepareStatement() and
    >>close()
    >>calls are intercepted. Is this your conclusion?
    >>
    >>Thanks for your help,
    >>
    >>Brian
    >>
    >>
    >>
    >>Joe Weinstein wrote:
    >>
    >>>
    >>>Brian Hartin wrote:
    >>>
    >>>>All,
    >>>>
    >>>>I'm not sure whether I need to call close() every time I execute the
    >>>
    >>>query for
    >>>
    >>>>a PreparedStatement, like I would normally do for a Statement. When
    >>>
    >>>using a connection
    >>>
    >>>>from the pool, and when I have room in the statement cache, does calling
    >>>
    >>>close()
    >>>
    >>>>simply put the statement back into the cache for that connection?
    >>>
    >>>Hi, no it doesn't. Closing a prepared statement that has been cached
    >>>involves cleaning it
    >>>up so it is in a known default state for it's next use:
    >>>
    >>> public static void cleanUpStatementForReUse(PreparedStatement p)
    >>> {
    >>> try { p.clearParameters(); p.clearBatch(); } catch (Throwable ignore)
    >>>{}
    >>>
    >>> try { p.setEscapeProcessing(true); } catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> if (p.getFetchDirection() != ResultSet.FETCH_FORWARD)
    >>> p.setFetchDirection(ResultSet.FETCH_FORWARD);
    >>> } catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> if (p.getFetchSize() != 0)
    >>> p.setFetchSize(0);
    >>> } catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> if (p.getMaxFieldSize() != 0)
    >>> p.setMaxFieldSize(0);
    >>> }
    >>> catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> if (p.getMaxRows() != 0)
    >>> p.setMaxRows(0);
    >>> }
    >>> catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> if (p.getQueryTimeout() != 0)
    >>> p.setQueryTimeout(0);
    >>> }
    >>> catch (Throwable ignore) {}
    >>>
    >>> try
    >>> {
    >>> p.clearWarnings();
    >>> }
    >>> catch (Throwable ignore) {}
    >>> }
    >>>
    >>>If you have some code that may need to re-use a given PreparedStatement
    >>>within a single
    >>>user invocation, it would be best to retain and re-use the statement
    >>>until it is not
    >>>needed for the current uppermost method, at which time it should be

    >>
    >>closed.
    >>
    >>>(ie: like your tests 2 and 5). I believe if you time the specific calls
    >>>within
    >>>a test, you will find that the statement.close() (from a pooled connection)
    >>>will take
    >>>the extra time. It's clear that the pooled test does much better with
    >>>a cached
    >>>prepared statement than with plain statements.
    >>> I would be very interested to see your results if the tests were
    >>>run in the
    >>>container, such as in a JSP etc. This way the pooled access would not
    >>>have to go
    >>>through any more OS processes than the direct driver tests.
    >>>thanks
    >>>Joe
    >>>
    >>> My tests don't
    >>>
    >>>>seem to indicate this:
    >>>>
    >>>>All tests use 100 iterations of a simple query.
    >>>>All test close the ResultSet after each query.
    >>>>All tests obtain a single connection before beginning the test and
    >>>
    >>>close it afterwards.
    >>>
    >>>>All tests were executed out of the container, which I believe accounts
    >>>
    >>>for the
    >>>
    >>>>approximately ten-fold increase in time for the pooled connection

    >>
    >>tests.
    >>
    >>>>Test 1: Statements on a regular Connection
    >>>>Time: 962 ms
    >>>>Open cursors left by this query for the connection = 0
    >>>>
    >>>>Test 2: PreparedStatements on a Connection preparing and closing the
    >>>
    >>>PreparedStatement
    >>>
    >>>>only once
    >>>>Time: 430 ms
    >>>>Open cursors left by this query for the connection = 0
    >>>>
    >>>>Test 3: PreparedStatements on a Connection preparing and closing the
    >>>
    >>>PreparedStatement
    >>>
    >>>>each iteration
    >>>>Time: 931 ms
    >>>>Open cursors left by this query for the connection = 0
    >>>>
    >>>>
    >>>>Test 4: Statements on a pooled Connection
    >>>>Time: 7310 ms
    >>>>Open cursors left by this query for the connection = 0
    >>>>
    >>>>Test 5: PreparedStatements on a pooled Connection preparing and closing
    >>>
    >>>the PreparedStatement
    >>>
    >>>>only once
    >>>>Time: 4747 ms
    >>>>Open cursors left by this query for the connection = 1
    >>>>
    >>>>Test 6: PreparedStatements on a pooled Connection preparing and closing
    >>>
    >>>the PreparedStatement
    >>>
    >>>>each iteration
    >>>>Time: 7050 ms
    >>>>Open cursors left by this query for the connection = 1
    >>>>
    >>>>I think the last two leave a cursor open because the statement is

    >>
    >>left
    >>
    >>>in the
    >>>
    >>>>cache after calling close(). However, if this is so, shouldn't I

    >>
    >>have
    >>
    >>>seen a
    >>>
    >>>>dramatic (relative) decrease in the time spent 'preparing' the statement
    >>>
    >>>in test
    >>>
    >>>>6? In other words, shouldn't test 6 have obtained the statement from
    >>>
    >>>the cache
    >>>
    >>>>each time it called prepare(), yielding a total execution time similar
    >>>
    >>>to test
    >>>
    >>>>4?
    >>>>
    >>>>Thanks,
    >>>>
    >>>>Brian
    >>>

    >



+ Reply to Thread