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=?)
| .......
|
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3878776#3878776
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3878776
-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
_______________________________________________
JBoss-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/jboss-user
