I recently spent some time optimizing a highly concurrent web application's database access code. This particular application uses NHibernate
for its ORM.
This post enumerates some of the "lessons-learned".
- Set show_sql to true. Viewing the SQL that NHIbernate runs while in development will help developers catch redundant or unnecessary database calls.
- Use HQL aggregation. HQL aggregate functions are much more performant than loading all of the objects and aggregating with code in memory.
- By default, enable lazy initialization.
- Use batch-fetching where appropriate. In a one-to-many mapping, if most of the time more than one item needs to be accessed in the "many" collection enable batch-fetching by setting the batch-size. The first time one item in the "many" is asked for NHibernate will prefetch "batch-size" items.
- Use the right fetch strategy.
- Retrieve objects by ID if possible. NHibernate has a first-level cache, which is only utilized when an object's retrieval is by ID.
- Use second-level cache for read-only associations.
- Multi queries can reduce database round-trips.
- Hand-code ADO.Net. Use a SQL profiler and a code profiler to find bottlenecks.If a bottleneck is found that can't be fixed by changing the fetch strategy, HQL, or caching, dropping down to ADO .Net is probably the best option. This doesn't mean swapping out NHIbernate completely, but just the slow query.
Labels: NHibernate, ORM, Redpoint