Deriving a Date Range for a Day

Many times the database will contain data with different times and we need to get all data for one day.  In .Net this is done by:

 

Console.WriteLine("Start Date: {0}", DateTime.Now.Date); 

Console.WriteLine("End Date: {0}", DateTime.Now.AddDays(1).Date.AddMilliseconds(-1)); 

This would output: 

Start Date: 4/30/2009 12:00:00 AM

End Date: 4/30/2009 11:59:59 PM

 Let's say in NHibernate we want a criteria expression to retrieve all objects with a DateTime that falls within an entire date (regardless of time)

return Session.CreateCriteria(typeof(Person))

    .Add(Expression.Between("DateOfBirth", dateOfBirth.Date, dateOfBirth.AddDays(1).Date.AddMilliseconds(-1)))

    .List< Person >();

This would return all People that were born on the same day (regardless of the time of birth) since the Time portion of the Date are usually stored in the database.

Don't Blame NHibernate When It's ADO.Net

I can't count the number of times I have troubleshot an 'NHibernate' problem when it was either a severe design flaw or a logic bug.  From trying to write to views without indexes, working with tables without primary keys or simply causing ADO.Net to execute a query that gives an OutOfMemory exception before NHibernate gets the data.  NHibernate is a very flexible ORM, partially because there are a lot of poorly written databases out there and an ORM gives you a lot once you have taken the upfront cost in time to design.

When troubleshooting query issues, one of the first things I will do is enable 'show_sql' or look at the logs, then I will execute this same statement through a query tool.  If query analyzer or Toad cannot execute a query then it's not an NHibernate problem.  You can even go the next step and execute the query in ADO.Net itself to prove your point but that is usually not necessary unless you are dealing with a more persistent individual.

Some common exceptions with large results or complex queries will be ADO throwing query timeout exceptions or maybe an out of memory exception.  The first just means that the query took a long time to run, the fix for this could be optimizing your query or increasing the query timeout.  The later may be fixed by narrowing your results or the columns you are selecting if the table has a lot of columns.

But neither of them are NHibernate's fault.  More analysis and thought should be done before we just blame the first non-Microsoft product.  NHibernate has a lot of features for those situations that when even good design fails, this is why it's the premier ORM for .Net.

NHibernate Performance Tuning

OR/M as a central part of your application architecture will require tuning over time.  Below are some tweaks which can have incremental to significant performance effects on your code.

1. Enable 2nd level cache and have a strategy for using it for some of your classes. reference 
2. Enable batch processing for persistence of objects.  This will allow NHibernate to send batches of your transactions (Inserts, Updates and Deletes) to the server in a batch. reference 
3. Have a good strategy for flushing and transaction management.  Remember that if you commit a transaction that you do not need to flush the object.  reference 
4. Make read-only classes immutable. reference 
5. Understand lazy loading and use effectively, for instance when you may but not always need an associated object(s) reference 
6. To bypass lazy loading understand eager fetching and use when you know you need associated object(s) and want to bypass lazy loading. reference 
7. Remove any columns and properties that you will never or should never need or use.
8. Using value type objects for your properties and components as opposed to reference types, when you have a choice, this will lower memory footprint.
9. Avoid composite keys (exception legacy databases).  Composite keys are problematic for database design as well as OR/M therefore avoid them.  reference 
10. Consider your logging strategy for production (less verbose) and usually disable show_sql in production.

Orin's NHib Profiler can also help you find improvements you can make to your configuration of NHibernate.

StyleCop Extensions Project

I've started a small project on Google Code for creating common StyleCop extensions.  Currently it enforces one rule that requires underscores to prefix instance variables.  If you have an idea for a new rule then create an Issue for it as an Enhancement. StyleCop Extension Project Home