When using nhibernate, how do I figure out optimal batch size in the mapping code?

Question

I am using nhibernate and i have code like this in the mapping area:

  HasMany(x => x.People).AsBag().Inverse().Cascade.AllDeleteOrphan().Fetch.Select().BatchSize(80); 
  HasMany(x => x.Clothes).AsBag().Inverse().Cascade.AllDeleteOrphan().Fetch.Select().BatchSize(150);

My question is, how do i figure out what the best value to put in .BatchSize ? Should i simply put a high number. Is there a downside to putting a number too high?

Answer 1

There isn't a single answer to this question.

The only way to determine what works best is profiling the app in production, with real users doing real things. This is time-consuming, so it's probably something you'll do only if you find the app is slow

The risk of a too-high number is that a query with a lot of parameters retrieving a range of records for each one might be harder on the DB than a few smaller queries.

Also, if you are not using all the collections that you have referenced (for example, if you retrieved a list of PostCategories, but you only show the Posts for one of them), you might be loading a lot of data unnecessarily.

My advice is to start with something like 20 or 50(*) and make it higher only if you often find yourself needing more than twice that number of collections loaded concurrently.

(*): yes, I did pull those numbers off my butt. 50 is what I currently use by default.

Answer 2

Though rather old, there is a blog entry by Ayende, where he compares different batch sizes (believe 10, 25, 250). Check it out. It may be useful to you.

Answer 3

I generally set my batch-size to the same value as my page size of the GUI.

So if my page size is 25 so is my batch-size. I have no evidence that this is a good value to choose but if just feels right to me.

However said the only true way is to follow what @Diego has written, run a profiler on the live server for a week and analyse the results. Change the batch-size run for another week an analyse both results. A lot of work I suspect!

I wouldn't recommend to high of a value as this could have negative impact. Another way to look at this is if your batch-size is high then you may be returning to many records in the select statement in the first place.