[FIXED] Query on DB is fast, but fetching by JPARepository is slow

Issue

I have following tables in DB:

Person, Parent, GrandParent Parent - Person is OneToMany relation (Person has parentId) GrandParent - Parent is ManyToMany relation (grandparent_parent table)

I created in PersonRepository which extends JPARepository :

@Query("SELECT person.uuid FROM Person person JOIN person.parent parent JOIN parent.grandparents grandparent WHERE grandparent.uuid = ?1")
Set<String> findByGrandParentId(final String parentId);

For fetching all ids of Person which are under given GrandParent. This SQL is created:

SELECT
   person0_.uuid as col_0_0_ 
FROM
    person person0_ 
INNER JOIN
    parent parent1_ 
        on person0_.parentid=parent1_.uuid 
INNER JOIN
    grandparent_parent grandaparent_parent2_ 
        on parent1_.uuid=grandaparent_parent2_.parentid 
INNER JOIN
    grandparent parent3_ 
        on grandaparent_parent2_.grandparentid=parent3_.uuid 
WHERE
    parent3_.uuid='13906f55-441c-45bd-bef6-8beefa4119c4'

I logged how much time repository needs fetch data, and it took (average) ~400ms to fetch ~400 records. Then I execute the same SQL query on DB and each time query took no more than 50ms.

I know that this generated query is not optimized because we can only join two tables GRANDPARENT_PARENT and PERSON, but this is not the problem here, because such query is executed also below 50ms.

Why I have such differences between fetching by repository and fetching in db? Is it possible to fix that?

Solution

Multiple Possibilities:

1. The generated Query

The generated query looks pretty fine to me. It's exactly the same as the query within your @Query annotation.

2. SQL Result to Java Object conversion

I don't know how big your tables are, but: Converting SQL Results to Java Objects takes some time. For small tables this could increase query time by 0-5%.

3. Lazy loading

You didn't show the code for your entities. If you have @OneToMany or @ManyToMany relations, JPA will by default use Lazy Loading. This can really slow down everything by magnitudes.

4. Latency

If you execute the SQL Query on the same machine where the SQL DB is, but your Java Application communicates over network with the SQL DB, it can result in much slower queries, too.

(5. Wrong kind of DB. Seems like you build an object graph. Maybe have a look at Neo4j ;-))

Answered By - Benjamin M