title: JPA EclipseLink 2 query performance
body: <p><strong>APPLICATION and ENVIRONMENT</strong></p>
  
  <p>Java EE / JSF2.0 / JPA enterprise application, which contains a web and an EJB module. I am generating PDF documents which contains evaluated data queried via JPA.</p>
  
  <p>I am using MySQL as database, with MyISAM engine on all tables. JPA Provider is EclipseLink with cache set to <code>ALL</code>. <code>FetchType.EAGER</code> is used at relationships.</p>
  
  <p><strong>AFTER RUNNING NETBEANS PROFILER</strong></p>
  
  <p>Profiler results show that the following method is called the most. In this session it was 3858 invocations, with ~80 seconds from request to response. This takes up 80% of CPU time. There are 680 entries in the <code>Question</code> table.</p>
  
  <pre><code>public Question getQuestionByAzon(String azon) {
      try {
          return (Question) em.createQuery("SELECT q FROM Question q WHERE q.azonosito=:a").setParameter("a", azon).getSingleResult();
      } catch (NoResultException e) {
          return null;
      }
  }
  </code></pre>
  
  <p>The <code>Question</code> entity:</p>
  
  <pre><code>@Entity
  @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
  public abstract class Question implements Serializable {
  
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      @Column(unique = true)
      private String azonosito;
      @Column(nullable = false)
      @Basic(optional = false)
      private String label;
      @Lob
      @Column(columnDefinition = "TEXT")
      private String help;
      private int quizNumber;
      private String type;
      @ManyToOne
      private Category parentQuestion;
  
      ...
  
      //getters and setters, equals() and hashCode() function implementations
  
  }
  </code></pre>
  
  <p>There are four entities extending <code>Question</code>. </p>
  
  <p>The column <code>azonosito</code> should be used as primary key, but I don't see this as the main reason for low performance.</p>
  
  <p>I am interested in suggestions for optimization. Feel free to ask if you need more information!</p>
  
  <p><strong>EDIT</strong> See my answer summarizing the best results</p>
  
  <p>Thanks in advance!</p>