6 Replies Latest reply on Aug 6, 2003 8:54 AM by Rene

    CMP takes 10++ minutes, JDBC takes 5 seconds on the same tas

    Sverker Abrahamsson Novice

      I don't know if it's me who do something compleatly wrong but I'm working on an application that uses an EJB backend with session and entity beans on JBoss 3.2.1 (also tested with 3.2.0) and MySQL 4.0.13 dbms with connector version 3.0.6.

      I am getting extreamly poor performance from the CMP beans. A simple query takes miliseconds to complete and the other day I was trying to make a routine that calculate statistics on a table. Even with a very small scope like a couple of thousand rows, it was extreamly slow and when I did a calculation on 40k rows the transaction timed out after 10 minutes.

      I turned on logging of queries to mysql and saw that there seams to be some problem with read-ahead because after a while it starts behaving as in the example in CMP doc chapter 6 that describes what happens when running without transaction, i.e. every id is fetched multiple times. Adding more memory to the jvm solved that but it's still extreamly slow even when that condition doesn't occure.

      I tested to do the same queries in mysql command tool and it fetched the data in 8 seconds where I believe most of the time were spent on printing it to the terminal.

      I then made a JDBC implementation of the same task and on the same data it completes including the calculations in about 5 seconds. That's more than a factor 100 faster!!

      How can this be?? The facade session methods are wrapped in container-managed transactions and the entity beans are generated by AndrMDA/Xdoclet. I've attached the part of the source that does this calculation, the session bean contains one method called createStatement which is the JDBC implementation and one method called createStatementCMP which is the CMP implementation. The source with xdoclet-tags for the entity beans are included as well as the datasource configuration (note that the JDBC uses the same datasource).

      Is it me who do something wrong, is there a missmatch between JBoss and MySQL or is the answer simply that CMP is this slow and that I should use hibernate or JDO instead?

        • 2. Re: CMP takes 10++ minutes, JDBC takes 5 seconds on the same
          Sverker Abrahamsson Novice

          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.

          • 3. Re: CMP takes 10++ minutes, JDBC takes 5 seconds on the same
            stuart evans Newbie

            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)

            • 4. Re: CMP takes 10++ minutes, JDBC takes 5 seconds on the same
              Sverker Abrahamsson Novice

              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
              K=t1_b_account.Id)
              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.

              Thanks

              • 5. Re: CMP takes 10++ minutes, JDBC takes 5 seconds on the same
                stuart evans Newbie

                Hi.

                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!!

                stuart.

                • 6. Re: CMP takes 10++ minutes, JDBC takes 5 seconds on the same
                  Rene Newbie

                  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....