How to optimize a JPQ JOIN query to run faster

Question

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?

Answer 1

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).