3

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);
Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
SPagadala
  • 217
  • 3
  • 12

1 Answers1

2

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
  • 77,657
  • 34
  • 181
  • 348
  • Thank you Jens Schauder. Q1- If leave HINT_FETCH_SIZE as 50 and with Pageable 1000, does it have any performance impact? Q2- When you mention 'query will get executed only once per page', even with a Pageable as 1000, do you mean query always tries to return 1000 records by ignoring FETCH_SIZE 50 ? – SPagadala Jul 20 '20 at 23:03
  • No, it is not ignoring the fetch size. That is simply not what fetch size does. I tried to explain that in my answer. – Jens Schauder Jul 21 '20 at 07:28