ORDER BY RND() 是 ASP 開發者實作「隨機推薦」「隨機廣告」最直覺的寫法,但也是效能殺手。本文揭露其運作機制,提供三種分級優化方案。
<%
' ❌ 效能極差寫法
sql = "SELECT TOP 10 * FROM Articles WHERE IsPub=1 ORDER BY RND(-(ID + " & Rnd() & ")), PubDate DESC"
%>
RND(-(ID + Rnd())) 試圖解決每列相同隨機數,但本質仍是逐列計算<%
' Access / SQL Server 通用
sql = "SELECT TOP 10 * FROM Articles WHERE IsPub=1 ORDER BY RND(ID * " & Timer() & ")"
' 或 SQL Server 專用
sql = "SELECT TOP 10 * FROM Articles WHERE IsPub=1 ORDER BY NEWID()"
%>
<%
' 步驟 1:取得符合條件的 ID 範圍
Set rs = conn.Execute("SELECT MIN(ArticleID) AS MinID, MAX(ArticleID) AS MaxID, COUNT(*) AS Cnt FROM Articles WHERE IsPub=1")
If rs.EOF Then Exit Sub
Dim minId, maxId, totalCount, targetCount, randomIds, i
minId = rs("MinID") : maxId = rs("MaxID") : totalCount = rs("Cnt")
rs.Close
' 步驟 2:在應用層生成隨機 ID 集合
targetCount = 10
If totalCount <= targetCount Then
sql = "SELECT * FROM Articles WHERE IsPub=1"
Else
' 生成不重複隨機數(簡易版,大量時建議 Fisher-Yates)
ReDim randomIds(targetCount - 1)
Randomize
For i = 0 To targetCount - 1
randomIds(i) = Int((maxId - minId + 1) * Rnd + minId)
Next
' 實務上需檢查 ID 是否存在且符合條件,這裡簡化
sql = "SELECT * FROM Articles WHERE ArticleID IN (" & Join(randomIds, ",") & ") AND IsPub=1"
End If
%>
<%
' SQL Server 專用:TABLESAMPLE (BERNOULLI/ SYSTEM)
' SYSTEM:頁面層級採樣,極快但不精確;BERNOULLI:列層級,較精確但較慢
sql = "SELECT TOP 10 * FROM Articles TABLESAMPLE (1 PERCENT) WHERE IsPub=1 ORDER BY NEWID()"
' 通用高效法:利用系統統計資訊估算
' 1. 取得總頁數/總列數(sys.partitions / sys.dm_db_partition_stats)
' 2. 計算隨機頁碼/偏移量
' 3. 使用 OFFSET/FETCH (SQL 2012+) 或 TOP + 子查詢
%>
<%
' 定期排程(如每小時)預計算隨機推薦池,存入快取表
' 排程工作:
' TRUNCATE TABLE RandomPool;
' INSERT INTO RandomPool (ArticleID, PoolType, SortOrder)
' SELECT ArticleID, 'HomePage', ROW_NUMBER() OVER (ORDER BY NEWID())
' FROM Articles WHERE IsPub=1;
' 頁面顯示時:
sql = "SELECT a.* FROM Articles a INNER JOIN RandomPool r ON a.ArticleID = r.ArticleID " & _
"WHERE r.PoolType = 'HomePage' ORDER BY r.SortOrder"
%>
| 方案 | 資料量 | 隨機度 | 效能 | 實作複雜度 |
|---|---|---|---|---|
| ORDER BY RND/NEWID | < 5K | 完美 | 極慢 | 低 |
| 應用層隨機 ID | 5K-50K | 良好 | 中等 | 中 |
| TABLESAMPLE | > 50K | 近似 | 極快 | 中(需 SQL Server) |
| 預計算隨機池 | 任何 | 可控 | 查詢極快 | 高(需排程) |