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

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 开发者_C百科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

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

If this is a multiuser application, there will be scalability issues unless you fetch the result for a single display page and commit the transaction.

For example, a paged display on a web site should perform a new query each time the user navigates to the next page. The alternative is to keep the full 20K result in the web session context, which will not scale well.

There is a slight annoyance with the SQL language for sorting, as the column name (or column index) for sorting cannot be parameterized.