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)

setFetchSize actually works well with the PostgreSQL driver, it just does a very different thing than you expect it to do. See my answer. — Henning, Sep 23 '09 at 20:38

score 28 · Answer 1 · answered Sep 23 '09 at 19:36

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

answered Sep 23 '09 at 19:36

dogbane

266,786
75
396
414

2

Exactly. setFetchSize is not working in autoCommit mode with PostgresSQL JDBC driver. – Danubian Sailor Jun 19 '12 at 10:10
2

It's worth noting that you probably need to turn autocommit back on before closing the connection, if you're using connection pooling, and other parts of the app rely on autocommit. Otherwise you may end up with random data loss when not explicitly committing. – Matt Ball Jul 23 '12 at 16:57
2

More details on this PostgreSQL JDBC restriction can be found at http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor. – Alex Oct 08 '14 at 15:15
FWIW autoCommit is "on" by default, so make sure to vary it off...see also the other comments. – rogerdpack Aug 18 '17 at 20:33

score 4 · Answer 2 · edited Oct 28 '12 at 14:01

4

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

edited Oct 28 '12 at 14:01

Vivek

1,640
1
17
34

answered Mar 06 '12 at 11:32

Wishper

343
1
3
11

2

These 3 keys, along with setting conn.setAutoCommit(false) did the trick for me – Peter Jan 28 '15 at 22:57

Henning · Answer 3 · 2009-09-23T21:45:43.303

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.

edited Sep 23 '09 at 21:45

answered Sep 23 '09 at 20:05

Henning

16,063
3
51
65

1

But he *isn't* iterating through the result set. The idea is that *while* you iterate through the result set, ideally it should fetch 1000 results over the network, then you can process those, and when you get to the 1001st it will then fetch the next 1000 etc. – Jon Skeet Sep 23 '09 at 21:11
Of course. The difference in speed is because of differenr sizes of the result sets, 10000 vs. 800000. – Henning Sep 23 '09 at 21:38
"So when using setFetchSize, the database creates the full result set, that's why it's taking so long." was what I needed. thanks. – cagcowboy Jul 22 '21 at 12:20

score 2 · Answer 4 · answered Sep 23 '09 at 19:16

2

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.

answered Sep 23 '09 at 19:16

Jon Skeet

1,421,763
867
9,128
9,194

Yeah I read that but couldn't believe that JDBC + postgreSQL would ignore that, it's around for ages. – kresjer Sep 23 '09 at 19:17
@kresjer: Its postgresql so you should have access to the source for the DB and the JDBC drivers. You could use it to figure out what is actually going on ... – Stephen C Sep 23 '09 at 22:24
NB that postgres (if autocommit is off so fetch size is working at all) setting the fetch size to 0 apparently makes it cache "everything" by default (guess that's how they interpret "ignoring" a hint) with oracle the default is 10 apparently http://docs.oracle.com/cd/A97335_02/apps.102/a83724/resltse5.htm – rogerdpack Jan 22 '15 at 00:24

Adir Dayan · Answer 5 · 2020-09-08T07:01:40.380

0

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

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

st.setMaxRows(1000);

edited Sep 08 '20 at 07:01

answered Sep 07 '20 at 19:49

Adir Dayan

1,308
13
21

Java JDBC ignores setFetchSize?

5 Answers5

Linked

Related