why your Spring Data JPA query so slow? took more than 10 mins to just fetch 100 thousands records

We encountered a query performance issue which took more than 10 minutes to complete a query from a table which has 100 thousands records. We are using Spring Data JPA framework. We thought the framework shouldn't be quite slow. 100,000 records is not too much. After google search, find some similar issues.

Why is JPA query so slow?

I am implementing queries in my web application with JPA repositories. The two main tables I am querying from are FmReportTb and SpecimenTb. Here are the two entity classes (only important attrib...

Stack Overflowddd

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

I have following tables in DB: Person, Parent, GrandParentParent - Person is OneToMany relation (Person has parentId)GrandParent - Parent is ManyToMany relation (grandparent_parent table) I c...

Stack OverflowByeBye

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.

Solutions

if you are using PreparedStatement, call PreparedStatement::setFetchSize before call executeQuery.

if you are using Spring JPA @Query, add @QueryHints to set fetch size.

@QueryHints(@QueryHint(name="org.hibernate.fetchSize", value="100000"))

or via properties in application.yml

spring:
  jpa:
    properties:
      hibernate:
        fetch_size: 100000

this setting might apply to all queries in your app, might cause memory consuming issues.

We used @QueryHints and call PreparedStatement::setFetchSize for the specific query will result in more than 100 thousands. We are now able to complete the query in 1 second. It is 660 times speed up comparing without the fetch size hint.