Fetch Size and Streaming

When a query returns many rows, the JDBC driver pulls them from the server in chunks. Fetch size is the chunk size. Too small means many round trips; too large means a lot of memory per query.

public class Main {
    public static void main(String[] args) {
        int rows = 100_000;
        int[] fetchSizes = {10, 100, 1000, 10_000};
        System.out.println("fetch size | round trips | rows held in driver memory");
        for (int f : fetchSizes) {
            int trips = (rows + f - 1) / f;
            System.out.printf("%10d | %11d | %d%n", f, trips, f);
        }
    }
}

fetch size | round trips | rows held in driver memory
        10 |       10000 | 10
       100 |        1000 | 100
      1000 |         100 | 1000
     10000 |          10 | 10000

public interface OrderRepository extends JpaRepository<Order, Long> {

    @QueryHints({
        @QueryHint(name = "org.hibernate.fetchSize", value = "500"),
        @QueryHint(name = "org.hibernate.readOnly", value = "true")
    })
    @Query("select o from Order o where o.createdAt >= :from and o.createdAt < :to")
    Stream<Order> streamForReport(@Param("from") Instant from, @Param("to") Instant to);
}

@Transactional(readOnly = true)                  // the stream needs an open transaction
public void writeMonthlyCsv(Writer out, YearMonth month) {
    try (Stream<Order> orders = orderRepository.streamForReport(start(month), end(month))) {
        orders.forEach(o -> {
            writeCsvLine(out, o);
            entityManager.detach(o);             // keep the persistence context small
        });
    }
}