If you’re a user or follower of Hibernate, you probably saw Gavin King’s excellent post on the JDBC fetch size issue tackled from a Hibernate perspective.
I posted some performance figures on my original post, which demonstrated how you can dramatically improve the consumption rate of rows from the database with an appropriate fetch size. Because the focus there was database performance, I ran the Java program directly on the database server.
But even with those dramatic figures, they don’t paint the true picture of the importance of fetch size, because in a real world setup, your Java programs will be running on an application server, not the database server and those two things are separated by a network.
Lets introduce a network to see how things change. I’m still using the same program from the original post, but I’m adjusting some source set numbers as I’ll note below.
Test 1 – Database Server and Application Server separated by 1Gb Ethernet
C:\tmp>java FetchSize2 10
Creating test table
Done.
Setting JDBC Statement default fetch size to 10
Querying all rows
ResultSet fetch size 10
Rows fetched 407390
Duration 86456 ms
Throughput 4712 rows/sec
C:\tmp>java FetchSize2 100
Creating test table
Done.
Setting JDBC Statement default fetch size to 100
Querying all rows
ResultSet fetch size 100
Rows fetched 407390
Duration 9820 ms
Throughput 41485 rows/sec
C:\tmp>java FetchSize2 1000
Creating test table
Done.
Setting JDBC Statement default fetch size to 1000
Querying all rows
ResultSet fetch size 1000
Rows fetched 407390
Duration 1335 ms
Throughput 305161 rows/sec
C:\tmp>java FetchSize2 10000
Creating test table
Done.
Setting JDBC Statement default fetch size to 10000
Querying all rows
ResultSet fetch size 10000
Rows fetched 407390
Duration 319 ms
Throughput 1277084 rows/sec
C:\tmp>java FetchSize2 100000
Creating test table
Done.
Setting JDBC Statement default fetch size to 100000
Querying all rows
ResultSet fetch size 100000
Rows fetched 407390
Duration 189 ms
Throughput 2155502 rows/sec
C:\tmp>java FetchSize2 1000000
Creating test table
Done.
Setting JDBC Statement default fetch size to 1000000
Querying all rows
ResultSet fetch size 1000000
Rows fetched 407390
Duration 75 ms
Throughput 5431866 rows/sec
C:\tmp>java FetchSize2 10000000
Creating test table
Done.
Setting JDBC Statement default fetch size to 10000000
Querying all rows
ResultSet fetch size 10000000
Rows fetched 407390
Duration 75 ms
Throughput 5431866 rows/sec
Note 1:
- Because there is a network involved, I only built a source set of rows of 400,000, so please don’t compare the numbers to the original post (which was fetching millions). The aim here is to compare the relative times across fetch sizes.
- Before you race out and set your fetch size to 1000000 to process millions of rows in your Java program, please take a moment and ask: “Why am I moving millions of rows out of the database?“. There is surely a better method here.
Test 2 – Database Server and Application Server separated by 16,000km (My home in Perth to a database on the east coast the USA, with who knows how many pieces of network infrastructure between them!)
Note 2: Because I’m impatient, I dropped the source data down a single copy of ALL_OBJECTS, so it was only ~80,000 rows here, a factor of 5 smaller than above.
C:\tmp>java FetchSize2 100
Creating test table
Done.
Setting JDBC Statement default fetch size to 100
Querying all rows
ResultSet fetch size 100
Rows fetched 82106
Duration 180214 ms
Throughput 455 rows/sec
C:\tmp>java FetchSize2 10000
Creating test table
Done.
Setting JDBC Statement default fetch size to 10000
Querying all rows
ResultSet fetch size 10000
Rows fetched 82109
Duration 6341 ms
Throughput 12948 rows/sec
C:\tmp>java FetchSize2 100000
Creating test table
Done.
Setting JDBC Statement default fetch size to 100000
Querying all rows
ResultSet fetch size 100000
Rows fetched 82109
Duration 21 ms <<< that clearly can't be right. Investigation to follow
Throughput 3909952 rows/sec
C:\tmp>java FetchSize2 10000
Creating test table
Done.
Setting JDBC Statement default fetch size to 10000
Querying all rows
ResultSet fetch size 10000
Rows fetched 82109
Duration 4706 ms
Throughput 17447 rows/sec
It is most likely your network architecture is better than these two extremes, but as you can see, a little bit of knowledge on the fetch size can go a long way toward better query performance.
Learning is not a spectator sport 
Got some thoughts? Leave a comment