傳統 TOP N 或雙重 TOP 分頁在大數據量下性能劣化。本文提供基于游標定位的高性能分頁存儲過程,配套 ASP.NET 分頁控件完整代碼,實現「只取當前頁數據」的極致優化。
ALTER PROCEDURE [dbo].[Tag_Page_Name_Select]
@MaxPageSize INT, -- 每頁記錄數
@PageNum INT, -- 當前頁碼
@Count INT OUTPUT -- 總記錄數輸出
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Name NVARCHAR(50), @Cursor INT;
-- 1. 獲取總記錄數
SELECT @Count = COUNT(tag_Name) FROM [viewdatabase0716].[dbo].[view_tag];
-- 2. 計算游標起始位置
SET @Cursor = @MaxPageSize * (@PageNum - 1) + 1;
-- 3. 越界保護:自動定位到最後一頁
IF @Cursor > @Count
BEGIN
IF @Count % @MaxPageSize = 0
SET @Cursor = CASE WHEN @Cursor > @MaxPageSize THEN @Count - @MaxPageSize + 1 ELSE 1 END;
ELSE
SET @Cursor = @Count - (@Count % @MaxPageSize) + 1;
END
-- 4. 定位到該頁第一條記錄的排序鍵值
SET ROWCOUNT @Cursor;
SELECT @Name = tag_Name FROM [viewdatabase0716].[dbo].[view_tag] ORDER BY tag_Name;
-- 5. 取當前頁數據
SET ROWCOUNT @MaxPageSize;
SELECT * FROM [viewdatabase0716].[dbo].[view_tag]
WHERE tag_Name >= @Name ORDER BY tag_Name;
SET ROWCOUNT 0;
END
SET ROWCOUNT 限制掃描行數,直接定位到頁首記錄。ORDER BY tag_Name 需有對應索引,實現索引順序掃描而非表掃描。public class ExStringBuilder {
// 屬性:頁碼、頁大小、顯示頁碼數、總記錄數
public int PageNum { get; set; } = 1;
public int MaxPageSize { get; set; } = 25;
public int MaxPages { get; set; } = 10;
public int DateCount { get; set; }
public int AllPage => (int)Math.Ceiling((decimal)DateCount / MaxPageSize);
public void Pagination() {
// 1. 計算顯示頁碼範圍
int leftRange = (MaxPages + 1) / 2 - 1;
int rightRange = (MaxPages + 1) / 2;
int leftStart, rightEnd;
if (MaxPages >= AllPage) { leftStart = 1; rightEnd = AllPage; }
else if (PageNum <= leftRange) { leftStart = 1; rightEnd = leftStart + MaxPages - 1; }
else if (AllPage - PageNum < rightRange) { rightEnd = AllPage; leftStart = rightEnd - MaxPages + 1; }
else { leftStart = PageNum - leftRange; rightEnd = PageNum + rightRange; }
// 2. 生成首頁/上一頁/頁碼/下一頁/尾頁 HTML
// ... 省略具體 StringBuilder 拼接邏輯
}
public void TagTable(ExDataRow row) {
// 生成數據行 HTML
}
}
var tagPager = new ExStringBuilder {
MaxPageSize = Convert.ToInt32(Session["Tag_size"]),
MaxPages = 9,
PageNum = Convert.ToInt32(Session["Tag_page"])
};
string[][] param = {
new[] { "MaxPageSize", "PageNum", "Count" },
new[] { tagPager.MaxPageSize.ToString(), tagPager.PageNum.ToString() }
};
DataTable dt = mySQL.BatchGetDB("Tag_Page_Name_Select", param, "Count");
tagPager.DateCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value;
tagPager.Pagination();
HeadPage.InnerHtml = FootPage.InnerHtml = tagPager.GetPageHtml;
foreach (DataRow row in dt.Rows) tagPager.TagTable(new ExDataRow(row));
TagBox.InnerHtml = tagPager.GetHtml;
SET ROWCOUNT 不影響 INSERT/UPDATE/DELETE,SQL Server 2012+ 建議改用 OFFSET/FETCH 標準語法。