[ACCEPTED]-Java JDBC ignores setFetchSize?-cursor

Accepted answer
Score: 28

Try turning auto-commit off:

// make sure autocommit is off
connection.setAutoCommit(false);

 st = connection.createStatement();
 st.setFetchSize(1000);
 System.out.println("start query ");
 rs = st.executeQuery(queryString);
 System.out.println("done query");

Reference

0

Score: 4

I noticed that your use of the API is different 2 from what expressed by Javadoc:

Try passing 1 parameters in this order

  ResultSet.TYPE_FORWARD_ONLY,
  ResultSet.CONCUR_READ_ONLY,
  ResultSet.FETCH_FORWARD
Score: 3

The two queries do entirely different things.

Using 11 the LIMIT clause limits the size of the result 10 set to 10000, while setting the fetch size 9 does not, it instead gives a hint to the 8 driver saying how many rows to fetch at 7 a time when iterating through the result set - which includes all 800k rows.

So 6 when using setFetchSize, the database creates the full 5 result set, that's why it's taking so long.

Edit for clarity: Setting 4 the fetch size does nothing unless you iterate 3 through the result (see Jon's comment), but 2 creating a much smaller result set via LIMIT 1 makes a great difference.

Score: 2

This will depend on your driver. From the 11 docs:

Gives the JDBC driver a hint as to 10 the number of rows that should be fetched from 9 the database when more rows are needed. The 8 number of rows specified affects only 7 result sets created using this statement. If 6 the value specified is zero, then the 5 hint is ignored. The default value is 4 zero.

Note that it says "a hint" - I would 3 take that to mean that a driver can ignore 2 the hint if it really wants to... and it 1 sounds like that's what's happening.

Score: 0

I think setFetchSize(...) is in order to provide Pagenation

But 2 in case you just want to limit the number 1 of rows, use this instead:

st.setMaxRows(1000);

More Related questions