JDBC / Hibernate Fetch Size and Memory Issues

Question

After learning a bit of work, I noticed that the application I'm working on uses the default sample size (which is 10 for Oracle from what I know). The problem is that in most cases, users receive a large amount of data (from several thousand to even hundreds of thousands) and that by default 10 is really a huge bottleneck.

Thus, the obvious conclusion here would be to increase the sample size. At first I thought about setting the default value to 100 and pushing it to 1000 for multiple queries. But then I read on the net that the default value is so small as to prevent memory problems (i.e. when the JVM heap cannot process so much data), should I worry about that?

I haven’t explained it anywhere else. Does this mean that a larger sample size means more overhead in obtaining a result set? Or they just mean that by default I can get 10 entries and then GC them and get 10 more, etc. (Then how can you say that fetching 10000 will simultaneously result in an OutOfMemory exception)? In this case, I do not care, because I still need all the records in memory. In the first case (when a larger set of results means more memory), I assume that I must load the test first.

Answer 1

By setting the sample size, you OutOfMemoryError risk of OutOfMemoryError .

The fact that you need all of these notes in any case is probably not justified. More likely that you need entities reflected by the returned ResultSet s ... Setting the sample size to 10000 means that you are typing 10,000 records represented by JDBC classes. Of course, you do not go through this through your application. First, you transform them into your favorite business logical entities, and then pass them to your business executor logic. Thus, the records forming the first sample array are available to the GC as soon as JDBC retrieves the next sample size.

Typically, this conversion is performed with a small number of bits at a time precisely because of the memory threat mentioned above.

One thing you are absolutely right: you must test performance with clearly defined requirements before tuning.

Answer 2

Thus, the obvious conclusion here would be to make the sample size larger.

Perhaps the equally obvious conclusion should be: "Let's see if we can reduce the number of objects that users return." When Google returns the results, it does it in batches of 25 or 50, sorted by probability, which will be useful to you. If your users return thousands of objects, perhaps you need to think about how to reduce this. Can a database do more work? Are there other operations that can be recorded to eliminate some of these objects? Can the objects themselves be smarter?