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.

Ok, article didn't directly mention EJB, so this post should be considered as (possible) clarification of the idea of this nice article.

I think PreparedStatement's caching was added in 5.1sp9 (or 8).

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?

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>

how to preserve formatting here????

Here is how each line should be be read:
Calls = 5 Line Time= 0 ( 1.7%) Cumulative Time = 0 ( 0.0%) etc.

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.

you mean 10 is default size of connection pool or PS pool for each connection?

Yup, I think 10 is hardcoded and not configurable. My quess is that the reason for this is fear of increased customer support calls.

10 is the size of prepared statements cache for each pooled connection.

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

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