NHibernate Eager Loading - Lots of unrelated data

Question

My members will have the ability to customise their profile page with X amount of widgets, each widget displays different data such as a list of music, list of people they are following etc.

Several widgets include: - List of media they have uploaded - List of people they are following - List of people following them - Html/Text widget - Media Statistics (num downloads etc) - Comments widget for other members to leave comments

Some widgets will have to page the data returned because there could be hundreds of results.

I haven't done any optimisation at the moment so it is doing lots of DB work to return all the data...what would be the most efficient way to retrieve the data...would 1 DB call per widget be acceptable? There could be around 5-20 widgets per page.

If you need more information about my situation please feel free to ask.

Paul

Answer 1

Short answer: It depends.

Start off from the unoptimised state, then use SQL profiler or a C# profiler like dotTrace to work out the best places to make improvements. Set a realistic goal to work towards (e.g. 'less than 800 milliseconds to load the page').

Generally I find performance starts to suffer after about 20-30 database calls in a request, but this is going to depend on your server, the location of the database etc.

There are many things that you can try: pre-caching, eager fetch using joins rather than selects etc. Nothing is going to guarantee better performance though unless it is applied intelligently.

For a page with lots of widgets, a common design pattern is to load each widget asynchronously using AJAX, rather than loading the entire page in one go.

Answer 2

since you've cut out out your work to widgets the proper thing to do would be for each widget to do a single query for all its required functionality. This would also be the case even if you retrieved widgets via AJAX (which as cbp noted is not a bad idea).

Secondly, i would set up some kind of mechanism for each widget to register its existence and then after all widgets have registered then i would fire a single query that would include all widget queries. (technically its again multiple queries but in a single round-trip, see MulriCriteria and MultiQueries in NH reference).

Also do not forget that lazy loads are hidden db retrievals and you could have a huge performance impact by using lazy load in a situation where an eager load is proper (for example Foo.Bar.Name where you always show the Bar.Name value when you present the Foo entity)

Performance degradation can occur even with less that 20-30 database call per request, but it depends on the size and complexity of your entities, queries, filters as well as the size of the data sets retrieved.