1 Reply Latest reply on Jun 25, 2002 11:55 PM by Dain Sundstrom

    Too many SQL SELECTs (I guess) in a CMP

    Carlos Augusto Grahl Newbie

      Hi!

      I successfully installed and configured JBOSS + PostgreSQL. I created an entity bean, which works fine...

      Then, I created a session bean that instantiates 5000 beans (I'm new to Jboss and now I'm testing performance). The code is below:

      for(int x=1; x<5000; x++) {
      Cliente cliente = home.findByPrimaryKey(new Integer(x));
      String endereco = cliente.getEndereco();
      }
      for(int x=1; x <5000; x++) {
      Cliente cliente = home.findByPrimaryKey(new Integer(x));
      String endereco = cliente.getEndereco();
      }

      On the first for statement, it tooks 16 secs to perform. And it makes 2 SELECTS on the db: one for the PrimaryKey and other for the other fields of the table. That's Ok.

      But on the second for statement (which tooks 8 secs), it makes one SELECT for the PrimaryKey on the db. Is that correct? I think that all of the beans should be on the memory now, so thats unnecessary access the db to load it..

      The beans are in-memory, since all of the fields are visible, and there is no select to reload them..

      Seems that JBoss only verifies the existence of this record on the database and, if it exists, load it from memory, not from the db...

      Is that correct?

      TIA

      Carlos Augusto Grahl

        • 1. Re: Too many SQL SELECTs (I guess) in a CMP
          Dain Sundstrom Master

          I strongly suggest you buy a copy of the JBossCMP documentation before attempting to measure performance or tune an application. There is an entire chapter on Optimized Loading.

          There are many things that can affect the performance of this code, but the most important are the transaction boundaries and commit options. These two determine when data must be reloaded from the database. What commit option are you using and what are the transaction bounds.

          BTW, you can load all of the data in one query if you use read ahead on-find. Using finders in a tight loop is a very bad idea, because the ejb specification requires a full database synchronization before a finder is executed.