PreparedStatements are not so good for EJBs

Discussions

Performance and scalability: PreparedStatements are not so good for EJBs

  1. Hi,

    There was an article published in Resources about prepared statements in ejbs http://www.theserverside.com/resources/article.jsp?l=Prepared-Statments

    I would like to say that though I fully agree with the general concept of the article I think that usage of PreparedStatements is not the best idea for your EJBs. The reason is that (at least in weblogic) PreparedStatements are not cached as seen from this post:

    Subject: Re: Caching prepared Statements (again)
    From: Joseph Weinstein <joe at weblogic dot com>
    Date: Thu, 20 Jan 2000 12:20:08 -0800
    Newsgroups: weblogic.developer.interest.jdbc

    Hi. We're not going to allow you to cache statements for EJBs if
    1 - you use the jts driver, or
    2 - you use the pool driver and you do not also cache pool connections.

    If you use a straight JDBC driver, you can do what you want, of course.
    We won't let you cache statements because anything done through a jts
    statement, even a read, will affect the transactional state in the
    DBMS (locks etc), and will only see data that is coherent with the
    transaction ongoing on the connection. If the tx rolls back, some read
    data may be invalid etc. Also, we don't want any JDBC done outside the
    context of the JTS transaction (after the commit) to be mistakenly assumed
    to apply to the tx for which the connection was obtained. In fact, at
    tx rollback or commit, we will wrest all JDBC objects from you, as you've
    seen.
      If you use the pool driver, you can do what you want until you close
    the connection, returning it to the pool. At that time we'll close any
    outstanding objects so you can't affect the next user of the connection.
      If *you* are willing to take full transactional responsibility for
    commits, rollbacks, read locks, and DBMS integrity, you can use the
    pool driver or a straight JDBC driver, but then the EJB/JTS system can't
    help you except to call beforeCommit() etc, to warn you to do what you
    have to do...
    Joe


    And since they are not cached there is no benefit of them because they take more time to create and they take more time to execute (all because they are *not* cached and created again every time in your ejbXXX method).

    I would like to hear your thoughts.

  2. Ok, article didn't directly mention EJB, so this post should be considered as (possible) clarification of the idea of this nice article.
  3. I think PreparedStatement's caching was added in 5.1sp9 (or 8).
  4. Hi Dmitri. I profiled weblogic prepared statements in Dec 2000, which was before sp8. And performance of prepared statements was really bad compared to regular statements (cause they were created on every usage). I profiled my system with wl6.0 and Oracle now and I have to admit prepared statements are really cached even for *different* tx. Here is how I tested it. In ejbStore I included a call to doStatementCheck() method which has both prepared and regular statements. Both work with identical tables (A and B).

    My unit tester runner implemented as EJB has NotSupported tx for all methods. All other ejbs have Required. So all calls to ejb from a unit test (event from a loop in a test method) will run in different tx.

    First I placed 10 create-and-change calls to my ejbs from in order to allow system to warm-up and fully intialize. Then I put JProbe trigger which cleared performance data and started performance recording on enty into second test method which placed 5 create-and-change calls to my ejbs. On completion trigger took a snapshot. Here are the results (I hope formatting will be preserved):

    Calls Line Time Cumulative Time protected void doStatementCheck() {
     5 0 ( 1.7%) 0 ( 0.0%) Connection c = null;
     5 0 ( 1.6%) 0 ( 0.0%) Statement s = null;
     5 0 ( 1.7%) 0 ( 0.0%) PreparedStatement ps = null;
     5 0 ( 1.7%) 0 ( 0.0%) ResultSet rs = null;
     5 0 ( 1.7%) 0 ( 0.0%) ResultSet rsFromPrepared = null;
                                                    try {
                                               //get connection from TX DataSource
     5 0 ( 4.7%) 2 ( 1.3%) c = JDBCUtil.getConnection();
     5 0 ( 7.2%) 8 ( 6.2%) s = c.createStatement();
     5 0 ( 7.1%) 2 ( 1.6%) ps = c.prepareStatement("select idB from B where name = ?");
     5 0 ( 8.7%) 68 ( 55.7%) rs = s.executeQuery("select idA from A where name = 'roman'");
     5 0 ( 8.6%) 1 ( 0.9%) ps.setString(1, "roman");
     5 0 ( 8.6%) 28 ( 22.9%) rsFromPrepared = ps.executeQuery();
                                                    } catch (SQLException e) {
                                                      logError("", e);
                                                      throw new EJBException(e.toString());
                                                    } finally {
                                                      try {
     5 0 ( 9.5%) 5 ( 4.2%) rs.close();
     5 0 ( 7.0%) 3 ( 2.7%) rsFromPrepared.close();
     5 0 ( 8.1%) 5 ( 3.8%) s.close();
     5 0 ( 6.0%) 1 ( 0.5%) ps.close();
     5 0 ( 14.0%) 0 ( 0.1%) c.close();
                                                      } catch (SQLException e2) {
                                                        logError("", e2);
                                                        throw new EJBException(e2.toString());
                                                      }
                                                    }
     5 0 ( 2.2%) 0 ( 0.0%) }


    As seen, total time to create, set and call PreparedStatement is 2+1+28=31 compared to 76=8+68 for regular statement.

    I also tested for the same TX (by making my unit tester runner methods have Required tx attibute) and results were the same.

    Resume: looks like weblogic handles PreparedStatements ok now, so Bill's article fully makes sense at least for ejbs in weblogic. What about other app servers and PreparedStatements?
  5. I'll try again:
    <code><pre>
    Calls Line Time Cumulative Time protected void doStatementCheck() {
     5 0 ( 1.7%) 0 ( 0.0%) Connection c = null;
     5 0 ( 1.6%) 0 ( 0.0%) Statement s = null;
     5 0 ( 1.7%) 0 ( 0.0%) PreparedStatement ps = null;
     5 0 ( 1.7%) 0 ( 0.0%) ResultSet rs = null;
     5 0 ( 1.7%) 0 ( 0.0%) ResultSet rsFromPrepared = null;
                                                    try {
                                               //get connection from TX DataSource
     5 0 ( 4.7%) 2 ( 1.3%) c = JDBCUtil.getConnection();
     5 0 ( 7.2%) 8 ( 6.2%) s = c.createStatement();
     5 0 ( 7.1%) 2 ( 1.6%) ps = c.prepareStatement("select idB from B where name = ?");
     5 0 ( 8.7%) 68 ( 55.7%) rs = s.executeQuery("select idA from A where name = 'roman'");
     5 0 ( 8.6%) 1 ( 0.9%) ps.setString(1, "roman");
     5 0 ( 8.6%) 28 ( 22.9%) rsFromPrepared = ps.executeQuery();
                                                    } catch (SQLException e) {
                                                      logError("", e);
                                                      throw new EJBException(e.toString());
                                                    } finally {
                                                      try {
     5 0 ( 9.5%) 5 ( 4.2%) rs.close();
     5 0 ( 7.0%) 3 ( 2.7%) rsFromPrepared.close();
     5 0 ( 8.1%) 5 ( 3.8%) s.close();
     5 0 ( 6.0%) 1 ( 0.5%) ps.close();
     5 0 ( 14.0%) 0 ( 0.1%) c.close();
                                                      } catch (SQLException e2) {
                                                        logError("", e2);
                                                        throw new EJBException(e2.toString());
                                                      }
                                                    }
     5 0 ( 2.2%) 0 ( 0.0%) }

    </code></pre>
  6. how to preserve formatting here????
  7. Here is how each line should be be read:
    Calls = 5 Line Time= 0 ( 1.7%) Cumulative Time = 0 ( 0.0%) etc.
  8. The way prepared statements works is each connection in the pool has it's own cache (in WebLogic it's size is 10). I think it is implemented this way in 5.1sp9, 6 and 6.1.

    BTW, JDBC 3.0 adds prepared statements functionality.
  9. you mean 10 is default size of connection pool or PS pool for each connection?
  10. Yup, I think 10 is hardcoded and not configurable. My quess is that the reason for this is fear of increased customer support calls.
  11. 10 is the size of prepared statements cache for each pooled connection.

  12. Hi all. Good news, at least for WebLogic and EJBs
    and PreparedStatements. As of our 5.1 sp10 release,
    or the latest 6.0 sp2 release, we have built in
    automatic, transparent caching of PreparedStatements.
    These are associated (necessarily) with the connections
    they come from. These initial releases cache up to 10
    statements per connection, filled first-come-first-cached.
    This showed significant performance benefit for the current
    EJB customers who've tried it, and some who have more
    complex applications, and who have received a
    100-statement-per-connection cache from me, have also
    gotten significant gain. The very next releases will
    allow a configurable cache size, including zero, for
    those JDBC drivers which demonstrate bugs when statements
    are 'severely re-used'. Sybase's last-but-one driver is
    an example. They fixed it in the latest.
    Joe
  13. Another detail: Statements are actually cached in one of two
    ways, depending on whether the DBMS driver is fully
    XA-compliant and whether the pool knows it. Because XA
    allows a connection (and it's statements) to be used
    serially on behalf of any number of concurrently running
    transactions, the required period of exclusive use of a
    given XA connection/statement is much less, so the pooling
    and statement caching caching for these is implemented to
    a finer granularity, with different code in WebLogic than
    for connections used via the older non-XA-aware APIs.
    Joe