customize read-ahead strategy doing a finder in one db call
demarco May 23, 2005 12:57 PMHi 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=?) .......