nhibernate alternates batch size

Question

When performing a query with NHibernate does not seem to be respecting the batch-size if it is set to more than the results actually returned.

I am using the latest version of NHibernate 2.1.0.4000 and the GA of Linq to NHibernate. I have an object structure similar to the Order which has a collection of OrderLines. The OrderLines have been defined as a bag with the following xml:

<bag name="OrderLines" access="field.camelcase" table="MyDatabase.OrderLines" lazy="true"   batch-size="50">
    <key column="OrderId"/>
    <one-to-many class="OrderLine"/>
</bag>

If I query for Orders and get 50 results back it correctly selects all the OrderLines in a single query, but if I get less than 50 results back it does not seem to respect the defined batch size.

E.g. If I get 40 results back instead of 50 if performs 3 queries with a batch size of 25, 12 and 3

Which looks like it is trying to guess the correct batch size to use (ie it does 1/2 the batch size first, then 1/2 the remainder etc). I would expect it to perform a batch size of 50 all the time and if there are less then make the batch size as large as it can, in this case a batch size of 40.

How can I get NHibernate to respect the batch size I have defined in all cases?

Answer 1

I was stumbling over the same strange behavior. I found that people were stumbling over the same thing in Hibernate (Java) too.

The behavior is documented here for Hibernate:

http://opensource.atlassian.com/projects/hibernate/browse/HB-1457
https://forum.hibernate.org/viewtopic.php?p=2233747#2233747
https://forum.hibernate.org/viewtopic.php?p=2422139

I guess this behavior is ported directly from Hibernate.

In short:
There are only a few SQL statements for batch-fechting prepared by Hibernate. Each with a fixed batch-size which definines the count of parameters in the IN-clause. Hibernate then uses these prepared statements for satisfying the batch-loading. The number you specify as batch-size in the mapping file, only defines the max. batch size that can occur.

For instance given batch-size=1000. If you have 200 parent entities and want to load the child-collections of these, nHibernate decides to use 4 statements: one with 125, 62, 10 and 3 parameters in the IN clause (summing up to 200).

However if you only have 125 parent entities, then hibernate decides to use only one statement, the one with 125 parameters.

(The numbers above are my observations in NH 2.1)

The reason behind this: (according to the linked forum discussion)
Concern about the negative performance impact of creating many different PreparedStatements when the maximum batch-size is large. (PreparedStatements are a Java construct, I wonder if this performace concern is equally valid for .NET)