JDBC Query vs JPA Query Performance

Hibernate ORM
1

i am having some issues related with performance while reading thousands of records from the database. I noticed that a pure JDBC query is much more faster that a JPA Native query.

Here is the query

select ID, COL_A, COL_B, COL_C, COL_D, COL_E, COL_F from MY_SUPER_VIEW_V v 
where 1=1 
and v.ID in (:idList)
and v.DATE_FROM <= :date
and v.DATE_TILL >= :date;

The in idList has more than 1000 records and because I am using an Oracle DB it needs to be split in n queries. This query returns more than 37K records from the DB

Further i have a method that coverts the Object[] result to my List.

In order to understand the performance issue i created a pure JDBC query and a JPA Native query respectivly to compare the results.

Here are the timings.

################ getScoresPureJDBCWithListIds ################
List of Ids retrieved. It took: 00:00:00.096 to execute query on DB using JDBC
It took: 00:00:01.180 to execute query on DB using JDBC query
Creating 24206 Scores records from DB result It took: 00:00:04.440
It took: 00:00:01.038 to execute query on DB using JDBC query
Creating 14445 Scores records from DB result It took: 00:00:04.307
################ getScoresJPANativeQueryWithListIds ################
It took: 00:06:09.450 to execute query on DB using JPA Native query
Creating 24206 Scores records from DB result It took: 00:00:00.009
It took: 00:04:04.879 to execute query on DB using JPA Native query
Creating 14445 Scores records from DB result It took: 00:00:00.007

For the JDBC query i can see 1) that executing the query is quite fast, but 2) processing each ResultSet element in a loop takes the most of the time 00:09 seconds int total

On the other for the JPA Native query 1) executing the query by calling the query.getResultList() method takes a lot of time 10:14 seconds ont the other hand 2) processing each result is quite faste here.

Why JPA Native is quite slow when compare with pure JDBC? Would it be the type conversions? In my case i am talking about varchar2 and numbers. I was expecting identical results to JDBC. but from 8 seconds to 10mins its a lot.

In my spring boot application.properties i set the fetch_size like this:

spring.jpa.properties.hibernate.jdbc.fetch_size=500

I used to have this not set, and then i tried 20 and 500. I did not see any difference.

What can i do to improve the JPA Native query?

Thank you so much for your help.

Best Regards

1 Like
2

This is not really a use-case Hibernate excels at. I would stay with JDBC and possibly try out these options:

  • use an Oracle array for the in-list
  • if possible do not load all rows into memory, stream them if possible
  • chose an appropriate fetch size

As you are using a view DTOs (and DTO projection) likely makes more sense than entities since you can’t update them anyway.

1 Like
3

The main difference between JPA getResultList() (and its Hibernate implementation) and the conversion of Object[] returned by JDBC into a List by your own code is memory management.

When doing the conversion after getting all objects from JDBC, you know the size of the Array to allocate… while Hibernate Loader class does a

final List results = new ArrayList();
...
// loop on results...
				results.add( result );
...

which is of course much less efficient.