ASP.NET 大量資料高效分頁：不使用預存程序的 NOT IN 方案

場景：50 萬筆資料，WebForms DataList 分頁

傳統 DataSet 填滿再分頁會耗盡記憶體。SQL Server 2000/2005 早期無 ROW_NUMBER()、OFFSET/FETCH，常用 NOT IN (SELECT TOP N ...) 實作分頁。本文整理完整可用方案。

資料庫準備

CREATE TABLE RedHeadedFile (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FileNames NVARCHAR(20),
    SendUser NVARCHAR(20)
);

-- 測試資料 50 萬筆
DECLARE @i INT = 1;
WHILE @i <= 500000
BEGIN
    INSERT INTO RedHeadedFile (FileNames, SendUser) VALUES (N'我的分頁算法', N'陸俊銘');
    SET @i += 1;
END

核心 SQL：NOT IN 分頁

-- 第 1 頁
SELECT TOP 20 * FROM RedHeadedFile
WHERE ID NOT IN (SELECT TOP 0 ID FROM RedHeadedFile ORDER BY ID ASC)
ORDER BY ID ASC;

-- 第 2 頁
SELECT TOP 20 * FROM RedHeadedFile
WHERE ID NOT IN (SELECT TOP 20 ID FROM RedHeadedFile ORDER BY ID ASC)
ORDER BY ID ASC;

-- 第 N 頁
SELECT TOP @PageSize * FROM RedHeadedFile
WHERE ID NOT IN (SELECT TOP @PageSize * @CurrentPage ID FROM RedHeadedFile ORDER BY ID ASC)
ORDER BY ID ASC;

原理：子查詢取得前 N 頁的 ID，主查詢排除這些 ID，再取 Top PageSize 筆。前提：分頁欄位必須有唯一索引（如 IDENTITY PK），且排序固定。

後台完整程式碼

public partial class WebForm8 : System.Web.UI.Page
{
    const int PageSize = 20;
    int TotalRecords, TotalPages;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            TotalRecords = GetTotalCount();
            TotalPages = (TotalRecords + PageSize - 1) / PageSize; // 天花板除法

            ViewState["TotalPages"] = TotalPages;
            ViewState["CurrentPage"] = 0;

            lblTotalPages.Text = TotalPages.ToString();
            lblTotalRecords.Text = TotalRecords.ToString();
            txtGoPage.Enabled = TotalRecords > PageSize;

            BindData();
        }
    }

    int GetTotalCount()
    {
        using (var conn = GetConnection())
        using (var cmd = new SqlCommand("SELECT COUNT(*) FROM RedHeadedFile", conn))
        {
            return (int)cmd.ExecuteScalar();
        }
    }

    void BindData()
    {
        int currentPage = (int)ViewState["CurrentPage"];
        int totalPages = (int)ViewState["TotalPages"];

        // 按鈕啟用狀態
        btnFirst.Enabled = btnPrev.Enabled = currentPage > 0;
        btnNext.Enabled = btnLast.Enabled = currentPage < totalPages - 1;

        // 核心分頁查詢
        string sql = $@"
            SELECT TOP {PageSize} * FROM RedHeadedFile
            WHERE ID NOT IN (
                SELECT TOP {PageSize * currentPage} ID FROM RedHeadedFile ORDER BY ID ASC
            )
            ORDER BY ID ASC";

        using (var conn = GetConnection())
        using (var adapter = new SqlDataAdapter(sql, conn))
        {
            var ds = new DataSet();
            adapter.Fill(ds);
            dlData.DataSource = ds.Tables[0].DefaultView;
            dlData.DataBind();
        }

        lblCurrentPage.Text = (currentPage + 1).ToString();
        txtGoPage.Text = (currentPage + 1).ToString();
    }

    protected void PageNavigation_Click(object sender, CommandEventArgs e)
    {
        int currentPage = (int)ViewState["CurrentPage"];
        int totalPages = (int)ViewState["TotalPages"];

        switch (e.CommandName)
        {
            case "next": currentPage++; break;
            case "prev": currentPage--; break;
            case "last": currentPage = totalPages - 1; break;
            default: currentPage = 0; break; // first
        }

        ViewState["CurrentPage"] = currentPage;
        BindData();
    }

    protected void txtGoPage_TextChanged(object sender, EventArgs e)
    {
        if (int.TryParse(txtGoPage.Text, out int page) && page >= 1 && page <= TotalPages)
        {
            ViewState["CurrentPage"] = page - 1;
            BindData();
        }
        else
        {
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('頁碼超出範圍');", true);
        }
    }

    SqlConnection GetConnection()
    {
        var conn = new SqlConnection(ConfigurationManager.AppSettings["DSN"]);
        conn.Open();
        return conn;
    }
}

前台關鍵標記

    
        

            

                
<%# Eval("FileNames") %>
                
<%# Eval("SendUser") %>
                
<%# Eval("ID") %>
            
        
    



    共  頁 /
     筆
    首頁
    上一頁
    下一頁
    尾頁
    當前第  頁
    跳頁 

效能分析與優化建議

現代建議

1. SQL Server 2012+ 請直接用 ORDER BY ID OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY。
2. 大數據、高併發 API 採用 Keyset Pagination（游標分頁），避免 OFFSET 深分頁效能崩塌。
3. 前端配合無限滾動或虛擬清單，減少跳頁需求。
4. 資料庫層面：分頁欄位建立覆蓋索引，INCLUDE 常用顯示欄位，避免書籤查找。

結語

NOT IN 分頁是經典年代的產物，理解其原理有助於維護舊系統。新專案請擁抱現代分頁語法與 Keyset Pagination，配合適當索引，才能在百萬級資料下保持毫秒級回應。