Thanks for the reply, forgot to mention that the code is part of a web project and deployed in jboss. I came to know that we have something called as prepared statement cache. Below is an excerpt from the The Server Side site about Prepared statement cache.
J2EE PreparedStatement Cache is implemented using a cache inside the J2EE server connection pool manager. The J2EE server keeps a list of prepared statements for each database connection in the pool. When an application calls prepareStatement on a connection, the application server checks if that statement was previously prepared. If it was, the PreparedStatement object will be in the cache and this will be returned to the application. If not, the call is passed to the jdbc driver and the query/preparedstatement object is added in that connections cache.
So i believe even though if i make the following call
stmt = conn.prepareCall(query);
it would return the existing prepared statement object for that query.
Any thoughts on this ? Also do i need to enable implicit statement cache on connection object to make use of prepared statement cache provided by jboss or not required ?
"a list of prepared statements for each database connection in the pool"
I can about guarantee that your "getConn()" is not returning the same connection every time. Also, a cache usually only holds so much, so by the time you call it again it may already have been removed. Also, about the "implicit statement cache", I would say to read the documentation.
If there's a statement cache, it will be a cache per connection, so whenever you re-use a given connection, it should re-use the previously prepared statement, as long as the cache is big enough to retain all the statements you will make, else it might have to flush some out to make room for new ones, and if there are more statements used in a series than the cache can hold, the cache might be worse than useless.
Hi JoeWeinstein, Thanks for the reply. Do i need to enable implicit caching on connection object or the prepared statement caching feature provided by my jboss handles it ? Is there any way to find out the cache size for prepared statement or any means of debugging to identify its really being cached.
As long as there is any one statement caching implementation involved, you'll get the benefit. Tuning info will be cache implementation specific. There is usually zero penalty in setting the cache size bigger than the number needed to hold all your prepared statements, so go big and see. Depending on the driver, there may be memory issues with caching large numbers of statements, so watch your memory too. Driver-level statement caching may be more economical with memory than higher-level statement caching, and depending on the implementation and features of higher level statement caching, performance may be better with higher-level caching. Logically, the higher up you re-use objects, the less reconstructing, rewrapping and/or lower-level cache manipulation will be needed...