sorting - database sort vs. programmatic java sort

Question

I want to get data from the database (MySQL) by JPA, I want it sorted by some column value.

So, what is the best practice, to:

• Retrieve the data from the database as list of objects (JPA), then sort it programmatically using some java APIs.

OR

• Let the database sort it by using a sorting select query.

Thanks in advance

Answer 1

If you are retrieving a subset of all the database data, for example displaying 20 rows on screen out of 1000, it is better to sort on the database. This will be faster and easier and will allow you to retrieve one page of rows (20, 50, 100) at a time instead of all of them.

If your dataset is fairly small, sorting in your code may be more convenient if you want implement a complex sort. Usually this complex sort can be done in SQL but not as easily as in code.

The short of it is, the rule of thumb is sort via SQL, with some edge cases to the rule.