This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Mar 28, 2013 9:02 AM by Joe Weinstein RSS

Value time of a prepared statement and execution plan differences

800405 Newbie
Currently Being Moderated
Hi Guys..... I have a doubt here that I have not been able to clarify searching over the internet.

Is about the prepared statements......

When I do: con.prepareStatement(query);

then the query is compiled and an execution plan is made for that query on the database, I know it. But what happens when I do con.close. Is the cached statement still accesible for further executions??? lets do this example:

Lets assume we are joining 20 tables and we have about 10 parameters to that query
public ArrayList getData(String id1, String id2, String id 10) {

       ArrayList response=new ArrayList();
       Connection con=manager.getConnection();
       String query="select name, phone, debtvalue, currency, etc from table1, table2, table3, table4, table20 where
                           table1.id=table2.t1id and table2.id=table3.id and table2.fid=? and table1.fid=? and etc etc";//a big query
       PreparedStatement pst=con.prepareStatement("select");
       pst.setString (1, id1);
       pst.setString (2, id2);
       pst.setString (3, id3);
       .
       .
       pst.setString (10, id10);

       ResultSet rs=pst.executeQuery();
       while (rs.next()) {
              //do whatever and build the response ArrayList
       }
       rs.close();
       pst.close();
       return response;

}
Will that prepared statement compilation and that execution plan be avaible for each execution of the getData method. Or a new compilation and execution plan is calculated each time I execute the method??? Assuming the query being executed takes a lot of time due it its complexity and is executed about 100 times a day in a production enviroment. Is it recommendable to use the prepared statement????

Is the precompiled stuff avaible If I am using a """NEW""" declared PreparedStatement with the same query???

Thanks a lot in advance for your responses......

Edited by: user4789473 on 25-mar-2013 17:14
  • 1. Re: Value time of a prepared statement and execution plan differences
    rp0428 Guru
    Currently Being Moderated
    >
    Is about the prepared statements......

    When I do: con.prepareStatement(query);

    then the query is compiled and an execution plan is made for that query on the database, I know it.
    >
    You 'know it'? Post evidence that supports that statement. Have you prepared a statement and verified that the database has compiled it or is even aware that the query exists?

    Have you tried using an invalid query to see if you get an error? You would expect an error if the database tried to compile an invalid query wouldn't you? Try it and see what happens: conn.prepareStatement(" give me everything from emp ");

    Whether precompilation happens when the statement is created or only the first time the statement is executed depends on the JDBC driver being used and whether it supports pre-compilation.

    See the Java API for 'java.sql.Connection'
    http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html
    >
    prepareStatement
    PreparedStatement prepareStatement(String sql)
    throws SQLExceptionCreates a PreparedStatement object for sending parameterized SQL statements to the database.
    A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

    Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

    Result sets created using the returned PreparedStatement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY. The holdability of the created result sets can be determined by calling getHoldability().
    >
    Drivers MAY support precompilation.
    >
    Will that prepared statement compilation and that execution plan be avaible for each execution of the getData method. Or a new compilation and execution plan is calculated each time I execute the method??? Assuming the query being executed takes a lot of time due it its complexity and is executed about 100 times a day in a production enviroment. Is it recommendable to use the prepared statement????

    Is the precompiled stuff avaible If I am using a """NEW""" declared PreparedStatement with the same query???
    >
    What happens in Vegas - stays in Vegas.

    What happens in the database stays in the database. For Oracle whether the query has been precompiled by the database or is not sent until the PreparedStatement is executed when the Oracle DB hard parses the query and creates an execution plan that plan is stored in the library cache in the database.

    Once the plan is stored in the library cache it is available for use/re-use by any session; Oracle has NO knowledge and does not care if that session originates from Java or not.

    Execution plans can, and do, get aged out of the SQL cache (still talking Oracle) to make room for other queries. So at any later time that execution plan may, or may not, still be in the cache. If it is not in the cache then another hard parse will occur and that new plan will get cached.
  • 2. Re: Value time of a prepared statement and execution plan differences
    800405 Newbie
    Currently Being Moderated
    Well, I am afraid I am still doubthfull about this...... I know that this cahes are in the database, but, how can the database or the JDBC driver know that it must use that stored cache, is the object that referenced to it no longer exists, I mean...... A second call to that method will create another object with another query(the same content but another one).... wouldnt the jdbc create another similar precompilation for it??? Does the execution plan differs when doing a prepared or an statement???
  • 3. Re: Value time of a prepared statement and execution plan differences
    rp0428 Guru
    Currently Being Moderated
    >
    Well, I am afraid I am still doubthfull about this...... I know that this cahes are in the database, but, how can the database or the JDBC driver know that it must use that stored cache, is the object that referenced to it no longer exists, I mean.
    >
    The database has, and uses the cache. Java and JDBC have NOTHING to do with it.
    >
    ..... A second call to that method will create another object with another query(the same content but another one)
    >
    A second call to what method?
    >
    .... wouldnt the jdbc create another similar precompilation for it??? Does the execution plan differs when doing a prepared or an statement???
    >
    Reread my original reply.

    The database, not JDBC, parses the query, saves it in the library cache and creates the execution plan. 'Precompilation', if the JDBC driver supports it is just the process of sending the query to the database at the time the prepared statement is created which is BEFORE it is executed.

    The preparation of the prepared statement is NOT precompilation. That preparation creates a statement that can accept bind variables that might be used in the statement so that the same statement can be reused with different data values.
  • 4. Re: Value time of a prepared statement and execution plan differences
    800405 Newbie
    Currently Being Moderated
    ok, then... the database stores and uses that cache for executing those queries, not the JDBC, I understand it. But my question is.... how can the database decide or know when to use that cache..... how can the database distinguish between a preparedStatement and a non prepared statement with for 2 identical queries if not through anyway on which the JDBC can refer to that specific prepared statement.

    Reading my original post, I put some code on there... executing several times the "getData" method will create several times the preparedStatement and execute that preparedStatement once per method call..... In this situation... will the database uses this pre cached execution for all the time that the getData method is executed??? becausse the database already had a precached execution for that query and doesnt matter if the prepared statement is created over and oaver again?????
  • 5. Re: Value time of a prepared statement and execution plan differences
    gimbal2 Guru
    Currently Being Moderated
    user4789473 wrote:
    ok, then... the database stores and uses that cache for executing those queries, not the JDBC, I understand it. But my question is.... how can the database decide or know when to use that cache..... how can the database distinguish between a preparedStatement and a non prepared statement with for 2 identical queries if not through anyway on which the JDBC can refer to that specific prepared statement.
    Statement and PreparedStatement are Java things, the DBMS doesn't know or need to know what they are. All it cares about is queries. PreparedStatement is simply a convience class to securely "click" together a query with parameters and take away the risk of SQL injection.

    It isn't productive to ask "how does the DBMS do it" questions here anyway. You'd have to ask the people who built the DBMS.
  • 6. Re: Value time of a prepared statement and execution plan differences
    Joe Weinstein Expert
    Currently Being Moderated
    The fact that you use a new connection each time means the DBMS has to parse the SQL
    every time you run your code, even if only to find whether there is a matching query plan,
    so (and you could test this easily) for your case (unless you are using a connection pooling
    system, that also caches PreparedStatements) it might be faster to use a plain statement.
    And better yet, make and call a stored procedure which does your SQL.
  • 7. Re: Value time of a prepared statement and execution plan differences
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Assuming the query being executed takes a lot of time due it its complexity and is executed about _100 times a day_ in a production enviroment. Is it recommendable to use the prepared statement????
    Given those assumptions I doubt that sql caching whether it occurrs or not will have any measurable impact on processing time or system performance.
  • 8. Re: Value time of a prepared statement and execution plan differences
    800405 Newbie
    Currently Being Moderated
    Yes I know that in this case it wont affect the overall time. I am trying to understand the whole stuff about using a preparedStatement or trying to understand when its not recommendable using a preparedStatement. I am not really worried about SQL injection....
  • 9. Re: Value time of a prepared statement and execution plan differences
    Joe Weinstein Expert
    Currently Being Moderated
    It's least likely to help using a PreparedStatement when you don't re-use it. Making a new connection, and making
    the prepared statement, using it, and then closing the connection is an example of it not being a help, unless you
    are using an intelligent connection pooling system that also caches statements. And depending on how the data is
    indexed in the DBMS, and how volatile the volume and distribution of the data is, the query plan created and stored
    at one time may be inappropriate/bad for re-use when the data profile has changed. One simple example is if a query
    plan is created when a table is only 3 pages of data, it may correctly ignore all indexes, and just do a table scan. When
    the table grows to 1000000 pages, that plan may be the worst thing to do. So sometimes a fresh query compile is
    a wise investment.
  • 10. Re: Value time of a prepared statement and execution plan differences
    rp0428 Guru
    Currently Being Moderated
    >
    Yes I know that in this case it wont affect the overall time. I am trying to understand the whole stuff about using a preparedStatement or trying to understand when its not recommendable using a preparedStatement. I am not really worried about SQL injection....
    >
    The main value gained from using prepared statements is when bind variables are used and then primarily in conjunction with batch processing to set those bind variables and send 'batches' to the server.

    That allows the query to be sent one time with a set of data (the data bound to the bind variables). The server process then executes the one statement multiple times using the set of bind data that was sent.

    If you are not using batch processing you will likely see little performance benefit from using prepared statements rather that just statements.
  • 11. Re: Value time of a prepared statement and execution plan differences
    Joe Weinstein Expert
    Currently Being Moderated
    The primary benefit of a prepared statement is to avoid the DBMS
    reparsing SQL. The initial call has to send the SQL to the DBMS, with
    a request to prepare it for re-use. The DBMS returns a cursor ID, or
    temp stored procedure ID etc. Then for the life of the session at least,
    the client can send a shorter message:

    exec <that cursor/stored procedure> [with these arguments]

    The DBMS just plugs and plays. There is no logical distinction between
    using the 'batch' API and simply re-executing the prepared statement
    in a loop, and in some cases there may be no driver-internal difference
    either.
  • 12. Re: Value time of a prepared statement and execution plan differences
    800405 Newbie
    Currently Being Moderated
    The initial call has to send the SQL to the DBMS, with
    a request to prepare it for re-use. The DBMS returns a cursor ID, or
    temp stored procedure ID etc.....

    This means that when you send a query, the dbms returns to the driver an ID in order to use that precached execution???? this means that the driver must keep a reference to that ID in order to use it???
  • 13. Re: Value time of a prepared statement and execution plan differences
    Joe Weinstein Expert
    Currently Being Moderated
    Yep. It's part of the internal state of the PreparedStatement object.
  • 14. Re: Value time of a prepared statement and execution plan differences
    800405 Newbie
    Currently Being Moderated
    This means that if I lose the reference to the preparedStatement, then tha ID will be lost too?? and re prepare/create another one with the same query string..... it wont use the same pre cached, but will prepare a new one???
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points