High-performance paging Implementation Analysis

Let's talk about the implementation method:
1. Assume that there is an indexed primary key field ID (integer type) in the Table. We will retrieve data by PAGE based on this field.
2. Place the page size in @ PageSize.
3. The current page number is stored in @ CurrentPage.
4. How can we make the record pointer quickly scroll to the row starting with the data we want to fetch? This is the key! With Set RowCount, we can easily implement it.
5. If we successfully scroll the record pointer to the row starting with the data we want to fetch, and then record the value of the Record ID field in that row, then, using Top and conditions, we can easily get the data of a specified page. Of course, with Set RowCount, do we still use Top?
Let's see how Set Rowcount helps us:

Copy codeThe Code is as follows:
Declare @ ID int
Declare @ MoveRecords int

-- @ CurrentPage and @ PageSize are input parameters
Set @ MoveRecords = @ CurrentPage * @ PageSize + 1

-- The following two rows are used to quickly scroll to the row of the data we want to fetch and record the ID.
Set Rowcount @ MoveRecords
Select @ ID = ID from Table1 Order by ID

Set Rowcount @ PageSize
-- Most hate to reduce the trouble *, but it is used for convenience.
Select * From Table1 Where ID >=@ ID Order By ID
Set Rowcount 0

You can try it out. In a table with 100th million records, you can flip pages to 100 pages (entries per page) to see how fast it is!

Because there are usually many reverse sorting, the above is also very easy to change.
Change Order by ID to Order by ID DESC
Change Where ID >=@ ID Order By ID to Where ID <= @ ID Order By ID DESC
You can.
Copy codeThe Code is as follows:
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo]. [Tag_Page_Name_Select]
-- Input the maximum number of records and the current page number.
@ MaxPageSize int,
@ PageNum int,
-- Set an output parameter to return the total number of records for the paging list
@ Count int output
AS
BEGIN
Set nocount on;

DECLARE
-- Define sorting name Parameters
@ Name nvarchar (50 ),
-- Define the cursor position
@ Cursor int
-- Obtain the total number of records first
Select @ Count = count (tag_Name)
FROM [viewdatabase0716]. [dbo]. [view_tag];
-- Define the position where the cursor needs to begin
Set @ Cursor = @ MaxPageSize * (@ PageNum-1) + 1
-- If the cursor is greater than the total number of records, place the cursor at the beginning of the last page.
IF @ Cursor> @ Count
BEGIN
-- If the last page is equal to the maximum number of records each time, the last page is returned.
IF @ Count % @ MaxPageSize = 0
BEGIN
IF @ Cursor> @ MaxPageSize
Set @ Cursor = @ Count-@ MaxPageSize + 1
ELSE
Set @ Cursor = 1
END
-- Otherwise, the remaining records on the last page will be returned.
ELSE
Set @ Cursor = @ Count-(@ Count % @ MaxPageSize) + 1
END
-- Point the pointer to the beginning of the page
Set Rowcount @ Cursor
-- Get the start position of the record
Select @ Name = tag_Name
FROM [viewdatabase0716]. [dbo]. [view_tag]
OrDER BY tag_Name;
-- Set the start position
Set Rowcount @ MaxPageSize
-- Get this page record
Select *
From [viewdatabase0716]. [dbo]. [view_tag]
Where tag_Name >=@ Name
Order By tag_Name

Set Rowcount 0
END


Paging Control
Copy codeThe Code is as follows:
Using System. Data;
Using System. Configuration;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using System. Text;

/// <Summary>
/// Extended connection string
/// </Summary>
Public class ExStringBuilder
{
Private StringBuilder InsertString;
Private StringBuilder PageString;
Private int PrivatePageNum = 1;
Private int PrivateMaxPageSize = 25;
Private int PrivateMaxPages = 10;
Private int PrivateCount;
Private int PrivateAllPage;
Public ExStringBuilder ()
{
InsertString = new StringBuilder ("");
}
/// <Summary>
/// Obtain the generated HTML
/// </Summary>
Public string GetHtml
{
Get
{
Return InsertString. ToString ();
}
}
/// <Summary>
/// Obtain the generated page HTML
/// </Summary>
Public string GetPageHtml
{
Get
{
Return PageString. ToString ();
}
}
/// <Summary>
/// Set or obtain the current page number
/// </Summary>
Public int PageNum
{
Get
{
Return PrivatePageNum;
}
Set
{
If (value> = 1)
{
PrivatePageNum = value;
}
}
}
/// <Summary>
/// Set or obtain the maximum number of minutes
/// </Summary>
Public int MaxPageSize
{
Get
{
Return PrivateMaxPageSize;
}
Set
{
If (value> = 1)
{
PrivateMaxPageSize = value;
}
}
}
/// <Summary>
/// Set or obtain the maximum number of pages displayed each time
/// </Summary>
Public int MaxPages
{
Get
{
Return PrivateMaxPages;
}
Set
{
PrivateMaxPages = value;
}
}
/// <Summary>
/// Set or retrieve the total number of data items
/// </Summary>
Public int DateCount
{
Get
{
Return PrivateCount;
}
Set
{
PrivateCount = value;
}
}
/// <Summary>
/// Obtain the total number of pages of data
/// </Summary>
Public int AllPage
{
Get
{
Return PrivateAllPage;
}
}
/// <Summary>
/// Initialize the page
/// </Summary>
Public void Pagination ()
{
PageString = new StringBuilder ("");
// Obtain the total number of pages
PrivateAllPage = (int) Math. Ceiling (decimal) PrivateCount/(decimal) PrivateMaxPageSize );
// Prevent the superscript or subscript from crossing the border
If (PrivatePageNum> PrivateAllPage)
{
PrivatePageNum = PrivateAllPage;
}
// Scroll cursor paging Mode
Int LeftRange, RightRange, LeftStart, RightEnd;
LeftRange = (PrivateMaxPages + 1)/2-1;
RightRange = (PrivateMaxPages + 1)/2;
If (PrivateMaxPages> = PrivateAllPage)
{
LeftStart = 1;
RightEnd = PrivateAllPage;
}
Else
{
If (PrivatePageNum <= LeftRange)
{
LeftStart = 1;
RightEnd = LeftStart + PrivateMaxPages-1;
}
Else if (PrivateAllPage-PrivatePageNum <RightRange)
{
RightEnd = PrivateAllPage;
LeftStart = RightEnd-PrivateMaxPages + 1;
}
Else
{
LeftStart = PrivatePageNum-LeftRange;
RightEnd = PrivatePageNum + RightRange;
}
}

// Generate page number list statistics
PageString. Append (...);

StringBuilder previusstring = new StringBuilder ("");
// If on the first page
If (PrivatePageNum> 1)
{
...
}
Else
{
...
}
// If the first page is displayed
If (PrivatePageNum> PrivateMaxPages)
{
...
}
Else
{
...
}
PageString. Append (PreviousString );
// Generate the intermediate page
For (int I = LeftStart; I <= RightEnd; I ++)
{
// When the current page
If (I = PrivatePageNum)
{
...
}
Else
{
...
}
}
StringBuilder LastString = new StringBuilder ("");
// If the last page
If (PrivatePageNum <PrivateAllPage)
{
...
}
Else
{
...
}
// If the last group
If (PrivatePageNum + PrivateMaxPages) <PrivateAllPage)
{
...
}
Else
{
...
}
PageString. Append (LastString );
}
/// <Summary>
/// Generate a Tag classification table
/// </Summary>
Public void TagTable (ExDataRow myExDataRow)
{
InsertString. Append (...);
}

Call method:
// Get the page setting and put it into the Session
ExRequest myExRequest = new ExRequest ();
MyExRequest. PageSession ("Tag _", new string [] {"page", "size "});
// Generate Tag pagination
ExStringBuilder Tag = new ExStringBuilder ();
// Set how many records are displayed each time
Tag. MaxPageSize = Convert. ToInt32 (Session ["Tag_size"]);
// Set the maximum number of pages displayed
Tag. MaxPages = 9;
// Set the current page
Tag. PageNum = Convert. ToInt32 (Session ["Tag_page"]);
String [] [] myNamenValue = new string [2] [] {
New string [] {"MaxPageSize", "PageNum", "Count "},
New string [] {Tag. MaxPageSize. ToString (), Tag. PageNum. ToString ()}
};
// Call the Stored Procedure
DataTable myDataTable = mySQL. BatchGetDB ("Tag_Page_Name_Select", myNamenValue, "Count ");
Tag. DateCount = (int) mySQL. OutputCommand. Parameters ["@ Count"]. Value;
Tag. Pagination ();

HeadPage. InnerHtml = FootPage. InnerHtml = Tag. GetPageHtml;

For (int I = 0, j = myDataTable. Rows. Count; I <j; I ++)
{
Tag. TagTable (new ExDataRow (myDataTable. Rows [I]);
}
TagBox. InnerHtml = Tag. GetHtml;