仅当查询包含FIRST_ROWS或ROWNUM限制时，ResultSet.next才会非常慢 [英] ResultSet.next very slow only when query contains FIRST_ROWS or ROWNUM restriction
问题描述
我使用
entityManager.createNativeQuery(sqlQuery);
query.setMaxResults(maxResults);
List<Object[]> resultList = query.getResultList();
为加快查询速度，我认为应包含FIRST_ROWS(n)提示或使用WHERE ROWNUM > n进行限制.
To speed up the query, I thought to include the FIRST_ROWS(n) hint or limiting using WHERE ROWNUM > n.
使用检测工具，我确实发现OraclePreparedStatement.executeQuery确实更快，但是EJBQueryImpl.getResultList花了很多时间，导致整体性能非常差.详细了解一下，我发现每执行10次ResultSet.next()调用，所需的时间与executeQuery本身()差不多.当我忽略查询提示或ROWNUM条件时，这种奇怪的行为就会停止，然后对resultet.next的第10次调用要比其他调用低一些，但只有2ms而不是3秒.
Using instrumentation, I see that indeed OraclePreparedStatement.executeQuery is faster, but a lot more time is spent in EJBQueryImpl.getResultList leading to an overall very poor performance. Looking more into detail, I see that every 10th call of ResultSet.next() takes about as long as executeQuery itself(). This strange behaviour stops when I leave out the query hint or the ROWNUM condition, then every 10th call of resultset.next is somewhat lower than the others, but only 2ms instead of 3 seconds.
推荐答案
类似的声音使JDBC executeQuery更快，但JDBC ResultSet则更慢.您可以更快地执行查询，但获取数据的速度较慢.似乎是JDBC问题，而不是EclipseLink，如果实际获取数据，则可以通过原始JDBC获得相同的结果.
Sounds like you made JDBC executeQuery faster but JDBC ResultSet next slower. You made executing the query faster but fetching the data slower. Seems to be a JDBC issue, not EclipseLink, you would get the same result through raw JDBC if you actually fetched the data.
10是默认的提取大小，因此您可以尝试将其设置为更大.
10 is the default fetch size, so you could try setting that to be bigger.
这篇关于仅当查询包含FIRST_ROWS或ROWNUM限制时，ResultSet.next才会非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
