What are the benefits of HQL for SQL for Group By queries?

Question

I am concentrating this question on "reporting" type queries (count, avg, etc., i.e. those that do not return the domain model itself), and I'm just wondering if there are any performance advantages for using HQL because it can use second level cache. Or perhaps it’s even better to cache the entire request.

The obvious implied advantage is that NHibernate knows what column names, as it already knows about model matching.

Any other benefits I should know about?

[I use NHibernate, but I assume that in this case, what applies to Hibernate will be equally applicable to NHibernate]

Answer 1

There are zero benefits. HQL will not exceed the direct database query to perform aggregation and data calculation. The result is something like:

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

Will always work faster in the database, then in HQL. Notice, I always say, because it is lame in order to take the line of thinking "depends on your situation." If this is due to aggregation of data and data; do it in SQL.

Answer 2

Objects in the second-level cache are retrieved only by id, so Hibernate will always run a request to get a list of identifiers, and then read these objects either from the second-level cache or using another request.

Hibernate, on the other hand, can cache the request and completely avoid calling the database in some situations. However, you should keep in mind that changing any of the tables participating in the query will invalidate it, so you may not often delete the cache. See here for a description of how the cache request works.

So the cost of your query is 0 if the query is cached or about the same as the query in direct SQL. Depending on how often your data changes, you can save a lot by turning on query caching or you won’t be able to save anything.

If you have a large volume of queries, and you can tolerate outdated results, I would say that it is much better to use a different cache for query results that expire only every minute.

Answer 3

The only advantage I can think of is that ORM requests are usually cached at the (prepared) statement level, so if you make the same request many times, you may be reusing the prepared statement.

But since you specifically set reporting and performance , I can’t come up with practical advantages (I hush up the fact that you have other advantages, such as data - access consistency, ORM query and SQL (in most cases it is easier to write a query using HQL), data type conversion, etc.)

Answer 4

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