Java JDBC ignores setFetchSize?

Question

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

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

Try passing parameters in this order

  ResultSet.TYPE_FORWARD_ONLY,
  ResultSet.CONCUR_READ_ONLY,
  ResultSet.FETCH_FORWARD

Answer 2

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.

Answer 3

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