CODEWITHSUNDEEP
javasqlspringoracle-databasespring-data-jpa
ByeBye
ByeBye

Reputation: 6946

Query on DB is fast, but fetching by JPARepository is slow

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?

Upvotes: 4

Views: 12038

Answers (3)

Errong Leng
Errong Leng

Reputation: 1

or if you are using PreparedStatement::executeQuery call PreparedStatement::setFetchSize before you execute the query

Upvotes: 0

Rohim Chou
Rohim Chou

Reputation: 1259

Had the same problem, expanding the jdbc fetchSize property resolved the issue for me:

// adding the fetchSize QueryHint for the query
@QueryHints(@QueryHint(name="org.hibernate.fetchSize", value="200"))
@Query("SELECT person.uuid FROM ....")
Set<String> findByGrandParentId(final String parentId);

// or set it globally, e.g., through application.properties
spring.jpa.properties.hibernate.jdbc.fetch_size=200

Through profiling, found that the bottleneck occurred while matching each ResultSet row to Entity object. Seems that each round trip to the DB slow down the overall performance in my case.

From Oracle JDBC Developer's Guide (emphasis mine):

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Upvotes: 1

Benjamin M
Benjamin M

Reputation: 24527

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

Upvotes: 3

Related Questions