Hibernate Query vs Criteria Performance

I recently had a rather strange phenomenon. Had to obtain a count that included joins over multiple tables with different WHERE conditions. I implemented the query first with the criteria API of hibernate. It correctly created the requested prepared SQL statement but was rather slow. Re-implemented then the entire query using HQL. Was rather nasty to do that but the result performed much faster than with the Criteria API. Does anybody know the reason for that behavior? I assumed that the Criteria and HQL framework use 开发者_Go百科the same code base to transform it to SQL.

Here is the query:

select count(*) from R r where r.ISREPLACEDBY = 0 
and r.STATUS='OK' and r.A = ? 
and r.C in 
   (select distinct RC from CX cx where cx.FROMDATE >= ? and cx.FROMDATE <=?)

I guess I finally found the reason. It seems that the criteria api creates new variable names each time a prepared statement is executed. The database (in our case, DB2) calculates then a new query execution plan each time the statement is executed. On the other hand, HQL uses the same variable names, allowing the database to re-use the query execution plans.

Criteria, in theory should have less overhead than an HQL query (except for named queries, which I'll get to). This is because Criteria doesn't need to parse anything. HQL queries are parsed with an ANTLR-based parser and then the resulting AST is turned into SQL. However, with HQL/JPAQL you can define named queries, where the SQL is generated when the SessionFactory starts up. In theory, named queries have less overhead than Criteria. So, in terms of SQL-generation overhead we have:

1. Named HQL/JPAQL Query - SQL generation happens only once.
2. Criteria - No need to parse before generating.
3. (non-named) HQL/JPAQL Query - Parse, then generate. That said, choosing a query technique based on the overhead of parsing and SQL generation is probably a mistake in my opinion. This overhead is typically very small when compared to performing a real query on a real database server with real data. If this overhead does actually show up when profiling the app then maybe you should switch to a named query.

Here are the things I consider when deciding between Criteria and HQL/JPAQL:

• First, you have to decide if you're OK with having a dependency on Hibernate-proprietary API in your code. JPA doesn't have Criteria.
• Criteria is really good at handling many optional search parameters such as you might find on a typical web page with a multi-parameter 'search form'. With HQL, developers tend to tack on where clause expressions with StringBuilder (avoid this!). With Criteria, you don't need to do that.
• HQL/JPAQL can be used for most other things, because the code tends to be smaller and easier for developers to understand.
• Really frequent queries can be turned into named queries if you use HQL. I prefer to do this later, after some profiling.

You can read some additional info here http://tech.puredanger.com/2009/07/10/hibernate-query-cache/

Hibernate Criteria Use reflection to generate SQL Statments

I generally believe that HQL is very near optimal, as it's nearly straight SQL with a few substitutions. I'd assume that the translation from HQL to SQL is just substitution; the Criteria API probably generates HQL to then get transformed. Generally HQL is your best bet.