Hibernate Query vs Criteria Performance

Question

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 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 <=?)

Answer 1

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.