How do you determine the recommended ADO.NET Batch Size in the Real World?

Question

NOTE: I'm not looking for the answer from MSDN.

How have you gone about determining the proper ADO.NET batch size value for your given database / application? What factors led to your decision and what experience can you share?

Using Fluent NHibernate, I'm currently using something like:

var sessionFactory = Fluently.Configure().Database(
             MsSqlConfiguration.MsSql2005.ConnectionString(c => c.FromConnectionStringWithKey("connString"))
             .AdoNetBatchSize(50)
             )
             .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Foo>()).BuildSessionFactory();

From my understanding this will gather up to 50 statements at a time before sending them through the Connection object to the server for processing.

Answer 1

You don't need to set this value. Let it run with the default. It only really matters if your queries are returning massive amounts of data e.g. BLOBS in which case you would choose a low batch size ~10-20 For normal uses just leave it out. I've never had to set it and all my apps run super-quick.