What is the best practice of sorting data with paging, on Business Tier or Database Tier?

Question

It might be a frequently asked question, however so far i couldn't find a convincing answer.

In my project, I need to do paging for a set of around 20,000+ records which is a joined result from multiple tables, and it need to be sorted differently in different scenarios.

Currently, there are 2 options in front of me:

1, do it by using store procedure on database tier ie. where dl.[row_number] between @index*@size+1 and @index*@size+@size. The problem of doing it is, you will have to write Store Procs for each sorting seperately.

2, do it on Business Logic tier, and do paging and sorting above the result. (ie. skip(), take()) But it is not ideal neither, since you may end up retrieving 20,000 records, but only 10 of them is used

Is there any standard best practice available for this? thanks in advance

Answer 1

keep this logic at the database layer.

if using the stored proc, then extend it to also include the sort column(s) so you can return the right set