[SOLVED] Setting Hibernate Fetch/Batch size in Spring-Boot

Setting Hibernate Fetch/Batch size in Spring-Boot

I have a query, which is expected to return ~500.000 elements, which have to be postprocessed. The elements are loaded from a spring-boot app with JPA/Hibernate. To increase the overall speed of the operation I use the getResultStream instead of getResultList.

Still, the speed of the operation seems to low. I experimented with the hibernate fetch-size, which should be applicable here.

In my application.yml, the fetch size is set in

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: ...

When I put the logger org.hibernate.cfg to debug, I can see that the values I set are printed out. However, they seem to have no effect whatsoever. Whether the fetch size is set to 1, 10, or 2000, the time for executing the code never differs.

What am I doing wrong here?

Solution

So, we just learned the hard way, that SqlServer ignores the fetch size in the default SELECTMETHOD=DIRECT and simply always transfers the whole ResultSet as is. Therefore, any changes in the fetch size will have no effect when executed against SqlServer unless you change the SELECTMETHOD to CURSOR (which will slow your queries down a lot).

My issue was therefore not with hibernate but with the underlying database.