n。GetRows 一次取回)。m。m 個 ID,再查詢完整欄位。<%
Dim conn, rs, sql, rCount, ids, i, randIdx, targetId, detailRs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=MyDB;UID=sa;PWD=xxx"
' 1. 取得總數
Set rs = conn.Execute("SELECT COUNT(*) FROM Articles")
rCount = rs(0)
If rCount = 0 Then Response.Write "無資料": Response.End
' 2. 取回所有 ID (用 GetRows 效能最佳)
Set rs = conn.Execute("SELECT ArticleID FROM Articles ORDER BY ArticleID")
Dim idArray: idArray = rs.GetRows() ' 回傳二維陣列 (0-based, 欄位 x 列)
' idArray(0, i) 即為第 i+1 筆 ID
' 3. 產生隨機索引
Randomize Timer
randIdx = Int(Rnd * rCount) ' 0-based
targetId = idArray(0, randIdx)
' 4. 查詢完整內容
sql = "SELECT Title, Content, Author, PubDate FROM Articles WHERE ArticleID = " & targetId
Set detailRs = conn.Execute(sql)
If Not detailRs.EOF Then
Response.Write "" & Server.HTMLEncode(detailRs("Title")) & "
"
Response.Write "" & detailRs("Content") & ""
Response.Write "作者:" & detailRs("Author") & " | 發布:" & detailRs("PubDate") & "
"
End If
detailRs.Close: Set detailRs = Nothing
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
%>
sql = "SELECT TOP 1 * FROM Articles TABLESAMPLE (1 ROWS) REPEATABLE (RAND() * 1000000)"
Set rs = conn.Execute(sql)
' 注意:TABLESAMPLE 為頁面層級抽樣,極小資料表可能回傳 0 列,需搭配 fallback
| 方法 | 資料量 1 萬 | 資料量 100 萬 | 備註 |
|---|---|---|---|
| ORDER BY NEWID() (SQL Server) | ~50ms | ~2000ms (全表掃描+排序) | 不建議大量資料 |
| ID 陣列 + 亂數 (本文) | ~5ms | ~30ms (GetRows 單次讀取) | 記憶體佔用約 4MB/百萬筆 |
| TABLESAMPLE | ~2ms | ~2ms | 統計抽樣,非嚴格均勻 |
TABLESAMPLE 或維護一張「隨機排序索引表」定期重建。TOP 1 ... ORDER BY RND(ID) 但極慢)。