2 Replies Latest reply on Apr 17, 2003 5:34 PM by marc.fleury

    Prepared Statement Cache in Jboss

    rohinibt

      The J2EE server keeps a list of prepared statements as a cache 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 for THAT connection , if yes it reuses the existing stmt. Now, shouldn't there be a SINGLE prepd stmt cache for all connections in the App Server pool. Because there is low probability that two successive requests made in the same session to the same EJBcomponent will use the SAME connection from the pool- so where is the performance advantage of Prepd stmts over plain Stmts at the level of the J2EE server. The other advantage at the level of the Database of reusing query plan for the cache will however remain.
      Does JBoss offer a different behavior in this case
      Thoughts anyone?

        • 1. Re: Prepared Statement Cache in Jboss
          keeleyd

          We use JDBC prepared statements extensively under our web pages and EJBs. It is fundemental, we could not get near the performance we require with dynamic SQL (as opposed to database stored procedures for example) without them. I expect this is true of most enterprise scale database applications.

          It appears the JBoss JDBC prepared statements can not be used with a JDBC connection pool? At least they must be prepared each time a connection is taken from the pool, which renders them useless. We have browsed the source code and can see that the statement gets closed, in fact if it is not closed then JBoss closes it for you and logs a debug line saying 'do your own house keeping'. We thought perhaps that the class jboss.resource.adaptor.jdbc.WrappedPreparedStatement may override the close() method to release the statement back to the pool for example, rather than closing it. But no.

          Can anyone provide any background to our problem?
          Is there an enhancement in this area planned?

          We found some "minerva" source that does not seem to be part of the JBoss 3 picture, that looked like it supported this. Has that been retired?

          Regards
          Dave

          • 2. Re: Prepared Statement Cache in Jboss
            marc.fleury

            this is a question for the JCA forums, david takes care of that