Java JDBC ignores setFetchSize ?

Question

Hi, I'm using the following code

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

The query return a lot of (800k) rows and it take a large time (~2m) between printing "start query" and "done query". When I manually put an "limit 10000" in my query there's no time between "start" and "done". Processing the results takes time so I guess it's overall faster if it just fetches 1k rows from the database, processes those and when it's running out of rows it can get new ones in the background.

The ResultsSet.CONCUR_READ_ONLY etc where my last guess; am I missing something?

(it's a postgresql 8.3 server)

Answer 1

This will depend on your driver. From the docs:

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

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

Answer 2

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

Answer 3

The two queries do entirely different things.

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

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

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