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


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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
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.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
finarisAuthor Commented:
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 ?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

set it to 250 to have 4 fetches - leading to the best performance
finarisAuthor Commented:
@Tolomir: why not one fetch ? 4 fetches are less performant, since for each fetch a request has to be sent to the RDBMS.
The fetch means no traffic (maybe 1 kb), but the result can be easier handled. See again my traffic jam example, above...
SujithData ArchitectCommented:
>> 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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.