2

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!

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
Daniel Szalay
  • 4,041
  • 12
  • 57
  • 103

4 Answers4

3

Using LAZY is a good start, I would recommend you always make everything LAZY if you are at all concerned about performance.

Also ensure that you are using weaving, (Java SE agent, or Java EE/Spring, or static), as LAZY OneToOne and ManyToOne depend on this.

Changing the Id to your other field would be a good idea, if you always query on it and it is unique. You should also check why your application keeps executing the same query over and over.

You should make the query a NameDQuery not use a dynamic query. In EclipseLink you could also enable the query cache on the query (once it is a named query), this will enable cache hits on the query result.

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
James
  • 17,965
  • 11
  • 91
  • 146
  • It's not really the same query executing over and over. I mean it's retrieving one instance from 680 entities. These entities are needed for (lots of) calculations, and the results are shown on (lots of) diagrams in the end. What I did is: 1. wrote a `@NamedQuery` for `Question`, 2. enabled shared cache in `persistence.xml`, 3. added the hint `@QueryHint(name = "javax.persistence.cache.storeMode", value = "REFRESH")` to the `@NamedQuery`; however not exactly sure if the last one is needed, but as a result these steps improved performance. Execution times reduces the procedure is repeated. – Daniel Szalay Feb 08 '11 at 23:51
  • I set the `javax.persistence.cache.storeMode` to `USE` instead of `REFRESH`, since these entities don't change. Also set `javax.persistence.cache.retrieveMode` to `USE`. Performance++, Thanks! :) Link: http://en.wikibooks.org/wiki/Java_Persistence/Caching – Daniel Szalay Feb 09 '11 at 00:06
  • Also added EclipseLink query caching hinz `@QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.TRUE)` as suggested; very good results. – Daniel Szalay Feb 09 '11 at 10:42
  • @DanielSzalay, can you share details related to your comment above, 2. enabled shared cache in persistence.xml ? – Howard Nov 28 '12 at 19:15
  • @Howard Unfortunately I don't have that `persistence.xml` anymore. I think this is what i inserted in the `` tag: ``. And if I remember correctly, I added the `@QueryHint` to the entity class which was desired to be cached. – Daniel Szalay Nov 29 '12 at 12:56
1

Have you got unique index on the azonosito column in your database. Maybe that will help. I would also suggest to fetch only the fields you really need so maybe some of then could be lazy i.e. Category.

Marcin Michalski
  • 1,266
  • 13
  • 17
  • Make sure the index actually exists on your table, maybe the unique annotation was added after the table was created? – Sam Barnum Feb 04 '11 at 18:55
  • I've added it manually too, and also tried `FULLTEXT` indexing. Neither improved performance noticeably. – Daniel Szalay Feb 04 '11 at 19:02
  • I've changed `fetch=FetchType.EAGER` to `fetch=FetchType.LAZY` at every relationship that had it set. Performance is magnitudes better: ~80s -> ~14s. Actually there are some other entity classes that contain lists of `Question`s. – Daniel Szalay Feb 04 '11 at 19:42
  • Just to mention. I will try and use ObjectDB to improve application performance, since there could be much bigger requests. – Daniel Szalay Feb 04 '11 at 19:49
1

Since changing fetch type of relationship to LAZY dramatically improved performance of your application, perhaps you don't have an index for foreign key of that relationship. If so, you need to create it.

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • I have tried this, and it did not improve performance so much. So I guess it is the design of entity classes making `FetchType.EAGER` very expensive. Still this is a great suggestion, since this can improve performance further with `FetchType.LAZY` set. Thanks! – Daniel Szalay Feb 05 '11 at 10:31
0

In this answer I will summarize what was the best solution for that particular query.

First of all, I set azonosito column as primary key, and modified my entities accordingly. This is necessary because EclipseLink object cache works with em.find:

public Question getQuestionByAzon(String azon) {
    try {
        return em.find(Question.class, azon);
    } catch (NoResultException e) {
        return null;
    }
}

Now, instead of using a QUERY_RESULT_CACHE on a @NamedQuery, I configured the Question entity like this:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Cache(size=1000, type=CacheType.FULL)
public abstract class Question implements Serializable { ... }

This means an object cache of maximum size 1000 will be maintained of all Question entities.

Profiler Results ~16000 invocations

QUERY_RESULT_CACHE: ~28000ms

@Cache(size=1000, type=CacheType.FULL): ~7500ms

Of course execution time gets shorter after the first execution.

Daniel Szalay
  • 4,041
  • 12
  • 57
  • 103
  • Interesting. How did you verify QUERY_RESULT_CACHE ~28000ms ? I'm new to Java Visual VM (jvisualvm) – Howard Nov 28 '12 at 20:32
  • @Howard If I remember correctly I attached a profiler to my application, and so I was able to see the method execution times. Then I just compared the execution times of the different cache settings. – Daniel Szalay Nov 29 '12 at 12:49