java - 设置 setMaxResults 时 JPA 查询速度极慢

Question

在一个特定的项目中，我遇到了不同配置中类型化查询的问题。

当在 TypedQuery 上设置 maxResults 值大于查询返回的实际结果数时，getResultList() 的执行时间会非常长(有时需要长达 350 秒才能检索由 6 列组成的 10 行)。

相比之下，没有 setMaxResults 的相同查询大约需要 15 秒才能执行。与 WHERE 相同的查询更改参数以检索大量结果也非常快。

我正在使用本地资源数据源的 Junit 测试类中测试所有这些，但 Weblogic 10.3.6 上的 JTA 数据源也会发生同样的情况(以及 Jboss EAP 6.1，但我不认为它是相关)。

我尝试玩hibernate.jdbc.fetch_size和hibernate.jdbc.wrap_result_sets在 persistence.xml 中，但很难估计是否有改进:一旦查询成功并被 Oracle 缓存，无论我如何调整这些值，执行速度都会很快(我没有权限)清除 Oracle 的语句缓存)。

我注意到，如果删除 ORDER BY从查询来看，执行时间下降到 50-60 秒，虽然仍然太长，但仍然少于 350 秒。不过，该列已建立索引(它是主键)，因此我不明白为什么它对性能影响这么大。

我正在使用 Criteria API 构建查询，但我也尝试使用 HQL 或 native 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 驱动程序问题还是数据库问题？

预先感谢您提供有关此情况的任何建议或想法。

配置:

• Java 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，没有变化)。
• hibernate.dialect:Oracle10gDialect。

可能的重复: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 会执行“绑定(bind)参数查看”之类的操作 - 它甚至会查看您提供的边界的实际值，如果它认为成本更低，它可能会决定采用不同的查询计划。 ...

您是否意识到 ORDER BY 是在 where 子句之后计算的？这意味着，您不会根据顺序获得前 n 条记录，而是获得随后排序的 n 条随机记录......