[ACCEPTED]-Java JDBC ignores setFetchSize?-cursor
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");
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
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.
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.
setFetchSize(...) is in order to provide Pagenation
But 2 in case you just want to limit the number 1 of rows, use this instead:
More Related questions