Hi all,

Any reason why setting a large batch-size, say batch-size = 100, is particularly bad?

Everything I've read about batch-size on Entities or collections say to keep this number small, preferably under 10.

I have a worst-case scenario that returns hundreds (possibly 2 or 3 thousand) of Items. The average case is most likely under a hundred.

As you can see from the sample mappings below, each Item has two collections that are not lazy loaded. This results in an additional 2 queries per Item returned.

If there are M Items to return, and the batch-size is set to N on both collections, the number of queries is roughly 2*M/N. My current configuration confirms this.

What are the side-effects of specifying a large batch-size?? E.g. I'd like to set the batch-size = 100, so for our large users this would result in only a dozen queries at most. Any reason why this would be bad, besides memory considerations?

My gut tells me that this is a bad thing, but I can't think of a concrete reason (except for memory considerations and large generated SQL), why this would be bad.

Even if batch-size = 1, the heap still has to handle 2 thousand items.

The only other thing is that the generated SQL underneath could potentially be very big with all the added (alias0.id=?) OR (alias0.id=?) OR ....

Thanks!

Hibernate version: Hibernate 2.1.7

Mapping documents:
These are sample mappings.


Sample HQL query:


Database: SQLServer 2000

The SQL will be ugly, but otherwise there is probably no big problem.


But you really *should* explore other options like subselect fetching or join fetching.

Thanks!! We're probably going to migrate to Hibernate3 in the next release and consider either subselect fetching or projection. For now, though in production, we have to live with Hibernate 2.1. We could left join fetch one collection, but two collections would be unwieldly, due to the cartesian product problem.

Also, for our particular case, the row being retrieved in "Item" has lots of columns (the above mappings are only sample mappings). Performing a left join fetch, even on one collection, would return a denormalized result set with Item being duplicated for each row in the eagerly fetched collection association.

If Hibernate does not complain much with a pretty large batch-size, I'd think I'd prefer that for now.

Initial testing confirms that batch-size="100" does not barf.

Thanks!

By setting the log level to DEBUG, I found out that for a batch size of 10, 10 select statements are generated. The first one with 1 key condition. The second one with 2 or-ed key conditions. The last one with 10 or-ed key conditions.

Therefore, I expect that a batch size of 100 would result in 100 pregenerated selects.

Turns out that setting the batch-size did work.

Our sample case of 1006 items (and 2012 collections), resulted in a total of only 53 queries. Not that far off from the theoretical expectation of 43!!
One query for the main load leaves 26 queries for each collection. That is 5 more than the expected 21, but I imagine that its just that the batch-size is not fully populated in some cases.

Bottom line is that 53 queries IS MUCH LESS THAN 2012 queries.

BTW, my numbers were not generated by DEBUG, but by setting show_sql = true. Could it be in your case that only the last one is used and the other nine are discarded?

I never wanted to say that it wouldn't work. In the contrary.
The statements I talked about were not executed but were held in memory for future execution.
I just wanted to point out that with a batch size of 100, you have 100 pregenerated select statements. This can become a memory bottleneck (perhaps).

Regards,
Heinz

Thanks for the clarification. I guess this isn't too bad since if batch-size=1 it has to pregenerate those select statements anyway (e.g. similar memory foot-print).

Thanks for the info!

Actually it is less than 100: the batch sizes are 100, 50, 25, 12, 6, 3,2,1, so only 8 SQL statements are required. (This is done in anticipation of the problem you describe.)



Negative. Hibernate generates and caches just one SQL statement.

I see, we're not on terms yet.

For batch-size=1, Hibernate pregenerates (and holds in memory) exactly one select statement with one key condition for this association. I have not investigated further whether this statement is held as a prepared statement or as a string.

For batch-size=10, it pregenerates (and holds in memory) 10 select statements. The first statement with one key condition, the second with two or-ed conditions, the third one with three or-ed conditions, ....
This way a pregenerated statement exists no matter how many instances (up to batch-size) have to be loaded.
At further run time no statements are generated. If 2 instances have to be selected, the second statement is used. If 10 instances have to be selected, the tenth statement is used.
Therefore, the memory foot-print does depend on your batch-size.

For batch-size=100, 100 statements are pregenerated for this association.
Therefore you need more memory if you increase the batch-size.
And if prepared statements are used, you also need the corresponding database resources.

Regards,
Heinz

Hibernate holds it as a string, the database connection pool will hold it as a PreparedStatement.

But again, the situation is not quite so bad as you say, because it is really only 8 statements, instead of 100.

Nice piece of information.
I read over your post the first time.

Keep up the good work (and support!),
Heinz