How do you determine the recommended batch size of ADO.NET in the real world?

Question

NOTE: I am not looking for an answer from MSDN .

How did you determine the correct ADO.NET batch size for this database / application? What factors led to your decision and what experience can you share?

Using Fluent NHibernate, I am now using something like:

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

In my opinion, this will collect up to 50 statements at a time before sending them through the Connection object to the server for processing.

Answer 1

You do not need to set this value. Let it work with the default value. It really matters if your queries return a huge amount of data, for example. BLOBS, in this case you would choose a small batch size of ~ 10-20. For ordinary purposes, just leave it. I never had to install it, and all my applications ran very fast.