CODEWITHSUNDEEP
springperformancejpaslowdown
Johny19
Johny19

Reputation: 5582

JPA/Hibernate 50% slower than using simple JDBC

First I would like to say that I'm far of being an expert with JPA and Hibernate. But I came across something that I'm completely stuck as I don't understand how things are working underneath and don't know how to debug it.

I have a webapp (Spring + JPA2.0). I have an algorithm that calculate a score for a user with data coming from the PostrgeSQL database. To benchmark the speed of the algorithm I have ran the calculation on 1 user.

It took 72 seconds. (i found this time a bit strange because i know it shouldn't take THAT long)

So what I did is replaced the database call (entityManger.createQuery(...)) by a Simple JDBC connection (conn = DriverManager.getConnection(url, props);) and I pull the data from the DB without using JPA.

it took 35 seconds to calculate the score for the same user...

Is it normal that using JPA makes the calculation 50% slower?? I mean I would have think that yes the time would have been "a bit" more than simply using jdbc but 50% ??

Is there any configuration that i don't know about that would explain this difference?

Here is the pseudo code of the algo. calculateScore is call within a loop 2000 times:

public double calculateScore(userId, placeId) {

//This returns in average 5 columns
List<Object[]> datas = entityManager.createNativeQuery("SELECT info1, info2, info3 WHERE user_id=.. and place_id=...").getResultList();
..
..
for (Object[] result : datas) {
 info1 = result[0] // Integer
 info2 = result[1] // Integer
 info3 = result[2] // Double

 if (info3 == null) {
     info3 = calculateMissing(..)
 }

 .. calculation ..

 return result
}

private double calculateMissing(..) {

    //this query returns maximum 20 rows 
    List<Double> data =  entityManager.createNativeQuery("SELECT info FROM..."). getResultList;


    //So here we could have UP TO 20 call to the db
    while(some cond) {
       data2 =  entityManager.createNativeQuery("SELECT * FROM...).getOne()
       if (data2 ..)
         cond = false
      ..
      }

    ... calculation ..

   return result;
}

So here are my "three" little select to the DB.

EDIT 1

I have tried narrowing where "exactly" the problem was. I have replaced one by one the call from jdbc to JPA. and it appears that the while loop was the one making the 50% (well almost) time loss. Doing dis was a really bad idea from the start, but I didn't know that JPA was not gonna like this AT ALL.

I have replaced the while loop with a smarter SQL query. And now the algorithm runs in 15 seconds instead of 70 seconds!

And in 12 seconds instead of 35 for the JDB version.

Upvotes: 0

Views: 3462

Answers (2)

Marcin Szymczak
Marcin Szymczak

Reputation: 11433

From my experience it seems that speed difference between hibernate/jdbc is significant for small queries. On the other hand when you try it with big list of results the preformance is quite similar.

It is really not bad in practical usage. Small queries still run fast enough. Long running queries (which should be your main concern) have similar performance.

Upvotes: 0

Andres
Andres

Reputation: 10717

I've experienced very large differences between JPA/Hibernate and JDBC when retrieving records by their primary key.

The differences can be particularly large if it's a flat object model. As the model complexity increases, the performance becomes more even.

Also, consider that using JPA implies the creation of a connection pool when the app starts (which is not necessary the case with JDBC), so the first transaction in special could take considerably longer with JPA than with JDBC.

Upvotes: 1

Related Questions