2

I have a (oracle)table with about 5 million records and a quite complex query which returns about 5000 records of it in less than 5 seconds with a database tool like toad. However when I ran the query via entityManager(eclipseLink) the query runs for minutes...

I'm probably too naive in the implementation. I do:

Query query = em.createNativeQuery(complexQueryString, Myspecific.class); ... setParameter... List result = query.getResultList();

The complexQueryString starts with a "SELECT *".

What kinds of optimization do I have? May be one is only to select the fields I really need later. Some explanation would be great.

Thomas Seehofchen
  • 367
  • 1
  • 5
  • 14
  • native queries in JPA have limited sense without deep database knowledge. Detailed anser impossible without Myspecific class and query – Jacek Cz Aug 25 '17 at 15:31

2 Answers2

0

I had a similar problem (I tried to read 800000 records with 8 columns in less than one second) and the best solution was to fall back to jdbc. The ResultSet was created and read really 10 times faster than using JPA, even when doing a native query.

How to use jdbc: normally in the J2EE-Servers a JDBC-DataSource can be injected as @Resource.

An explanation: I think the OR-Mappers try to create and cache objects so that changes can easily detected later. This is a very substantial overhead, that can't be recognized if you are just working with single entities.

aschoerk
  • 3,333
  • 2
  • 15
  • 29
  • agree, specially in this part: use JDBC if developer feels control in low level, or use JPA if higher logic level is expected. For me the worst is mix JPA + extensive coding in native queries. Has downsides of both – Jacek Cz Aug 26 '17 at 15:01
  • BTW query that correctly does computing/filtering on SQL server side, with good designed database (indexes etc), doesn't create too much java objects on client side – Jacek Cz Aug 26 '17 at 15:04
  • @JacekCz I agree, but sometimes the queries get too complicated. My Usecase was a quite complilcated job-selection algorithm already using CTEs. – aschoerk Aug 26 '17 at 16:44
0

Query.setFetchSize(...) may help a bit. It tells the jdbc driver how many rows to return in one chunk. Just call it before getResultList();

query.setFetchSize(5000);
query.getResultList();
ThanhLoyal
  • 393
  • 3
  • 11