Hi, I produced a small document on the advantaged and disadvantages of using prepared statements and a prepared statement cache to improve the prepared statement performance. I'm working on an implementation of a prepared statement cache that affects the current multiconnection pool only at a few methods.
Gerard
Prepared Statement Cache This document describes the reasons for using prepared statements in jdbc queries and using a prepared statement cache. Advantages of prepared statements 1) Optimized access path calculation To process a query, the database performs two tasks. First it will calculate an access path to the result data, then it calculates the actual result table based on the access path. These steps will be performed for use with statements as well as for the use with prepared statements. Prepared statements however, have the advantage that these two tasks are seperated which makes it possible to reuse the results of the first task. During execution of database queries, the most time will be spent calculating the access path and only a small amount of time composing the result table. 2) Complex query parameters Database vendors often handle the use of complex data types (Date, Timestamp, Blob) in queries in a different way. The result is that when using statements for an application that supports multiple database vendors, different queries have to be defined, to use these complex data types. The JDBC interface solves this problem by introducing prepared statements. In these prepared statements, 'normal' Java objects can be used as query parameters. The vendor specific database driver handles translation to and from the database format of these java objects. The result is that code using prepared statements is much simpeler and cleaner than code using statements. 3) Consistent use of database queries in an application As mentioned before, applications can use the JDBC interface in two different ways to access database data. These options are statemens and prepared statements. To increase and stimulate simple and consistent code, it is wise to choose for one of the two options unless there are big disadvantages in using one or the other in certain cases. Disadvantages of prepared statements 1) Higher startup time The initial execution of a prepared statement is more expensive (in time) than the execution of a statement. Advantages of a prepared statement cache 1) Better performance by reuse of prepared statements In web applications, connections will most of the time be used for a rather short time. The advantage in the use of prepared statements seems to be gone for these type of applications. However, if in these applications a connection pool will be used, the connections will be returned to te pool after a request and the connection can be reused for another request. Users tend to perform the same queries while using the application, so caching the prepared statements per connection in the connection pool should lead to a performance improvement. Disadvantages of a prepared statement cache 1) Caching prepared statements uses system memory Like any cache the prepared statement cache uses system resources to store data. Mainly internal memory will be used to store the prepared statements. Further more, at the preparation of a prepared statement a check must be made to see if the prepared statement is available in the cache. This uses a small amount of processor time. To be able to control the resources that the cache uses, it is possible to make the cache size configurable using some kind of LRU mechanism. For performance reasons, it is however best to make teh size of the prepared statement cache as big as the maximum number of differenty queries that the application has multiplied by the maximum number of connections in the connection pool. References [http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html] [http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html] [http://www.theserverside.com/resources/article.jsp?l=Prepared-Statments]
