7 Replies Latest reply on Oct 18, 2008 10:09 PM by Alexander Torstling

    Performance Problems Selecting EJB's

    Toni Beckman Novice

      Hi,


      our application handles messages between clients and partners. Each Message references a client and a partner, as well as other ejbs. A partner and a client also have associations to other ejbs via OneToMany, OneToOne and ManyToOne.


      Currently there are 5266 messages in the system. To display all messages to the user using a DataModel/Table with a simple


      entityManager.createQuery("SELECT m FROM Message m").getResulList();
      



      will execute about 6355 select statements and take about 60 seconds to execute.


      The view itself only displays message.client.name and message.partner.name.


      How can I increase the performance?


      Tim

        • 1. Re: Performance Problems Selecting EJB's
          Fernando Montaño Expert

          What about:



          entityManager.createQuery("SELECT m FROM Message m left join fetch m.client left join fetch m.partner").getResulList();
          
          



          HTH.

          • 2. Re: Performance Problems Selecting EJB's
            Toni Beckman Novice

            Thanks for the reply!


            The Message ejb actually is a subclass of EmailMessage, which itelsef references stores email addresses like this:


            @Entity
            @Name("emailMessage")
            
            public class EmailMessage extends CampaignMessage implements Serializable
            {
              @OneToOne(cascade = {CascadeType.ALL})
              EmailAddress from;
            
              @OneToMany(cascade = {CascadeType.ALL}) @JoinTable(name="emailmessage_to")
              List<EmailAddress> to = new ArrayList<EmailAddress>();
            
              @OneToMany(cascade = {CascadeType.ALL}) @JoinTable(name="emailmessage_cc")
              List<EmailAddress> cc = new ArrayList<EmailAddress>();
            
              @OneToMany(cascade = {CascadeType.ALL}) @JoinTable(name="emailmessage_bcc")
              List<EmailAddress> bcc = new ArrayList<EmailAddress>();
            
              @OneToMany(cascade = {CascadeType.ALL}) @JoinTable(name="emailmessage_replyto")
              List<EmailAddress> replyTo = new ArrayList<EmailAddress>();
            
              @OneToMany(cascade = {CascadeType.ALL})
              List<EmailHeader> emailHeaders = new ArrayList<EmailHeader>();
            
            



            The EmailAddress ejb


            @Entity
            @Name("emailAddress")
            
            public class EmailAddress implements Serializable
            {
              @Id
              @GeneratedValue(strategy = GenerationType.IDENTITY)
              long id;
            
              @Email
              String emailAddress;
              String name;
            
            



            About 95% of the selects fetch EmailAdress ejbs:


            Hibernate: select emailaddre0_.id as id703_0_, emailaddre0_.emailAddress as emailAdd2_703_0_, emailaddre0_.name as name703_0_ from EmailAddress emailaddre0_ where emailaddre0_.id=?
            


            So following your advice I tried:


            entityManager.createQuery("SELECT cm FROM EmailMessage cm left join fetch cm.to  left join fetch cm.cc ") // tried only 2 collections, because more than 2 failed too. Only one works, but that still creates all the select statements.
            



            and got the following exception


            "javax.ejb.EJBTransactionRolledbackException: org.hibernate.HibernateException: cannot simultaneously fetch multiple bags"
            



            • 3. Re: Performance Problems Selecting EJB's
              Fernando Montaño Expert

              My example will work for OneToOne and ManyToOne relationships. Your concrete example uses OneToMany, you can try with INNER JOIN FETCH. 

              • 4. Re: Performance Problems Selecting EJB's
                Fernando Montaño Expert

                Although this exception is clear enough: cannot simultaneously fetch multiple bags.

                • 5. Re: Performance Problems Selecting EJB's
                  Toni Beckman Novice

                  Do you see any other way how I structure the query in such a way, so to avoid fetching 5000 or so EmailAddress objects?

                  • 6. Re: Performance Problems Selecting EJB's
                    Marcell Newbie

                    Can you use native query?

                    • 7. Re: Performance Problems Selecting EJB's
                      Alexander Torstling Newbie

                      Just google for the multiple bags problem. You need to specify an index column or use sets instead of lists. Specifying index column is probably the best.