Performance Problems Selecting EJB's

[toni]

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

[fernando_jmt]

What about:

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

HTH.

[toni]

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"

[fernando_jmt]

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

[fernando_jmt]

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

[toni]

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

[barbacena]

Can you use native query?

[obfuscator]

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.