0 Replies Latest reply on May 23, 2005 12:57 PM by demarco

    customize read-ahead strategy doing a finder in one db call

      Hi together,

      i know this is an old problem and is discussed in this
      forum in different context; but i didn't find a
      solution for my scenario.... and here it is:

      I'm using a table about 30 columns and over 150.000 entries.
      Now i want to execute a finder findAll that executes ony 1 db call.
      For this i try a lot of optimizations with the read-ahead strategy,
      but it executes always more than one query.
      First the select-all query and then a "or"-concatinated query with
      100 entries (because of my page-size=100 settings in standardjbosscmp-jdbc.xml).
      Is there a possibility to avoid the additional selects?
      One of these queries has a duration of about 5 sec.

      Here my last try; code snippet and environment:
      - JBOSS 3.2.5 width SAPDB and JDK1.4.2
      - CommitOption A for CMP-Entities
      - Using a stateless session bean with transaction required to
      execute finder and iterations in one transaction.
      Thanks help.


      Code of my session bean.

      ....
      InitialContext jndiContext = new InitialContext();
      Object ref = jndiContext.lookup(TestCMPLocalHome.COMP_NAME);
      TestCMPLocalHome home = (TestCMPLocalHome)ref;
      Collection c = home.findAll();
      Iterator it = c.iterator();
      while(it.hasNext())
      {
       TestCMPLocal loc = (TestCMPLocal)it.next();
       System.out.println( loc.getCol1() );
       System.out.println( loc.getCol2() );
      }
      ...
      


      My XDoclet configuration.
      /**
       * @ejb.bean name="TestCMP"
       * display-name="TestCMPBean"
       * description="TestCMP"
       * jndi-name="ejb/TestCMP"
       * type="CMP"
       * cmp-version="2.x"
       * view-type="both"
       * @ejb.transaction
       * type="Required"
       *
       * @jboss.unknown-pk
       * class="java.lang.Integer"
       * column-name="keyID"
       * jdbc-type="INTEGER"
       * sql-type="INTEGER"
       *
       * @jboss.entity-command
       * name="pk-sql"
       *
       * @jboss.entity-command-attribute
       * name="pk-sql"
       * value="SELECT seqkeyID.nextval AS maxID FROM dual"
       *
       * @jboss.persistence
       * create-table="false"
       *
       * @ejb.finder
       * signature="java.util.Collection findAll()"
       * query="SELECT OBJECT(l) FROM TESTTABLE l"
       * view-type="both"
       * description="gets all entities"
       * unchecked="true"
       *
       * @jboss.load-group
       * name="subset"
       * description="some fields used in query"
       *
       * @jboss.read-ahead
       * strategy = "on-find"
       * eager-load-group = "subset"
       *
       * */
      


      Executed queries.
      18:34:01,455 DEBUG [TestCMP#findAll] Executing SQL: SELECT t0_l.keyID,t0_l.col1, t0_l.col2 FROM TESTTABLE t0_l
      
      18:35:46,548 DEBUG [TestCMP] Executing SQL: SELECT keyID, col1, col2
      FROM TESTTABLE WHERE (keyid=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR
      (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)
      .......
      18:35:46,548 DEBUG [TestCMP] Executing SQL: SELECT keyID, col1, col2
      FROM TESTTABLE WHERE (keyid=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR
      (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)
      .......
      18:35:46,548 DEBUG [TestCMP] Executing SQL: SELECT keyID, col1, col2
      FROM TESTTABLE WHERE (keyid=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR
      (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)
      .......
      18:35:46,548 DEBUG [TestCMP] Executing SQL: SELECT keyID, col1, col2
      FROM TESTTABLE WHERE (keyid=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?) OR
      (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)OR (keyID=?) OR (keyID=?) OR (keyID=?) OR (keyID=?)
      .......