CODEWITHSUNDEEP
spring-data-jpa
Akash Verma
Akash Verma

Reputation: 847

Which sorting method is efficient among sorting using JPA order by or sorting using collection sort() method?

I have a table called 'userProfileEmployment'. It has a start_date col. which stores values in yyyy-mm-dd format. From this I have to fetch the list of employments in Asc order.

Now, I have two approaches for it 1. fetch directly sorted rows from DB through JPA query. 2. fetch the rows as it is from DB, store it in a List and then sort it using collection sort method

I am sharing Code for approach 1:- List employmentList = userProfileEmploymentRepository.findAllByProfileIdSorted(userProfileId);

Snippet from UserProfileEmploymentRepository.java class:

@Query("select upe from UserProfileEmployment upe where upe.profileId = :profileId and (upe.deleted = 0 or upe.deleted is null) order by upe.startDate")
    List<UserProfileEmployment> findAllByProfileIdSorted(@Param("profileId") Long profileId);

Now we get the sorted output from both approaches. So my question is which one of the two is better. Does sorting using the order by clause is more costly or sorting using collection sort method is more better

Upvotes: 0

Views: 414

Answers (1)

Punit Tiwan
Punit Tiwan

Reputation: 106

Sorting in DB is preferable than in-memory(i.e. Collections) in most of the cases.

Firstly because DB's sorting is always optimized to handle large data set and you can be even optimize it better by using indexes.

Secondly, if later you want to start returning paginated data(i.e. slice of whole data) then you can load only those slices by using pageable from DB and save heap memory.

Upvotes: 2

Related Questions