CODEWITHSUNDEEP
javasqlperformancehibernatejpa
user1079877
user1079877

Reputation: 9358

How to optimize a JPQ JOIN query to run faster

I have big performance issue in my JPA application. Suppose I have 3 entities:

class TaskResult {
    Task task;
}

class Task {
    User user;
}

class User {
    Long id;
}

Now, I want to get list of all TaskResults for one specific userId, there query will be something like:

SELECT * FROM TaskResult r WHERE r.task.user.id = :id

And for over 10000 records it's too slow! Is there any other workaround?

Upvotes: 4

Views: 2349

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153700

  1. Try restricting the SELECT clause to TaskResult only and use explicit JOINs:

     SELECT tr 
 FROM TaskResult tr 
 JOIN tr.task t
 JOIN t.user u
 WHERE u.id = :id

  2. Turn all EAGER associations into LAZY ones and use FETCH on a query-basis.

  3. Make sure there's an index on all Foreign Keys (Task.task_result_id and User.task_id)

  4. Try running an SQL query an see if it's that slow too. If the SQL is slow, then Hibernate can't get any faster.

  5. Try selecting fewer columns from TaskResult and use a projection instead.

  6. You say there are 10,000 rows, but you surely don't need to display that many entities into the UI. Try using keyset pagination which scales better than the default OFFSET pagination (supported by most JPA implementations).

Upvotes: 8

Related Questions