NHibernate HQL vs CriteriaAPI vs QueryOver vs Linq. Performance

Question

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 and Linq.

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

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.

Immixture

Answer 1

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.

Explode

Very nice. Lets see what the others say about QueryOver. – Immixture

Answer 2

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.

Chromonema

Answer 3

QueryOver is a very good replacement for Criteria in most respects because of the improved syntax and the type-safety. However, it should be noted that processing the lambda expressions in the QueryOver can be expensive (and I guess the same would apply for LINQ queries). So while executing the end SQL query does not take any longer than other methods (ICriteria, HQL), converting the query object to the appropriate SQL query does take longer.

As an example, we have been developing an application that was running hundreds of QueryOver queries a second (using ADO.NET batching support) and we found converting the queries to ICriteria almost doubled our ability to execute queries AND halved CPU-usage. I guess the overhead involved in processing the lambda expression can be much-reduced if you use the second-level cache and query caching; but as this is not suitable for our application, I haven't done this myself.

Regarding your more general question as to which is faster: it really depends. The only sure-fire way of telling is by running a profiling tool (NHProf works wonders for scenarios such as this). But generally speaking, HQL is the closest to SQL and therefore the most flexible, allowing you a bit more scope to optimize your queries. However, for the vast majority of simple to intermediate complexity queries, there is little to no difference between ICriteria and HQL. And ICriteria queries are far easier to work with if your queries are dynamically built.

Blood

we've seen some performance hits using query over. The lambda compilations can be quite expensive. – Sihon

Answer 4

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

Shocker

Well, I was afraid that this will happen. I was trying to understand which is faster and why. This is not a choice between the two. Actually I have my choice already. So, lets go back to the performance of the HQL and Criteria. PS: By the way in the comments Ayende says that the performance is equal but I am not very sure. I think that there are situations where we have performance issues. This is just a guess. – Immixture

You say you have performance issues. Which method are you using? I Prefer criteria because I think Hibernate makes the fastest query for you – Oday

In my experience it is lack of understanding one of the APIs that makes it seem slower. Have you considered trying NHProfiler/some other profiler to see what is taking the time? If you have an example that runs faster in one of the two - try looking at the raw sql that is outputted - that's usually the culprit. – Shocker

Actually I use criteria and I do not have performance issues so far. I read thermal7's answer here #3262356 and I was curious what are the differences. – Immixture