Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 10 | Comments: 37

filter by tags archive

NHibernate Multi Criteria

time to read 6 min | 1061 words

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

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

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

Right now, no, there isn't.

Feel free to send me a patch :-)

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

Jeff,

that is probably on the horizon for NH 2.0.

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

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

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

deepsun

thanks anyway....

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Production postmortem: The case of the memory eater and high load - about one day from now
  2. Production postmortem: The case of the lying configuration file - 3 days from now
  3. Production postmortem: The industry at large - 4 days from now
  4. The insidious cost of allocations - 5 days from now
  5. Find the bug: The concurrent memory buster - 6 days from now

And 4 more posts are pending...

There are posts all the way to Sep 10, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    14 Aug 2015 - The case of the man in the middle
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats