JPA select creates many requests to db, performance

Question

I'm new to JPA and I wrote simple JPA selects for my web service. I was using classic SQL statements before. It was great, I knew how to get solid speed, but I'm working with 3 different databases in 5 different projects now. So I thought that learning JPA was a good way to use one technology instead of having to know 3 SQL languages. So I created some tables, entities and tried to use JPA. But something was wrong in the first moment - speed.

(I'm using MSSQL 2008)

I compared simple native SQL select and JPA select :

em.createNativeQuery("SELECT c.* FROM Table c", MyTable.class);

and

em.createQuery("SELECT c FROM Table c", MyTable.class)

createNativeQuery was 5x quicker, but why?

So I started SQL profiler included in MSSQL package and debugged what JPA was doing. I have a table with 100 records and what I was seeing surprised me:

createNativeQuery creates 1 request to db and result was 100 records in query

createQuery creates 100 requests and every request returns 1 record. Something like this:

1 SELECT Col1, Col2 FROM TABLE WHERE (ID = ?)<br>
2 SELECT Col1, Col2 FROM TABLE WHERE (ID = ?)<br>
.
.
.
100 SELECT Col1, Col2 FROM TABLE WHERE (ID = ?)

I'm using JPA 2 with Eclipse Link. Is this normal behavior for how JPA works? Or have I done something wrong in my settings.

You haven't mentioned when those queries are occuring. I'd recomemnd turning EclipseLink logging on to see the SQL it issues and when as described here: http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging and try to figure out when those selects are being issued. My bet is you have lazy attributes that your application is accessing as it iterates over the results, causing the fetch group to be to be triggered one by one. Fetch groups are described here: http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Performance/Fetch_Groups Check the mappings for col1 + col2 — Chris, Nov 28 '12 at 13:55

score 1 · Answer 1 · answered Nov 27 '12 at 19:26

This is done to optimize the scenarios where you don't need to use all your result objects at once. EclipseLink loads the object skeletons first and when you traverse the objects, it populates them behind the scene.

If you want to load all objects at once then I think the QueryHint as below should help:

  Query query = em.createQuery("SELECT c FROM Table c", MyTable.class)
  query.setHint(QueryHints.BATCH_SIZE, 100); //set any appropriate batch size

score 0 · Answer 2 · answered Nov 27 '12 at 19:26

You should not be using createNativeQuery for a simple example as you have provided.

Try something like this (using Query instead):

    Query agedUsers = em.createQuery( "SELECT u " + "FROM Users u "
            + "WHERE u.age = :age" );
    agedUsers.setParameter( "age", age);
    List<User> users = agedUsers.getResultList();

The table name is the name of the entity, and you must use a alias instead of *.

score 0 · Answer 3 · answered Nov 28 '12 at 06:23

sorry I have little mess when let jpa use loading row by row and when use QueryHints.BATCH_SIZE. Can you explain me when is created skeleton and when are data populated?
When I return data to list :

List users = agedUsers.getResultList();

I have list mapped to primefaces dataTable, data are populated when Is dataTable rendered, or when query returns list?

When Is usefull let jpa load data row by row and when is better load data using QueryHints.BATCH_SIZE?

JPA select creates many requests to db, performance

3 Answers3