SQL Server 表值函數中 TOP 子句位置對效能的劇烈影響分析

現象：TOP 放在函數外 vs 內，效能相差 6 倍

某表值函數 GetFrame_CustomerSerch 以管理員身分執行回傳 150 萬列，單獨執行約 30 秒。但若在函數外加 TOP 20：

SELECT TOP 20 * FROM GetFrame_CustomerSerch('admin','1'); -- 耗時 ~3 分鐘

將函數內部 SQL 取出，直接加 TOP 20 卻只需 6 秒。差異極大，原因何在？

根本原因分析

1. 查詢最佳化器無法「下推」TOP 至函數內部

• 內嵌表值函數在編譯時會被「展開」併入外層查詢計畫，最佳化器有機會將外層 TOP 下推至內部運算子（如 Index Seek + Top），實現早期截斷。
• 多陳述式表值函數（本案例使用 RETURN (SELECT ... UNION ALL SELECT ...) 結構）編譯時不展開，最佳化器將其視為黑盒子，只知道大致資料列數估計，無法將外層 TOP 傳遞給內部執行計畫。
• 結果：函數內部完整產生 150 萬列，存入暫存表/表變數，再由外層 TOP 截取前 20 列，造成海量不必要運算與記憶體/TempDB 壓力。

2. 表變數/暫存表溢寫至 TempDB

• 函數宣告 RETURNS TABLE 但無 @table_variable 明確定義，實際執行時結果集會寫入內部工作表（類似表變數）。
• 150 萬列超過記憶體可容納範圍，溢寫至 TempDB，產生大量 I/O 與分頁鎖競爭。
• 記憶體授予機制：查詢最佳化器依據資料列估計授予記憶體，若估計偏低（函數內部統計資訊缺失），會導致記憶體不足溢寫。

解決方案與最佳實踐

1. 改用內嵌表值函數：僅允許單一 SELECT 陳述式，無 BEGIN...END 區塊，最佳化器可展開並下推 TOP/WHERE 等邏輯。

   CREATE FUNCTION dbo.GetCustomerTop(@WorkNo varchar(38), @TopN int)
   RETURNS TABLE
   AS RETURN (
       SELECT TOP (@TopN) ... -- 參數化 TOP
       FROM ...
   );
   

2. 將篩選條件下推至函數參數：避免外層 WHERE，直接在函數內部套用 TOP 與 WHERE。
3. 改用預存程序 + OUTPUT 參數或結果集：預存程序編譯獨立，可內部控制 TOP，並支援複雜流程控制。
4. 使用 APPLY 運算子搭配內嵌函數：CROSS APPLY dbo.GetCustomerTop(c.WorkNo, 20) 可逐列套用並受益於下推。

延伸現象：計畫快取參數嗅探導致效能忽好忽壞

文中提到「存儲過程運行 1-2 天後變慢，改一個空格又變快」，典型參數嗅探問題：首次編譯時參數值產生的計畫不適合後續參數值。解法：OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、區域變數接收參數、或啟用強制參數化資料庫選項。

總結

表值函數並非銀彈。多陳述式表值函數阻擋最佳化器下推 TOP/WHERE，導致全量計算再截取。掌握「內嵌 vs 多陳述式」差異、善用參數化 TOP、必要時改用預存程序或 APPLY，才能發揮 SQL Server 查詢最佳化器的最大威力。