[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");
0
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.
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.