I forgot the attachments
I might add as well that I've tested with both commit option A and B and that I tested to set get-methods read-only as suggested by the optimization article at onjava.com but no major changes in performance could be noticed.
At this point I'm stuck, I can't continue with CMP if this is the performance I have to expect.
hi - i dont have time to read your code - but simply to say that the time diff is so extreme - it can only really be that the SQL the CMP is generating is 1 query per record - and perhaps not in a transaction (although I note you have ths covered).
CMP without read-ahead tuning has issues - either record by record loading or huge SQL statements with perhaps and overflowing amount if ID's to match.
If you set the logging options as in the cmp doc then you can see the SQL being exectuted and then you will see why the time del;ay is so exteme.
for datasets of unknows or several thousand - you must use tuning of the queries, so that the data is loaded on-find (IMHO)
stuart (hope this helps)
Hi Stuart and thank you for your answer.
I've been running with the mysql logging all queries and this is what I get there:
030624 11:08:09 9 Query SET autocommit=0
9 Query SELECT DISTINCT t0_b.Id FROM billingevent t0
_b, account t1_b_account WHERE (t1_b_account.Id = 8 AND t0_b.CreateTime > '2003-
06-22 23:59:00' AND t0_b.CreateTime < '2003-06-24 23:24:00') AND (t0_b.Account_F
030624 11:08:12 9 Query SELECT Id,Amount, Success, Committed, Rollba
ck, BillingType, MessageType, Reason, Message, CreateTime, MessageId, Country_FK
, Account_FK, Carrier_FK, MessageEvent_FK FROM billingevent WHERE (Id=102950) OR
(Id=102951) OR (Id=102952) OR (Id=102953) OR (Id=102954) OR (Id=102955) OR (Id=
102956) OR (Id=102957) OR (Id=102958) OR (Id=102959) OR (Id=102960) OR (Id=10296
1) OR (Id=102962) OR (Id=102963) OR (Id=102964) OR (Id=102965) OR (Id=102966) OR
(Id=102967) OR (Id=102968) OR (Id=102969) OR (Id=102970) OR (Id=102971) OR (Id=
102972) OR (Id=102973) OR (Id=102974) OR (Id=102975) OR (Id=102976) OR (Id=10297
7) OR (Id=102978) OR (Id=102979) OR (Id=102980) OR (Id=102981) OR (Id=102982) OR
(Id=102983) OR (Id=102984) OR (Id=102985) OR (Id=102986) OR (Id=102987) OR (Id=
102988) OR (Id=102989) OR (Id=102990) OR (Id=102991) OR (Id=102992) OR (Id=10299
3) and so on
As you can see by the first SET autocommit=0 this is done. What is done next is fetching the ID's and then the data in a 1000 window.
I just tested to run the same query in the mysql command tool and it completed in 0.06 seconds. Looking at the log it looks like most of the time is spent inbetween these queries but I'll do as you suggested and use the jboss loging instead.
Yep - it all looks normal. The building and batching of the long query strings is very slow and you can eliminate these by using an eager-load group and an on-find read-ahead policy - that way you can load ALL of the objects in one simple select statement instead of 2 (one to fnid ids and one-batched to pull the other fields).
It's easy to try ... just copy the gangster example for defining an eager load group. it should be fast then.
The "gotcha" is when your eager load lods other objects (as you do here - I see CMR fields) which are also eager loaded - as the result is loads of objects you did not dream of using yet get loaded too - so try jsut one or two in your application!!
Sorry for my stupid comment:
Doing it "right" could mean, you get the entire database (table + related tables) into the memory of the application server :)
I'm writing on an EJB implementation of an application which is currently written in PHP and uses Oracle's OCI interface. There are some really big tables (biggest has more then 2 million entires, 2nd trouble maker grows about 10.000 - 60.000 entires per day).
Once a week a lot of statistics are run from PHP, which simply accesses all rows at least once!
Does this mean we need really much memory for the application server to hold enough object to have a good performance? (Okay, the Oracle Cluster has 8 GB)
BTW: Yes, writing it in PL/SQL would definitly improove performance like hell, but the app should someday be portable between several vendors. That's why I wanted to try to get it done with EJB and (according to several articles on the web) one of the fastest application servers....