[Database] General paging stored procedure page 1/5

Paging stored procedures can be implemented in four ways: Row count, cursor, ascending-descending, and subquery

I remember someone tested the efficiency of these four methods from the best performance to the worst order-row count, cursor, ascending-descending, and subquery.

The following are some of the information I have collected for your reference.
QUOTE:
Address: http://www.codeproject.com/aspnet/PagingLarge.asp
Author: Jasmin Muharemovic
Translator: Tony Qu
Download:

Introduction
In Web applications, paging a large database result set is a well-known issue. Simply put, you do not want all query data to be displayed on a single page, so it is more appropriate to display data with pages. Although this is not a simple task in the traditional asp, in asp.net, The DataGrid Control simplifies this process to only a few lines of code. Therefore, in asp.net, paging is simple, but the default DataGrid paging event will read all the records from the database and put them in the asp.net web application. When your data is more than 1 million, this will cause serious performance problems (if you do not believe it, You can execute a query in your application, then, check the memory consumption of aspnet_wp.exe in the task manager. This is why you need to customize paging behavior. This ensures that only the data records required for the current page are obtained.

There are many articles and posts on this issue on the Internet, and there are some mature solutions. The purpose of this article is not to show you a stored procedure that can solve all problems, but to optimize existing methods and provide you with an application that can be tested, in this way, you can develop based on your own needs. The following is a good start. It contains many different methods and provides some performance test results.

How to paging through Recordset?

However, I am not very satisfied with most of the above content. First, half of the methods use traditional ADO, which is clearly written for the "old" asp. Some of the remaining methods are SQL Server Stored Procedures, and some of them cannot be used because of slow time, just as the performance results you saw at the end of the article, however, some of them caught my attention.

Generalization
I decided to carefully analyze the three methods: temporary table, dynamic SQL, and Rowcount ). In the following article, I prefer to call the second method (ascending-descending) Asc-Desc method. I don't think dynamic SQL is a good name, because you can also apply dynamic SQL logic to another method. The common problem with all these stored procedures is that you have to estimate which Columns are to be sorted, not just primary key Columns, this may cause a series of problems-for each query, You need to display it by page, that is, you must have many different paging queries for each different sort column, this means that you either perform different stored procedures for each sorting column (no matter which paging method is used), or you must place this function in a stored procedure with the help of dynamic SQL. These two methods have minor impact on performance, but they increase maintainability, especially when you need to use this method to display different queries. Therefore, in this article, I will try to use dynamic SQL to summarize all the stored procedures, but for some reason, we can only implement some versatility, therefore, you must write an independent Stored Procedure for complex queries.

The second problem that allows all sorting fields, including primary key columns, is that if the columns are not properly indexed, these methods will not help either. In all these methods, sorting is required for a paging source. For large data tables, the cost of sorting by non-indexed columns is negligible. In this case, because the corresponding time is too long, all stored procedures cannot be used in actual conditions. (The corresponding time varies from a few seconds to a few minutes, depending on the table size and the first record to be obtained ). Indexes of other columns may cause extra performance problems that are not expected. For example, if you import a large amount of data every day, it may become slow.