What is the best practice for sorting data with search alerts, at the business or database level?

Question

This may be a frequently asked question, but so far I have not been able to find a convincing answer.

In my project, I need to swap for a set of approximately 20,000 records, which is a combined result from several tables, and it needs to be sorted differently in different scenarios.

There are currently two options in front of me:

1, do this using the ie. where dl.[row_number] between @index*@size+1 and @index*@ size+@size database storage procedure ie. where dl.[row_number] between @index*@size+1 and @index*@ size+@size ie. where dl.[row_number] between @index*@size+1 and @index*@ size+@size . The problem is that you have to write Store Procs for each sort separately.

2, do it at the Business Logic level, paging and sorting over the result. (ie. skip(), take()) But this is not ideal, because you can get 20,000 entries, but only 10 of them are used

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

Answer 1

keep this logic at the database level.

if you are using a saved process then add it to also include the sort column so you can return the correct set

Answer 2

If it is a multi-user application, there will be problems with scalability if you do not get the result for a single displayed page and make a transaction.

For example, a page-by-page display on a website should fulfill a new request each time a user moves to the next page. An alternative is to keep the full 20K result in the context of a web session that will not scale well.

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