ResultSet.next is very slow only when the request contains FIRST_ROWS or ROWNUM constraints

Question

I fulfill my own request with

entityManager.createNativeQuery(sqlQuery); query.setMaxResults(maxResults); List<Object[]> resultList = query.getResultList(); 

To speed up the query, I thought of turning on the FIRST_ROWS(n) tooltip or limiting with WHERE ROWNUM > n .

Using the toolkit, I see that OraclePreparedStatement.executeQuery really works faster, but much more time is spent on EJBQueryImpl.getResultList , which leads to overall very poor performance. Looking in more detail, I see that every 10th call to ResultSet.next() takes about the same time as executeQuery (). This strange behavior stops when I do not mention the query hint or the ROWNUM condition, and then every 10th call to resultset.next is slightly lower than the rest, but only 2 ms instead of 3 seconds.

Answer 1

Do you have different query plans when you turn on the prompt? My guess is that you are based on your description of the problem.

When you execute a query in Oracle, the database usually does not materialize the entire result set at any given time (obviously, it might be necessary if you specify an ORDER BY that requires all the data to be materialized before the view happens). Oracle does not actually begin to materialize data until the client begins to receive data. It satisfies the requests enough to generate as many rows as the client asked to retrieve (in your case, it looks like 10), returns these results to the client and expects the client to request more data before continuing with the processing of the request.

It appears that when the FIRST_ROWS hint is FIRST_ROWS on, the query plan changes in a way that makes it more expensive. Obviously, this is not the purpose of the FIRST_ROWS hint. The goal is to tell the optimizer to create a plan that makes the selection of the first N rows more efficient, even if it makes the selection of all rows from the query less efficient. This causes the optimizer to prefer things like index scans through table scans, where table scans can be more efficient overall. It seems that in your case, the optimizer’s estimates are incorrect, and in the end he chooses a plan that is usually less effective. This often implies that some statistics for some of the objects referenced by your request are incomplete or incorrect.

Answer 2

It looks like you made JDBC executeQuery faster, but the JDBC ResultSet next slower. You executed the request faster, but slower loading the data. This seems to be a JDBC problem, not EclipseLink, you will get the same result through raw JDBC if you really retrieved the data.

10 is the default sample size, so you can try to establish that it is larger.

See, http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/config/QueryHints.html#JDBC_FETCH_SIZE

Answer 3

Try adding the maximum row restriction for SQL directly instead of using setMaxResults, i.e. add where rownum <maxResults for the sql row. EclipseLink will use rownum in the query for maximum rows when creating SQL, but since you use your own SQL, it will use a result set to limit the rows.