1 Reply Latest reply on Sep 19, 2002 3:54 PM by Dain Sundstrom

    CMP and Bean Cache

    mantelus Newbie

      Hi people,

      i just set up a small Bean project with JBuilder and
      took a look at the SQL statements JBOSS produces while
      communicating with a MySQL database.

      I am using a stateless session bean looking up some
      CMP entits beans and executing some getter calls on
      them. The beans are connected via a bi directional relationship.

      The code is like this, called 10 times in a loop:

      RelnHome rH = (RelnHome) i.lookup("jb7Reln");
      Reln r = rH.findByPrimaryKey(new Integer(1));
      Rel1 r1 = r.getRel1();
      Reln r2 = rH.findByPrimaryKey(new Integer(1));
      Rel1 r21 = r2.getRel1();
      Reln r3 = rH.findByPrimaryKey(new Integer(1));
      Rel1 r31 = r3.getRel1();

      System.out.println(":D " + r1.getName());
      System.out.println("D: " + r1.getName());

      The SQL statements depend on the transaction setting
      for the calling session bean.

      SQL for transaction set to "never":

      2002-09-19 15:56:59,059 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:56:59,069 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Reln] Executing SQL: SELECT value, ID_REL1, ID_REL1 FROM reln WHERE (ID_RELN=?)
      2002-09-19 15:56:59,069 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:56:59,079 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Reln] Executing SQL: SELECT value, ID_REL1, ID_REL1 FROM reln WHERE (ID_RELN=?)
      2002-09-19 15:56:59,089 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:56:59,089 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Reln] Executing SQL: SELECT value, ID_REL1, ID_REL1 FROM reln WHERE (ID_RELN=?)
      2002-09-19 15:56:59,099 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Rel1] Executing SQL: SELECT name FROM rel1 WHERE (ID_REL1=?)
      2002-09-19 15:56:59,109 INFO [STDOUT] :D VisitY
      2002-09-19 15:56:59,109 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Rel1] Executing SQL: SELECT name FROM rel1 WHERE (ID_REL1=?)
      2002-09-19 15:56:59,109 INFO [STDOUT] D: VisitY

      ==> even a simple .getName() is forwarded to the database.
      ==> SQL statements are identical in every iteration
      ==> absolutely no caching, (no bean materialization?)

      SQL for transaction set to "required":

      2002-09-19 15:44:16,623 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:44:16,633 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Reln] Executing SQL: SELECT value, ID_REL1, ID_REL1 FROM reln WHERE (ID_RELN=?)
      2002-09-19 15:44:16,633 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:44:16,643 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Reln.findByPrimaryKey] Executing SQL: SELECT ID_RELN FROM reln WHERE ID_RELN=?
      2002-09-19 15:44:16,653 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Rel1] Executing SQL: SELECT name FROM rel1 WHERE (ID_REL1=?)
      2002-09-19 15:44:16,663 INFO [STDOUT] :D VisitY
      2002-09-19 15:44:16,663 INFO [STDOUT] D: VisitY

      ==> caching seems to work locally
      ==> however, for every iteration, the SQL statements are
      once again identical
      ==> no caching across method calls (transactions?)

      Why is the caching behavour that bad and every stupid
      call routed to the database? This cannot be good for
      performance, its outstandingly bad!

      Are there any special settings i must adjust to have
      my beans cached across transactions? I could agree that
      finders go the db, though i find it stupid because the
      appserver could track what beans he already got and what
      beans he does not, aint it?


      gracefully appreciating your help!!!
      Hannes

        • 1. Re: CMP and Bean Cache
          Dain Sundstrom Master

          You are using Commit Option B (the default), which assumes that there are other users of the database, so JBoss must go back to the database, the source of truth, to get the current value.

          Running with out a transaction is a really, really, really bad idea. When you change a relationship it can easily cause changes to several rows in several tables, and if you don't have a transaction, you can easily end up with an inconsistent database.