What adavantages does HQL have over SQL for Group By queries

Question

I am concentrating this question on 'reporting-type' queries (count, avg etc. i.e. ones that don't return the domain model itself) and I was just wondering if there is any inherent performance benefit in using HQL, as it may be able to leverage the second level cache. Or perhaps even better - cache the entire query.

The obvious implied benefit is that NHibernate knows what the column names are as it already knows about the model mapping.

Any other benefits I should be aware of?

[I am using NHibernate but I assume that in this instance what applies to Hibernate will be equally applicable to NHibernate]

Answer 1

HQL is an object query language. SQL is a relational query language.

Answer 2

There are zero advantages. HQL will not outperform a direct database query to perform data aggregation and computation. The result of something like:

Select count(*), dept from employees group by dept

Will always perform faster at the DB then in HQL. Note I say always because it is lame to take the 'depends on your situation' line of thinking. If it has to do with data and data aggregation; do it in SQL.

Answer 3

The objects in the second level cache are only retrieved by id, so Hibernate will always run a query to obtain a list of ids and then read those objects either from the second-level cache or with another query.

On the other hand, Hibernate can cache the query and avoid the DB call completely in some situations. However you have to consider that a change to any of the tables involved in the query will invalidate it, so you might not hit the cache very often. See here a description of how the query-cache works.

So the cost of your query is either 0, if the query is cached, or about the same as doing the query in straight SQL. Depending on how often your data changes you might save a lot by enabling query caching or you might not save anything.

If you have a high volume of queries and you can tolerate stale results, I'd say it's a lot better to use another cache for the query results that only expires every x minutes.