Session.SetBatchSize does not change the batchsize

Question

hi there,

I create the Session factory like:

FluentConfiguration cfg =
    Fluently.Configure().Database(MsSqlConfiguration.MsSql2005.ConnectionString(
        c => c.Is(dbConnectionString)).**AdoNetBatchSize(100)**.ShowSql()).                    
            Mappings(m => m.FluentMappings.AddFromAssembly(mappingAssembly)).
            Mappings(m => m.HbmMappings.AddFromAssembly(mappingAssembly));

If I later set session.SetBatchSize(someOtherSize); during the later program execution nothing happens. it is as if this command is just a mock.

Why that?

Thanks in advance

Answer 1

How are you checking that batching actually occurs and what batch size is being used? SQL profiler does not show batching, you have to use NHibernate Profiler to get a good understanding of what is being batched.

Looking at the NH source session.SetBatchSize() does what it says it does, so it should work :)

Answer 2

Don't forget to set the <property name="adonet.batch_size">3</property> in the config file. The max value, I think is 50. But NH doesn't throw any error if set an higher value and I don't know the default value the.

Answer 3

I have no idea if and how the NHProf reports batching but using the normal SQL Profiler you cannot notice it.

To verify how it works and if it is indeed enabled as I have set it up, I had to debug the NHibernate's code.

What NHinernate does is to add each generated SQL command in a collection of SQL commands that it is flushed (send to the DB) when the defined BatchSize is reached or when there are no more SQL commands to execute.

Observing the SQL profiler this is not noticable as SQL queries appear but actually NHibernate sends the commands in bactches to the DB.

This way if you want to execute 10 SQL statements without setting the BatchSize NHinerante will talk to the DB 10 times but setting the BatchSize to 10 then it will talk to the DB only once sending the all SQL queries in one go. Unfortunately this is not noticeable in the SQL Profiler...

Answer 4

I'm using NHibernate Profiler to see if batching really occurs. Code looks like this

Session.NHibernateSession.SetBatchSize(data.Items.Count);
        foreach (var o in data.Items)
        {
            //something else...
            base.Save(o);
        }
        Session.NHibernateSession.SetBatchSize(0);

Profiler still gives me error "Large number of individual writes".

BTW Im using Fluent Nhibernate

Thnx