6 Replies Latest reply: Jul 11, 2013 12:23 PM by SandeepChulupunur RSS

    Regarding prepared statement behavior

    SandeepChulupunur

      Hi All,

       

      My query is regarding prepared statement.

       

      say if i am executing a select or prepared statement using prepared statement it would compile only once and subsequent requests would directly go for execution rather than for compilation again (this is my understanding).

       

      in case of below scenario how would it behave ? each time when preparedstatement object is created would it compile ?

       

      // method in one class A

      public void testMethod("",10,""){

      }

      // method in class B

      public void testMethod(String query, int cols, String country) throws Exception{

        Connection conn = null;

        PreparedStatement stmt = null;

        ResultSet resultset = null;

              try{ 

         conn = getConn(); 

         stmt = conn.prepareCall(query);

         p.setInt(1, 10);

         p.execute();

               } catch(Exception e){

                      e.printStackTrace();

               } finally{

                      resultset.close();

             stmt.close();

             conn.close();

                }

      }

       

       

      Thanks.

        • 1. Re: Regarding prepared statement behavior
          masijade

          A new preparedstatement will be prepared in the db every time you create a new preparedstatement java object.  Create the object once, and once only.  As in outside the method and pass it to the method (please do not make it an instance variable).

          • 2. Re: Regarding prepared statement behavior
            SandeepChulupunur

            Hi masijade,

             

             

            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 ?


            Thanks.

            • 3. Re: Regarding prepared statement behavior
              masijade

              "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.


              • 4. Re: Regarding prepared statement behavior
                Joe Weinstein-Oracle

                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.

                • 5. Re: Regarding prepared statement behavior
                  SandeepChulupunur

                  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.

                  • 6. Re: Regarding prepared statement behavior
                    Joe Weinstein-Oracle

                    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...