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?

Capel

You can also control the batch size in code session using session.SetBatchSize() – Remediless

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.

Polythene

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.

Arhat

The blog post is about batch insert/update/delete, not collection loading. – Polythene

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.

Kevel