Hibernate查询与条件绩效 [英] Hibernate Query vs Criteria Performance

问题描述

我最近有一个相当奇怪的现象。必须获得一个计数，其中包括使用不同WHERE条件连接多个表的计数。我首先使用hibernate的标准API实现了查询。它正确地创建了请求的准备好的SQL语句，但速度很慢。然后使用HQL重新实现整个查询。相当讨厌这样做，但结果表现比Criteria API要快得多。有人知道这种行为的原因吗？我认为Criteria和HQL框架使用相同的代码库将其转换为SQL。

以下是查询：

 从R r中选择count（*），其中r.ISREPLACEDBY = 0 
和r.STATUS ='OK'和rA =？ 
和rC在
中（从CX cx中选择不同的RC，其中cx.FROMDATE> =？和cx.FROMDATE< =？）
  

解决方案

我猜我终于找到原因了。似乎标准API会在每次执行准备好的语句时创建新的变量名称。数据库（在我们的例子中是DB2）每次执行语句时计算一个新的查询执行计划。另一方面，HQL使用相同的变量名称，允许数据库重用查询执行计划。

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

解决方案

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.

这篇关于Hibernate查询与条件绩效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！