NHibernate HQL vs CriteriaAPI vs QueryOver. Performance.

Question

Hi,

What are the performance differences between the hql and criteriaApi and QueryOver? Are there any situations where one is faster or slower than the other?

Edit: I extended the question with QueryOver.

=============================================

Well, I am not sure which response to mark as answer so I will mark posts with most VoteUp. I don't know. This is actually more discussion than a question.

Answer 1

I have yet to find any difference between the two. That being said - hql has functionality that isn't available in the Criteria-API.

Mostly it is a matter of style and personal preference if you use the former or the latter. And the translation from your query to SQL doesn't account for much when it comes to querying.

From the people that know more than I: http://ayende.com/Blog/archive/2009/06/01/nhibernate-queries-ndash-should-i-use-hql-or-criteria.aspx

Answer 2

The performance characteristics between ICriteria and HQL vary by little (i don't know about QueryOver) for one simple reason.

ICriteria queries will by default try to implement your fetch strategies as defined in your mapping whereas HQL by default considers everything Lazy and depends on your join declarations inside your query to define what is eagerly fetched or not.

Additionally ICriteria depends on mapping for parameter passing whereas HQL allows for explicit parameter types e.g. IQuery.SetInt32("fooParam", 5);

What really matters is the pluggability of ICriteria queries (see ICriteria.CreateCriteria().CreateCriteria() etc) where the NH engine will have to resolve the ICriteria and try to generate the most valid SQL.

In the opposite side its probably easier to predict if an HQL query will produce consistent results and as such makes QueryCache usage easier.

Either way the configuration is generated once with the ISessionFactory creation and mapping definitions and what-not are in-memory, so performance is good.

The actual SQL generation is made differently between the two and that is the reason that a utility for ICriteria -> HQL does not exist.

In the end, my experience has shown me that performance between the 2 is probably the same give or take some milliseconds.

As a side note, declaring as much as possible in the mapping will result in more concise sql generation as well as NHibernate operation, for example for string properties mapping the Length in the .hbm.xml will result in NHibernate checking string lengths before going to the database.

Answer 3

As far as QueryOver is concerned, I would expect the performance to be identical to the Criteria API, since it is built as an extension of that API. So for equivalent queries in the two APIs I would expect the same database query to be generated. The only difference I have noted between QueryOver and Criteria interfaces is that I find the QueryOver interface to be "cleaner" and more pleasant to work with.

In my experience the Criteria API has received more "love" than the HQL interface. I have run into cases where I could express certain queries with the criteria interface that I could not find an equivalent way to express in the HQL interface.

Regarding performance, I find the way the query is "asked" has more bearing on performance than the selection of Criteria API versus HQL versus QueryOver. I would use the interface that provides you with the most natural fit to your thinking.