NHibernate efficiency

Question

Having fallen behind in the world of ORM and modern data access, I'm looking to move away from DataSets (shudder) and into a proper mapping framework.

I've just about got my head around Linq to SQL, an I'm now looking into NHibernate with the view to using it in our next project.

With old school sql and data sets, your sql queries obviously only return the data you want. I also understand that L2S is also clever enough to evaluate its where clauses so that it only ever returns the objects you requested. Is NHibernate the same? And is it the same with Ayende's Linq to NHibernate?

By this i mean, if i do the equivalent of:

Select * from customers where name = "fred"

will it fetch every customer into memory, and then filter out the non-freds, or is it clever enough to only get what it needs in the first place?

If it is intelligent, what are the caveats? Are there certains types of query which cannot be evaluated in this way? What performance issues do i need to be aware of?

Thanks

Andrew

Answer 1

A quick answer is that the ORM will check the property name and will transform it to a SQL query that will do you name = .... So it won't load all the customers into the memory to search for the name.

Answer 2

Hi Andrew.

Nhibernate comes with a couple of different ways to query the database. Hibernate uses a Sql like syntax called HQL, which is SQL on objects. Also it can do a search by example, where you create an object and fill in the critea you want, then hibernate will pull the object out of the DB which have the same property values.

have a look here it will get you up to speed summer of Nhibernate lession 2 and 2a will answer in more depth

HTH

bones

Answer 3

Yes, NHibernate should be one of the most performant ORM out there, I'd used in one project and it was pretty cool. About LINQ to NHibernate you may want to check this article: http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/11/26/linq-to-nhibernate.aspx There is a learning curve (NHQL and ICriteria) and if you want to make the jump is going to be difficult at first, but then all the invested time comes back to you. Hope this helps.

Answer 4

IF you are truly worried about what queries NHibernate is generating, run SQL Server Profiler and find out what is running or configure it's logging and turn on its SQL output. It'll display all the SQL statements to where you configure its log4net.

Answer 5

NHibernate will create a similar query to your example whether you use HQL or the ICriteria but it will actually do you one better. It will create a parametrized sql statement so that the query can be cached in SQL Server.

The ICriteria is extremely flexible, however there is a little bit of a learning curve to become proficient in it. I too recommend the summer of nhibernate screencasts to get up to speed quickly.

Answer 6

It is somewhat simple to achieve "enterprise" class performance with NHibernate.

Based on the property and relation mappings in the object the ORM will build a specific query for that fetch. If a property is marked as lazy loading then it will be excluded and when the property is accessed within the session a subsequent fetch will get only that piece of missing data. So if we were to turn on SQL profiler the translated query would like something like this

Select cust.Address, cust.Email from customers cust where cust.Name = "fred"

Also important is the depth of your query. We can limit the number of records that are returned (important for large data sets) by using HQL queries that page the data depending on your database provider...as long as they work with the HQL parser. You can also set the FetchMode or CollectionFetchMode on your relation mappings that will perform joins to select from multiple tables in a single interaction.

If you are using SQL server you can turn on sp_execsql that will run all of the fetches through a stored proc for a quick performance boost.

For more information there is a great article on Code Project that provides details on how to improve the performance of NHibernate in your application. If you are using a service-oriented architecture, latency can be a big concern. Check out this thread for some details on that.

Answer 7

Linq to SQL development has been dropped for Linq to Entities. You should give it a try also.