Description Mike Seminaro 2010-06-24 17:32:50 EDT

Please note observations obtained by experiments below:

Observation 1, query all contacts using JPA

           long startTime = System.currentTimeMillis();
           Query query = em.createQuery("SELECT c FROM Contact c");
           query.setHint(QueryHints.QUERY_REDIRECTOR, new IgnoreDefaultRedirector());
           //query.setHint(QueryHints.JDBC_FETCH_SIZE, 500);

           List<Contact> contacts = (List<Contact>)query.getResultList();

           for(Contact c : contacts)
           {
           }
           System.out.println("Execute query took " + (System.currentTimeMillis() - startTime) + " ms");
           System.out.println("contacts.size()="+contacts.size());

Output:
Execute query took 31362 ms
contacts.size()=205

Observation 2, query all contacts using JDBC

-- Code sample omitted to improve readability --

Output:
Execute query took 222 ms
Full logic took 10735 ms

Observation 3, query all contacts using JPA - uncommented query.setHint(QueryHints.JDBC_FETCH_SIZE, 500);

Output:
Execute query took 26454 ms
contacts.size()=205

Observation 4, query all contacts using JDBC - uncommented s.setFetchSize(500);

Output:
Execute query took 459 ms
Full logic took 592 ms

Conclusion:

- JDBC responded well to fetch size setting, execution time went down from 10735 ms to 592 ms (an 18x improvement)

- On the other hand, EclipseLink JPA did not respond as well to QueryHints.JDBC_FETCH_SIZE 31362 ms vs. 26454 ms. The difference is not as significant and can probably be eliminated if we use the average by repeating this experiment by a sufficient number of times.

Is EclipseLink ignoring QueryHints.JDBC_FETCH_SIZE? It seems like the value is never set on the JDBC statement that is being executed within EL (I used the debugger to verify this).