设置setMaxResults时，java JPA查询速度非常慢

Question

在一个特定的项目中，我面临一个不同配置的类型化查询问题

在TypedQuery上设置大于查询返回的实际结果数的maxResults值时，getResultList（）执行需要很长时间（有时检索由6列组成的10行需要350秒）

相比之下，不带setMaxResults的同一查询执行大约需要15秒。同样的查询，通过更改WHERE参数来检索更多的结果，速度也非常快

我正在使用资源本地数据源在Junit测试类中测试所有这些，但Weblogic 10.3.6（以及Jboss EAP 6.1）上的JTA数据源也会发生同样的情况，但我认为这与此无关

我试图在持久性中使用hibernate.jdbc.fetch_size和hibernate.jdbc.wrap_result_sets。xml，但很难估计是否有改进：一旦查询成功并因此被Oracle缓存，无论我如何调整这些值（我没有清除Oracle语句缓存的特权），执行都会很快

我注意到，如果我从查询中删除ORDER BY，执行时间将下降到50-60秒，这仍然太长，但仍然少于350秒。不过，该列是索引的（它是一个主键），因此我不明白它为什么会对性能产生如此大的影响

我正在使用CriteriaAPI构建查询，但我也尝试使用HQL或本机SQL。我使用CriteriaQuery#multiSelect仅检索所需的字段，然后将结果分配给具有适当构造函数的类（select new ...）

以下是由Hibernate翻译的查询：

SELECT * FROM
  (SELECT message0_.MESG_ID  AS col_0_0_,
    message0_.CREATION_DATE  AS col_1_0_,
    messagetyp1_.MSGT_ID     AS col_2_0_,
    message0_.MESSAGE_NAME   AS col_3_0_,
    interview2_.INVW_ID      AS col_4_0_,
    interview2_.IVWT_ID      AS col_5_0_,
    party3_.LICENSE_USERNAME AS col_6_0_
  FROM  message0_
  LEFT OUTER JOIN S399_MESSAGE_TYPES messagetyp1_
  ON message0_.MSGT_ID=messagetyp1_.MSGT_ID
  LEFT OUTER JOIN S399_INTERVIEWS interview2_
  ON message0_.INVW_ID=interview2_.INVW_ID
  LEFT OUTER JOIN S399_PARTIES party3_
  ON message0_.PRTY_ID          =party3_.PRTY_ID
  WHERE message0_.CREATION_DATE>=?
  AND (interview2_.IS_POLLING  IS NULL
  OR interview2_.IS_POLLING     =0)
  ORDER BY message0_.MESG_ID DESC
  )
WHERE rownum <= ?

当我在SQLDeveloper中运行它时，速度很快（应该如此），通常不到一秒钟

当我调试Hibernate的代码时，我可以看到org.hibernate.jdbc.AbstractBatcher.getResultSet(PreparedStatement)异常缓慢，在ps.executeQuery()上。它应该把我引向JDBC驱动程序问题还是数据库问题

提前感谢您对这种情况的任何建议或想法

配置：

• 爪哇6
• Hibernate 3.6.10（JPA 2.0）（尝试升级到Hibernate 4和JPA 2.1，没有更改）
• Oracle数据库11g（11.1.0.7.0）
• Oracle jdbc驱动程序11.2.0.3（在11.1.0.7.0和11.2.0.4中试用过，没有变化）
• 冬眠。方言：Oracle10g方言

可能重复：ResultSet.next very slow only when query contains FIRST_ROWS or ROWNUM restriction

Answer 1

您可以尝试添加/*+ FIRST_ROWS */提示。如果没有帮助，你就必须找出Hibernate实际执行的是什么

更新：您的查询需要查看多少数据（以MB为单位）？15秒的响应时间是否合理？你能为这个问题提供一个“解释计划”吗

看看这个查询，似乎应该在message0_.CREATION_DATE上有一个索引。您可以尝试在where子句中提供一个上限。就像汤姆·凯茨经常说的。。。想想看！如果在过去和现在的某个日期之间查询数据，但没有传递上限（SYSTIMESTAMP）——基本上，您希望数据库知道在日期列上使用该索引仍然是值得的。Oracle会做“绑定参数窥视”之类的事情——它甚至会查看您提供的边界的实际值，如果它认为这会降低成本，它可能会决定使用不同的查询计划

你知道ORDER BY是在where子句之后计算的吗？这意味着，您不会根据order by获得前n条记录，而是n条随机记录，然后这些记录被排序