Sunday, January 13, 2013

Hibernate Query vs Criteria Performance

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
at one place, and HQL like
from employee e where
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.

No comments: