Can we use HINT_FETCH_SIZE and Pageable together in JPA query

user_7144151 0
Viewed 1617

I have a query like below in my JpaRepository. I have the Pageable size as 1000 records, but HINT_FETCH_SIZE as 50. Does this mean to fill the Page of 1000 records, this query is called 20 times (20 x fetch size 50)? Is my understanding correct? What could be the ideal HINT_FETCH_SIZE if we need a Pageable of 1000 records?

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "50"))
@Query("SELECT m FROM sales m " +
        "WHERE m.settlementDate >= :start " +
        "AND m.settlementDate < :end " +
        "ORDER BY m.salesId")
Page<Sales> findBySettlementDatePage(
        @Param("start") LocalDate start,
        @Param("end") LocalDate end,
        Pageable pageable);
pageable spring-data-jpa fetch
1 Answers

Does this mean to fill the Page of 1000 records, this query is called 20 times (20 x fetch size 50)?

No. The query will get executed only once per page. The fetch size determines how many rows the database should send at once before waiting for the client to signal that it has processed all the rows send so far which happens automatically by accessing all the rows in a ResultSet which in turn happens when the result is converted to a List or similar by the JPA implementation.

This is especially useful when not all rows fit into memory. So this argument is not that relevant.

Another thing your database might do is optimise the query plan for the fetch size specified. This means it tries tor bring the first n rows to you as fast as possible even though this might make it take longer to get all rows.

Since you have to fill a Page object before your program can continue you want your fetch size so large that all the data for a single page gets fetched in one go. For a simple entity this might be exactly the number of rows in the Page. But if the entity has an eagerly fetched one-to-many relationship it might actually do a join and fetch way more then 1000 rows.

So far the theory. In practice you just shouldn't specify a fetch size and just leave it to the database driver. And only when you see problems (or have a good reason to expect some) should you actually experiment with different fetch sizes to see where the best value for your scenario is.

Jens_Schauder_666860
Related