Re: SV: [JBoss-user] Extremely slow transaction

[Dain Sundstrom]

This is described in the JBossCMP documentation. You need a transaction around your for loop. You can start a transaction by using a SessionBean or programmatically with a UserTransaction.

-dain

Bekkum, Odd Rune wrote:

Hi!
I'm working on the same project as Jon.

Digging into it a bit more I've found the following:
If we do a findAll on a table containing 252 rows we get 252 SQL calls,
the first one containg 252 ors, the next one 251, then 250.. down to 1.
So from what I understand JBoss first gets all the unique ids, then doing
all the or selects with one less or each time. To run this query takes a over 3 minutes.

Have we set up something wrong or is due to the translation from ejb-ql to
JBoss SQL.

Any help appreciated.

Regards
Odd-Rune Bekkum


-----Opprinnelig melding-----
Fra: Sacha Labourey [mailto:Sacha.Labourey@;ml.cogito-info.ch] Sendt: 11. oktober 2002 09:57
Til: [EMAIL PROTECTED]
Emne: RE: [JBoss-user] Extremely slow transaction


Hello Jon,

for (id=0;id++;id<1000) {
// Start transaction
Collection c=findByStatus(id,status);
for(i=c.iterator();i.hasNext();) {
  ((EntityLocal) (i.next())) . setStatus(newStatus);
}
// End transaction
}

From the log/server.log we see that each findByStatus generates a HUGE select statement with around 300 ORs ( SELECT id FROM VPKRITERIE WHERE (id=?) OR (id=?) OR (id=?) OR (id=?) OR (id=?) OR (id=?) OR
(id=?) ............

OK, but when you do the "Collection c=findByStatus....", the collection c
that you receives contains at least 300 items, right? Temporarly, let's not
speak about the generated statement in itself, but let's focus on what you
are asking to JBoss.

Can you first confirm that the size of the SQL statement is related to the
size of your collection (or smaller). I mean, can you confirm that the
result of findByStatus is not a collection of 2 items and that the SQL
statement includes 300 ORs.

We have tried to play around with conf/standardjbosscmp-jdbc.xml and standardjaws.xml, but to no help. The parameteres set is:

the best way will be to play, finder by finder, in the jbosscmp-jdbc.xml of
your application once we've understood what is really happening. Playing
with standardjaws.xml is useless unless your using JAWS instead of JBossCMP
(depends on the DocType declaration of ejb-jar.xml)

Cheers,

			Sacha

-------------------------------------------------------
This sf.net email is sponsored by: Influence the future of Java(TM) technology. Join the Java Community Process(SM) (JCP(SM)) program now. http://ads.sourceforge.net/cgi-bin/redirect.pl?sunm0004en
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user