CODEWITHSUNDEEP
springoracle-databasespring-data-jpalarge-data
Prashant
Prashant

Reputation: 1042

Spring data Oracle JPA performance with pagination

I am looking to retrieve a large dataset with a JpaRepository, backed by Oracle table. The choices are to return a collection (List) or a Page of the entity and then step through the results. Please note - I have to consume every record in this set, exactly once. This is not a "look-for-the-first-one-from-a-large-dataset-and-return" operation.

While the paging idea is appealing, the performance will be horrible (n^2) because for each page queried, oracle will have to pull up previous n-1 pages, making the performance progressively worse as I get deeper in the result set.

My understanding of the List alternative is that the entire result-set will be loaded in memory. For oracle JPA spring does not have a backing result-set.

So here are my questions

  1. Is my understanding of the way List works with Spring Data correct? If it's not then I will just use List.
  2. If I am correct, is there an alternative that streams Oracle/JPA result-sets?
  3. Is there a third way that I am not aware of.

Upvotes: 1

Views: 3708

Answers (1)

Cepr0
Cepr0

Reputation: 30329

Pageable methods in SDJ call additional select count(*) from ... every request. I think this is reason of the problem. To avoid it you can use Slice instead of Page as return parameter, for example:

Slice<User> getAllBy(Pageable pageable);

Or you can use even List of entities with pagination:

List<User> getAllBy(Pageable pageable);

Additional info

Upvotes: 1

Related Questions