title: JPA query extremely slow when setMaxResults is set
body: <p>In a particular project, I am facing a problem with a typed query in different configuration.</p>
  
  <p>When setting on a TypedQuery a maxResults value greater than the actual number of results returned by a query, getResultList() takes a really long time to execute (sometimes up to 350 seconds to retrieve 10 rows composed of 6 columns).</p>
  
  <p>In contrast, the same query without setMaxResults takes around 15 seconds to execute. The same query with <code>WHERE</code> parameters changed to retrieve a larger number of results is also really fast. </p>
  
  <p>I'm testing all of this in a Junit test class with a resource-local datasource, but the same happens with a JTA datasource on Weblogic 10.3.6 (as well as Jboss EAP 6.1 but I don't think it is relevant).</p>
  
  <p>I tried to play with <code>hibernate.jdbc.fetch_size</code> and <code>hibernate.jdbc.wrap_result_sets</code> in persistence.xml, but it's pretty hard to estimate if there is an improvement: once the query succeeded and thus has been cached by Oracle, it will be fast to execute no matter how I adapt those values (I don't have the privileges to clear Oracle's statement cache).</p>
  
  <p>I noticed that if I remove the <code>ORDER BY</code> from the query, the execution time drops to 50-60 seconds, which is still way too long but still less than 350 seconds. The column is indexed, though (it is a primary key), thus I don't understand why it impacts the performance that much.</p>
  
  <p>I'm building my query with the Criteria API, but I also tried with HQL or native SQL. I'm using <a href="http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaQuery.html#multiselect%28java.util.List%29" rel="nofollow noreferrer">CriteriaQuery#multiSelect</a> to retrieve only the needed fields, and then assign the results to a class with an appropriate constructor (<code>select new ...</code>).</p>
  
  <p>Here is the query as translated by Hibernate:</p>
  
  <pre><code>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&gt;=?
    AND (interview2_.IS_POLLING  IS NULL
    OR interview2_.IS_POLLING     =0)
    ORDER BY message0_.MESG_ID DESC
    )
  WHERE rownum &lt;= ?
  </code></pre>
  
  <p>When I run this in SQLDeveloper, it is fast (as it should be), less than a second generally.</p>
  
  <p>When I debug Hibernate's code, I can see that it is <code>org.hibernate.jdbc.AbstractBatcher.getResultSet(PreparedStatement)</code> that is abnormally slow, on <code>ps.executeQuery()</code>. Should it point me towards a JDBC driver problem or rather towards a database problem?</p>
  
  <p>Thanks in advance for any advice or idea about this situation.</p>
  
  <p>Configuration:</p>
  
  <ul>
  <li>Java 6</li>
  <li>Hibernate 3.6.10 (JPA 2.0) (tried to upgrade to Hibernate 4 and JPA 2.1, no change)</li>
  <li>Oracle database 11g (11.1.0.7.0)</li>
  <li>Oracle jdbc driver 11.2.0.3 (tried with 11.1.0.7.0 and 11.2.0.4, no change).</li>
  <li>hibernate.dialect: Oracle10gDialect.</li>
  </ul>
  
  <p>Possible duplicate: <a href="https://stackoverflow.com/questions/8861332/resultset-next-very-slow-only-when-query-contains-first-rows-or-rownum-restricti">ResultSet.next very slow only when query contains FIRST_ROWS or ROWNUM restriction</a></p>