java - How does Hibernate's batch-fetching algorithm work?

Question

I found this description of the batch-fetching algorithm in "Manning - Java Persistence with Hibernate":

What is the real batch-fetching algorithm? (...) Imagine a batch size of 20 and a total number of 119 uninitialized proxies that have to be loaded in batches. At startup time, Hibernate reads the mapping metadata and creates 11 batch loaders internally. Each loader knows how many proxies it can initialize: 20, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1. The goal is to minimize the memory consumption for loader creation and to create enough loaders that every possible batch fetch can be produced. Another goal is to minimize the number of SQL SELECTs, obviously. To initialize 119 proxies Hibernate executes seven batches (you probably expected six, because 6 x 20 > 119). The batch loaders that are applied are five times 20, one time 10, and one time 9, automatically selected by Hibernate.

but I still don't understand how it works.

1. Why 11 batch loaders ?
2. Why batch loaders can initialize: 20, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1 proxies ?

If anybody could present a step by step algorithm ... :)

Answer 1

This helps avoid creating a large number of different prepared statements.

Each query (prepared statement) needs to be parsed and its execution plan needs to be calculated and cached by the database. This process may be much more expensive than the actual execution of the query for which the statement has already been cached.

A large number of different statements may lead to purging other cached statements out of the cache, thus degrading the overall application performance.

Also, since hard parse is generally very expensive, it is usually faster to execute multiple cached prepared statements (including multiple database round trips) than to parse and execute a new one. So, besides the obvious benefit of reducing the number of different statements, it may actually be faster to retrieve all of the 119 entities by executing 11 cached statements than to create and execute a single new one which contains all of the 119 ids.

As already mentioned in the comments, Hibernate invokes ArrayHelper.getBatchSizes method to determine the batch sizes for the given maximum batch size.