Ayende @ Rahien

Refunds available at head office

NHibernate Multi Criteria

A while ago I added Mutli Query to NHibernate, but I didn't have the time to add Multi Criteria capabilties in time for the 1.2 release. As it turn out, I managed to implement it about three weeks ago, and only now I had enough time to sit and document it.

Just as a reminder, the end goal is being able to use NHibernate to send this query in a single roundtrip to the database:

SELECT TOP 15 * FROM Customers;  SELECT COUNT(*) FROM Customers;

That is the canonical example for this feature. I think that it falls into the "optimization tricks" category, but I have run into several cases where the functionality was critical to satisfying the time constraints.

At any rate, now we can use this functionality with Criteria as well:

IList multiResults = s.CreateMultiCriteria()

     .Add(s.CreateCriteria(typeof(Customer)).SetMaxResults(15))

     .Add(s.CreateCriteria(typeof(Customer)).SetProjection(Projections.RowCount()))

     .List();

IList customers = (IList)results[0];

IList counts = (IList)results[1];

int count = (int)counts[0];

This result in a single query being send to the DB, and multiply result sets returned to NHibernate.

A more complex scenario may be a page where you have to get several types of data:

  • Policy Types
  • Insurance Types
  • Paged Customers List
  • User personalizaztion info

Now, instead of hitting the database four times, you can package this into a single query, with four results sets being returned.

Caching is supported, but only for the entire mutli criteria query, so you can't cache just the policy types and hit the DB for everything else.

Have fun, and remember the rules of optimization:

  • Don't do it
  • For experts only: Don't do it, yet.

Comments

Pete w
05/20/2007 03:23 PM by
Pete w

I for one am very excited about this one

Ive come across a few anecdotal situations where performance was of the essence and I had to bypass NHibernate to gain this kind of control over my data layer.

Increasing the granularity of communication between clients and servers reduces overhead performance costs incurred by "chatty" servers. Normally, you wouldn't care very much about an extra roundtrip to the database, but when you are considering this kind of performance adjustment, you are probably cognizant of your situation.

Jnapier
05/22/2007 11:45 PM by
Jnapier

Is there any way to get an IList from a search query in a multiCriteria? If not, we maybe able to add this capability if we modify add to take a typeparam like so .Add(myCriteria)

Ayende Rahien
05/23/2007 07:01 AM by
Ayende Rahien

Right now, no, there isn't.

Feel free to send me a patch :-)

Jeff Brown
06/11/2007 07:06 PM by
Jeff Brown

Nice stuff!

Now what I still need is a way to perform bulk updates in-situ in the database without resorting to custom SQL or sprocs...

Ayende Rahien
06/11/2007 07:29 PM by
Ayende Rahien

Jeff,

that is probably on the horizon for NH 2.0.

In the mean time, you can use batch size to handle that.

deepsun
07/04/2007 01:04 PM by
deepsun

Hi,

Please can you tell me how can i implement following

select x.*, y.Count from

(select * from Users u where u.Id between 1 and 100) as x,

(select Count(*), b.Id from User b group by b.Id ) as y

where x.Id = y.Id

or

select x.*, y.Count from

(select * from Users u where u.Id between 1 and 100) as x

inner join

(select Count(b.Id), b.Id from User b group by b.Id ) as y

on x.Id = y.Id

in NHibernate 1.2 or any version of NHIbernate. using HQL or Create Criteria

Please do reply

Ayende Rahien
07/08/2007 10:06 AM by
Ayende Rahien

please ask in the forums, this is not really the place for such questions

deepsun
07/09/2007 05:21 AM by
deepsun

thanks anyway....

Comments have been closed on this topic.