+972 52-548-6969
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;
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
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.
Is there any way to get an IList<T> 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<MyEntity>(myCriteria)
Right now, no, there isn't.
Feel free to send me a patch :-)
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...
Jeff,
that is probably on the horizon for NH 2.0.
In the mean time, you can use batch size to handle that.
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
please ask in the forums, this is not really the place for such questions
thanks anyway....
Comment preview