CODEWITHSUNDEEP
javaoracle-databasejdbceclipselink
stracktracer
stracktracer

Reputation: 1900

ResultSet.next very slow only when query contains FIRST_ROWS or ROWNUM restriction

I execute a native query using 

entityManager.createNativeQuery(sqlQuery);
query.setMaxResults(maxResults);

List<Object[]> resultList = query.getResultList();

To speed up the query, I thought to include the FIRST_ROWS(n) hint or limiting using WHERE ROWNUM > n.

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.

Upvotes: 4

Views: 6477

Answers (3)

James
James

Reputation: 18379

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 is the default fetch size, so you could try setting that to be bigger.

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

Upvotes: 2

Chris
Chris

Reputation: 21145

Try adding the max rows limit to the SQL directly instead of using setMaxResults, ie add where rownum < maxResults to the sql string. EclipseLink will use rownum in the query for max rows when it creates the SQL, but since you are using your own SQL, it will use the result set to limit rows.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

Do you get different query plans when you include the hint? My assumption is that you do based on your description of the problem.

When you execute a query in Oracle, the database does not generally materialize the entire result set at any point in time (obviously, it may have to if you specify an ORDER BY clause that requires all the data to be materialized before the sort happens). Oracle doesn't actually start materializing data until the client starts fetching data. It runs enough of the query to generate however many rows the client has asked to fetch (which it sounds like is 10 in your case), returns those results to the client, and waits for the client to request more data before continuing to process the query.

It sounds like when the FIRST_ROWS hint is included, the query plan is changing in a way that makes it more expensive to execute. Obviously, that's not the goal of the FIRST_ROWS hint. The goal is to tell the optimizer to generate a plan that makes fetching the first N rows more efficient even if it makes fetching all the rows from the query less efficient. That tends to cause the optimizer to favor things like index scans over table scans where a table scan might be more efficient overall. It sounds like in your case, however, the optimizer's estimates are incorrect and it ends up picking a plan that is just generally less efficient. That frequently implies that some of the statistics on some of the objects your query is referencing are incomplete or incorrect.

Upvotes: 2

Related Questions