For some reason HQL seems more faster than Criteria
If we write a query like
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 <= ?)
Using both HQL and Criteria , then HQL will run much faster.
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.
Another issue i noticed when using HQL , I was using HQL like
from employee emp where emp.id=123
at one place, and HQL like
from employee e where e.id=123
at another place.
Although i was using a query level cache BUT still it was not being cached BECAUSE query cache was not being used here as the key for query cache is the query itself.