這是 ASP 開發者永恆的爭論話題。本文從底層實作、功能差異、效能基準、適用場景四個維度深度剖析,給出明確的選擇建議。
| 特性 | rs.Open | conn.Execute |
|---|---|---|
| 回傳物件 | Recordset 物件(需預先建立) | Recordset 物件(自動建立) |
| 游標類型控制 | 完整支援(4 種游標) | 僅支援 ForwardOnly (0) |
| 鎖定類型控制 | 完整支援(4 種鎖定) | 唯讀 |
| RecordCount 可用性 | 視游標而定(Static/Keyset 可用) | 永遠為 -1(不可用) |
| 資料修改能力 | 支援 AddNew/Update/Delete | 唯讀,不可修改 |
| 參數化查詢 | 需搭配 Command 物件 | 不支援(需拼接 SQL) |
| 多重結果集 | 支援 NextRecordset | 不支援 |
| 效能(單純查詢) | 稍慢(物件建立開銷) | 較快(輕量級) |
<%
' ✅ 推薦:簡單列表、下拉選單、驗證存在性
Dim conn, rs, sql
Set conn = GetConnection()
sql = "SELECT Top 10 Title, Url FROM Articles WHERE IsPub = 1 ORDER BY PubDate DESC"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
Response.Write "" & rs("Title") & "
"
rs.MoveNext
Loop
rs.Close : Set rs = Nothing
%>
<%
' ✅ 推薦:分頁、需要知道總筆數、上一頁/下一頁
Dim rs, sql
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Products WHERE CategoryID = 5"
rs.Open sql, conn, 3, 1 ' adOpenStatic, adLockReadOnly
If Not rs.EOF Then
Response.Write "總共 " & rs.RecordCount & " 筆產品
"
' 可使用 rs.MoveFirst, rs.PageCount, rs.AbsolutePage
End If
rs.Close : Set rs = Nothing
%>
<%
' ✅ 唯一可行方式:新增/修改/刪除記錄
Dim rs, sql
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Users WHERE UserID = 100"
rs.Open sql, conn, 1, 3 ' adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs("LastLogin") = Now()
rs.Update
End If
rs.Close : Set rs = Nothing
%>
conn.Execute:~12msrs.Open (0, 1) ForwardOnly/ReadOnly:~15msrs.Open (3, 1) Static/ReadOnly:~28msrs.Open (1, 3) Keyset/Optimistic:~45ms差異來源:游標引擎初始化、鎖定管理、客戶端游標下載開銷
conn.Execute 或 rs.Open sql, conn, 0, 1 (ForwardOnly/ReadOnly)SELECT COUNT(*) 獨立查詢 + conn.Execute 分頁,避免 Static 游標開銷Command.Execute 參數化執行,不使用 Recordset 更新rs.Open 配合 adUseClient 離線記錄集<%
' ❌ 舊寫法:Static 游標消耗記憶體
rs.Open "SELECT * FROM BigTable", conn, 3, 1
total = rs.RecordCount
' ✅ 新寫法:兩次輕量查詢
Set rsCount = conn.Execute("SELECT COUNT(*) FROM BigTable")
total = rsCount(0) : rsCount.Close
Set rsData = conn.Execute("SELECT TOP 20 * FROM BigTable WHERE ID > " & lastId & " ORDER BY ID")
' 處理資料...
%>