We're having major performance problems w/ our nHib queries for our primary inheritance hierarchy which consists of a table for each concrete and abstract class: e.g. ManagedElement <-- NetworkElement <-- Device <-- Router. ManagedElement has 1-M ManagedElements; for example, Units and Nodes are ManagedElements and the containment is: Unit - ChildUnit - Node - Router (again, everything is a ManagedElement and, therefore, the containment is represented in the ManagedElement object and table). Devices like Routers have 1-M NetworkInterfaces.

We only have three thousand records in our ManagedElement table and one query that selected all our Units (and their ChildUnits) with their contained Nodes and the Devices in the Nodes and the Interfaces in the Devices took 20 minutes.

I'm about to embark on performance improvements. Does anyone have any insight? I'm looking at the following:

- We may need to redesign our tables for the above.
- One guy on a web site said nHib vs pure ADO.Net is 3-4 times slower and people suggested to a) turn off logging and b) try using the second-level cache.
- Another web site says to fully qualify table names - http://geekswithblogs.net/billy/archive ... 71736.aspx
- Another web site said that clearing the session/cache every 100 items or so on a save/update dramatically improves performance (issue Session.Flush(); Session.Clear();) - http://www.theserverside.net/news/threa ... 378#170533
- Another person has info on customized gets/sets to improve performance -http://jaychapman.blogspot.com/2007/11/nhibernate-access-performance.html

The HIA (Hibernate In Action) book has the following:
- Section 4.4.7 - Tuning Object Retrieval says to enable nHib logging to see the queries and if SQL use joins that are too complex or slow, set outer-join=false (default) for M-1 and look into setting max fetch depth between 1 and 4. Otherwise, if too many SQL stmts are executed, use lazy=true for all 1-M. It also recommends analyzing the query in the db to see if all indexes are used properly an the db cache hit ratio.
- Section 7.6 - Optimizing Object Retrieval says to set lazy fetching in map files and override to eager fetching in code via HQL when needed (based on analysis described in section 4.4.7). It also says that batch fetching can help and that if there are a small # of instances, consider keeping all instances in the second-level cache

- I'm going to read the following:
http://www.codeproject.com/KB/cs/NHibernate_Perf.aspx
http://www.codeproject.com/KB/database/ ... Perf2.aspx
- As well as the following from nHib's reference doc:
http://www.hibernate.org/hib_docs/nhibe ... mance.html:
http://www.hibernate.org/hib_docs/nhibe ... mance.html

Note: one of the things that we're going to look at -- because we have a very hierarchy-oriented model -- is Nested Sets

The first question would be : Why do you need to load ALL the database(3000+ entities) into memory ?.

If you are displaying the records then you're using a grid(or something like that) and you surely use paging which will load 20, 40, 100 rows at best .. after that it will obviously be be too many rows for a person to manage.


If you need to manipulate all that data at once, then go for a dataset.