There are two ways of selecting a list of objects in NHibernate based on a set of criteria.

One is using the IQuery interface which let you build a query using a sql like syntax. The other is using the ICriteria interface, which is a more object oriented way of creating a query.

I like the ICriteria way much more, but I found out that IQuery is much better in terms of performance.

I am working on a small project for a client that wants a website for engine tuning enthusiasts. I found out that the some of the pages that included searches were loading quite slowly, so I decided to try to optimize the performance.

I was using ICriteria and as an example I will show you how I filtered by a specific brand and ordered the result:

ICriteria crit = session.CreateCriteria(typeof(Car));
SimpleExpression exp1 = Expression.Eq(”Brand”, brand);
crit.Add(exp1);
Order order = Order.Desc(”HorsePower”);
crit.AddOrder(order);
IList carList = crit.List();

With the Expression-class you can create different comparisons, Gt/Like/Lt etc. and the order can of course also be ascending. I think it’s an elegant way of creating a query.

But then I tried to use IQuery instead, which in this example would be:

string q = String.Format(”from Car where BrandId = {0} order by {1} desc”, brand.Id, “HorsePower”);
IQuery query = session.CreateQuery(q);
IList carList = query.List();

This is much more SQL-like and to be honest I don’t want to be preoccupied with placing where’s and order by’s in a string, especially when there are many parameters and not all of them always are set.

But well it turns out that the search was 4-5 times quicker using IQuery.

It says in the description for ICriteria that it’s still an experimental API, so that’s probably why, but I’m a bit unhappy it’s performing that bad.