Pooling Prepared Statements - Good Idea?

[Robert Paris]

I was just reading that a prepared statement, when created, allows the database to optomize that type of query so you can use it over and over again with less overhead on every call after the first one (i.e. creation of the statement). It also recommended pooling these so you can use them all over the application. This leads me to a few questions:
1. Has anyone tried this? Was there a real performance benefit?
2. Is this a good idea? Why or why not?
3. Does this work across all databases?
4. What about timeout? Will this cause problems with the pool?
5. Has anyone tried this with mySQL?
6. What about in JSP? Where would I save the pool? Would there be problems of concurrent access?

[Ray H.]

Rob,
I'm not that experienced on this topic but hopefully I know enough to answer at least part of your question.
Prepared statements are pre compiled so that the SQL statement itself is sent to the DB engine before its executed. So everytime you execute that statement the DB only has to perform the "execute" step of the SQL execution plan. This is much better performance although JDBC does not guarantee it. You can also parameterize your prepared statement to give it a little more flexibility.
Also, I actually posted a message a few hours ago about setting up a DataSource for connection pooling in MySQL (via WSAD) and got a very good response. I've been able to get it to work. It makes for faster performance as well as cleaner code. It also makes the connection available to the entire app, in case you wanted to establish a connection in a java bean outside of your web project.
As we all know, establishing a connection is an expensive process and connection pools allow you to incur this expense only once which is especially useful if you have many transactions that require database activity.
I would also advise you to not architect your app in such a way that the jsp would know anything about a connection, leave that up to a servlet/EJB or perhaps a java bean.
I can't really speak to the effects of timeouts etc... as this is a very complex problem in any database programming.
Hope this helps and hopefully a java guru can add on to what I say in case it is unclear.
Ray

[Robert Paris]

Thanks for the reply, I'll check out your other post. I did do a quick test (ran it on a loop of 200 updates, and ran each test 5 times) - nothing too conclusive since i did so little, BUT... I found that using mySQL (3.2.3), mysql jdbc driver, jetty (JSP) that the prepared statement version was actually a tad slower (about 3% slower) every time. In other words, no difference and NO performance gain. (I got the code straight out of O'Reilly's JDBC book on this). Maybe it doesn't work with mysql (in other words mySQL 3.2.3 doesn't optomize statements)?

[Robert Paris]

OK, I seem to have the answer:
"JavaSoft wrote PreparedStatement in order to make queries to databases that support pre-compilation of queries more efficient. MySQL does not support pre-compilation of queries, but other methods in PreparedStatement are useful."
Don't know what version that applies to (found it on MM.mySQL page), but I'm guessing all of them until I hear diff.

[Peter den Haan]

For Oracle, prepared statements really make a difference. It maintains a statement cache with compiled statements. If your statement matches a statement in the cache, you save yourself the SQL compilation and optimization phase. In one case where we did some measurements it made a 20% difference in execution time. I imagine this depends on your SQL -- how important compilation and optimization is relative to SQL execution time.
Note that this statement cache is not dependent on reuse of the actual Java PreparedStatement objects! In fact, it usually doesn't seem to make much difference for Oracle if you reuse these or not -- as long as the SQL text itself remains the same. So in the case of an Oracle database, I'd definitely use PreparedStatement but not bother to use an object pool.
- Peter

[chandana sapparapu]

Hi Peter,
I would like to know where exactly the code for prepared statement opimizations is implemented.
Is it in Application server's connection pooling mechanism or in the JDBC driver of the Database server?

If it is in the app server, can I assume that all major players are doing it?
Also, JDBC 3.0 specfies Prepared Statement pooling. This makes the case for the optimizations to be done in the database server. Am I right?

Thanks.

[Peter den Haan]

Originally posted by chandana sapparapu:
I would like to know where exactly the code for prepared statement opimizations is implemented. Is it in Application server's connection pooling mechanism or in the JDBC driver of the Database server?

It's in the JDBC driver, the database, or both. JDBC 2.0 and earlier provided PreparedStatement as a way to provide support for any execution plan caching that the database might do. JDBC 3.0 adds the possibility of pooling the PreparedStatements themselves.

In the case of Oracle, there is an SQL statement pool on the database server which essentially associates SQL text with its execution plan. This pool only works if the SQL text is exactly the same. If you embed query values in your SQL (as you would do in a Statement: SELECT NAME FROM EMP WHERE EMPNO=5), the likelihood of using a cached plan is much lower than when you would use query parameters (as you would do in a PreparedStatement: SELECT NAME FROM EMP WHERE EMPNO=?). I don't think the Oracle driver pools PreparedStatements; it probably wouldn't make a lot of difference since the hard work is done on the server anyway.

- Peter
[ August 05, 2004: Message edited by: Peter den Haan ]