Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Why has setting a large jdbc fetch size a bad impact on Performance ?

Posted on 2008-11-19
Medium Priority
Last Modified: 2013-12-19

Im doing some tuning with the jdbc driver of oracle and i remarked, that setting a large fetch size is not always a good way to improve the performance of a client.
Can anyone explain me why is this so  !

Thank you in advance.
Question by:finaris
  • 3
  • 2
  • 2
LVL 27

Accepted Solution

sujith80 earned 1000 total points
ID: 22992747
Large size means more data, leads to more network traffic and causes latency. Client need to cache more data.
Optimal fetch sizes are in the ranges of 100-500. (Of course varies with the volume of data being fetched.)
LVL 27

Assisted Solution

Tolomir earned 1000 total points
ID: 22992756
It's all about memory allocation. The fetched data has to be stored in memory before it can be processed.

In the worst case you fetch more data before your client is able to process the input, do this 10 times and you eat up all available memory.

So a better way is always to fetch just so much data that can be processed before the next data arrives. It's like traffic jam, as long as there is just so much traffic the needle eye can handle all cars can drive fast, with more cars they have to wait before they can get through, the result is traffic jam.

Author Comment

ID: 22993425
I still did not understand the drawback of setting the fetchSize as large as possible.

Suppose we have a table with 1000 rows and i want to display these rows in a graphical table.

Alternative A : Set fetchSize to 1000. This leads to copy the rows to the memory in 1 RDBMS access.
Alternative B  : Set fetchSize to 10.  This leads to copy the rows to the memory in 100 RDBMS access.

Under which circumstance can be alternative B less performant as alternative A ?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 27

Expert Comment

ID: 22993559
set it to 250 to have 4 fetches - leading to the best performance

Author Comment

ID: 22993773
@Tolomir: why not one fetch ? 4 fetches are less performant, since for each fetch a request has to be sent to the RDBMS.
LVL 27

Expert Comment

ID: 22994259
The fetch means no traffic (maybe 1 kb), but the result can be easier handled. See again my traffic jam example, above...
LVL 27

Expert Comment

ID: 23001505
>> 4 fetches are less performant, since for each fetch a request has to be sent to the RDBMS.

Four fetch calls are made to the RDBMS, true. But it doesnt establish 4 connections and it doesn't go through 4 query processing steps. It uses the same opened cursor.

You are aksed to carry 200kg of something from point A to point B, do you think dragging the whole thing from A to B get you there faster or 10 trips of 20kg each?

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question