APPLICATION and ENVIRONMENT
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.
I am using MySQL as database, with MyISAM engine on all tables. JPA Provider is EclipseLink with cache set to ALL
. FetchType.EAGER
is used at relationships.
AFTER RUNNING NETBEANS PROFILER
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 Question
table.
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;
}
}
The Question
entity:
@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
}
There are four entities extending Question
.
The column azonosito
should be used as primary key, but I don't see this as the main reason for low performance.
I am interested in suggestions for optimization. Feel free to ask if you need more information!
EDIT See my answer summarizing the best results
Thanks in advance!