SQL Sever Table Variable 龜速問題

我不是 SQL guy,也比較少機會處理 SQL Server 相關的效能問題。這次難得碰到了,在解決效能問題的過程當中,自己也學了點東西。

問題描述

底下的預儲程序(有簡化過)跑得很慢,拖垮了應用程式整體效能:

alter procedure usp_Demo
as
begin
    declare @temp table (ID varchar(50), Total int)

    INSERT @temp (ID, Total)
        SELECT @ID, count(distinct r.rid)
     FROM my_report r JOIN my_info i ON i.id=r.id 
     WHERE i.flag=7 and r.status=0
end

GO 

如果將其中的 INSERT @temp 那行指令拿掉之後就跑得飛快,為什麼?

根本原因

如開頭說的,我不是 SQL guy,被問到這個問題,也只好趕快去做功課。不過,我恐怕還是沒辦法解釋得很清楚,若習慣看原文,不妨先看看專家怎麼說吧:Query performance and table variables

我的理解是,SQL Server 並不會蒐集 table variable 的統計資料,因為它認為會存入 table variable 的資料一定很少,通常應該只有一筆記錄。也正因為如此,每當碰到 table variable 的時候,查詢引擎就理所當然的針對「一筆記錄」的情況來產生最佳的 execution plan。如果你的預儲程序會新增很多筆記錄到 table variable(例如新增數千甚至數萬筆),而其中所使用的 SQL 命令又包含巢狀 join 的動作,那麼原先錯估的 execution plan 當然就會很沒有效率。

此外,在另一篇標題為「TempDB:: Table variable vs local temporary table」的文章中,Sunil Agarwal 提到,建立 table variable 也像一般 DDL 操作那樣需要寫入 metadata 到 system catalog 中,這表示你的預儲程序可能會有 DDL 競爭(contention)的問題。

解決方法

在需要新增多筆暫存資料的場合,盡量使用 temp table。只有單筆記錄的場合才使用 table variable。

在我碰到的 case,如前述預儲程序的例子,的確要花很長時間才能跑完(印象中是二十秒以上)。如果改用 temp table,不到一秒就跑完了。所以,可別小看那一行 insert @temp 指令喔!
如果你聽說 table variable 執行速度會比較快是因為他們都是 in-memory 物件,SQL Sever 專家 Pinal Dave 告訴你並非如此--table variable 和 temp table 一樣都是建立在 TempDB 裡面。
延伸閱讀

Post Comments

技術提供:Blogger.