我不是 SQL guy,也比較少機會處理 SQL Server 相關的效能問題。這次難得碰到了,在解決效能問題的過程當中,自己也學了點東西。
如果將其中的 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 指令喔!
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 裡面。延伸閱讀
Well explained! Thank you
回覆刪除