電子佈告欄 (BBS)、搜尋結果、商品清單等常需「分頁顯示」。ADO `Recordset` 內建分頁屬性 (`PageSize`、`PageCount`、`AbsolutePage`),配合客戶端游標即可快速實作。本文以經典 ASP+BBS 為例,完整說明兩種分頁策略與程式碼。
兩種分頁策略比較
| 策略 | 做法 | 優點 | 缺點 | 適用場景 |
| 全量讀入 (Client-side Cursor) | 一次 `SELECT *` 所有符合條件記錄 → `Recordset` 存記憶體 → 用 `AbsolutePage` 切頁 | 程式簡單;翻頁快 (無再查 DB) | 記憶體暴增;資料過舊;不適合大資料/高併發 | 資料量 < 5,000 筆、低併發、內部管理系統 |
| 分頁查詢 (Server-side Paging) | 每次只 `SELECT TOP N ... WHERE id > last_id` 或 `OFFSET/FETCH` | 記憶體穩定;資料即時;支援海量資料 | SQL 較複雜;需處理排序/游標一致性 | 公開網站、大資料量、高併發、即時性要求高 |
Recordset 分頁關鍵屬性
PageSize:每頁筆數 (如 20)
PageCount:總頁數 (自動計算)
AbsolutePage:目前頁碼 (1-based),設定可跳頁
AbsolutePosition:目前記錄在 Recordset 中位置 (1-based)
RecordCount:總筆數 (需 `CursorLocation = adUseClient` 才準確)
EOF/BOF:判斷是否超出範圍
完整 BBS 分頁範例 (全量讀入法)
<%@ Language=VBScript CodePage=65001 %><%
Option Explicit
Response.Buffer = True
Dim conn, rs, sql, pageSize, curPage, totalRecords, totalPages, startRec, endRec
Dim keyword, orderBy
'--- 參數 ---
pageSize = 20
curPage = CInt(Request("page"))
If curPage < 1 Then curPage = 1
keyword = Trim(Request("kw"))
orderBy = "postdate DESC"
'--- 連線 ---
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("bbs.mdb")
'--- 建立 Recordset (必須用 Client Cursor 才支援分頁屬性) ---
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 ' adUseClient
rs.CursorType = 3 ' adOpenStatic (支援 AbsolutePage)
rs.LockType = 1 ' adLockReadOnly
'--- 動態 SQL ---
sql = "SELECT id, subject, name, email, postdate FROM bbs_table"
If keyword <> "" Then
sql = sql & " WHERE subject LIKE '%" & Replace(keyword, "'", "''") & "%' OR name LIKE '%" & Replace(keyword, "'", "''") & "%'"
End If
sql = sql & " ORDER BY " & orderBy
rs.Open sql, conn
'--- 分頁屬性 ---
rs.PageSize = pageSize
totalRecords = rs.RecordCount
totalPages = rs.PageCount
If curPage > totalPages Then curPage = totalPages
If curPage < 1 Then curPage = 1
rs.AbsolutePage = curPage
'--- 顯示當頁資料 ---
Dim i, recCount
recCount = 0
Response.Write "| 主題 | 作者 | 日期 |
"
Do While Not rs.EOF And recCount < pageSize
Response.Write "" & _
"| " & Server.HTMLEncode(rs("subject")) & " | " & _
"" & Server.HTMLEncode(rs("name")) & " | " & _
"" & FormatDateTime(rs("postdate"), 2) & " | " & _
"
"
rs.MoveNext
recCount = recCount + 1
Loop
Response.Write "
"
'--- 分頁導覽列 ---
Response.Write ""
'--- 清理 ---
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
%>
SQL Server / Oracle 真正的分頁查詢 (Server-side)
'--- SQL Server 2012+ (OFFSET/FETCH) ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
"ORDER BY postdate DESC " & _
"OFFSET " & ((curPage-1)*pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY;"
'--- Oracle 12c+ (OFFSET/FETCH) ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
"ORDER BY postdate DESC " & _
"OFFSET " & ((curPage-1)*pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY;"
'--- MySQL / PostgreSQL / SQLite ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
"ORDER BY postdate DESC " & _
"LIMIT " & pageSize & " OFFSET " & ((curPage-1)*pageSize) & ";"
'--- SQL Server 2000/2005/2008 (ROW_NUMBER) ---
sql = "WITH Paged AS (" & _
" SELECT id, subject, name, postdate, ROW_NUMBER() OVER (ORDER BY postdate DESC) AS rn " & _
" FROM bbs_table" & _
") SELECT * FROM Paged WHERE rn BETWEEN " & ((curPage-1)*pageSize+1) & " AND " & (curPage*pageSize) & ";"
效能優化關鍵
- 索引:`ORDER BY` 欄位 (如 `postdate`) 必須有索引。
- 避免 SELECT *:只取需要欄位,減少網路/記憶體傳輸。
- 快取總筆數:`COUNT(*)` 昂貴時,可快取 5~10 分鐘或用近似值。
- 游標選擇:`adOpenForwardOnly` + `adUseServer` 最省資源,但不支援 `AbsolutePage`;需分頁時改 `adOpenStatic` + `adUseClient`。
- 連線池:確保連線字串相同以重用連線。
結論:舊專案維護可用 Recordset 分頁;新專案務必採用資料庫原生 `OFFSET/FETCH` 或 `ROW_NUMBER` 分頁,並配合快取策略,才能支撐高併發與大資料量。