[T-SQL] 評估 PVIOT 加速資料組成效益


關於 PVIOT 官方說明請參考:

https://docs.microsoft.com/zh-tw/sql/t-sql/queries/from-using-pivot-and-unpivot

前篇 [T-SQL] WITH 通用資料表運算式:暫時資料表 有提到,要來介紹 PVIOT ,我在實際運用時,是測完 WITH 後,直接進入 PVIOT ,所以實際上 WITH 並沒用在系統中,只有測試過。

使用暫存資料庫、WITH 的範例語法請參考前篇,這篇就著重在 PVIOT ,範例也可以看看官方說明。

PVIOT 語法比 LEFT JOIN 簡潔

前篇是使用專案資料庫,資料筆數少,所以很難正確評估效益,這篇改用主資料庫,先用 COUNT 把資料庫筆數撈出來,有 158,673,734 ,也就是說有 1.58 億筆資料。

資料庫筆數

之後同時比較三種查詢方式的執行計畫:

將執行計劃時間整理如下表:

動作時間(秒)估計筆數
插入資料到暫存資料庫2.806202809
從暫存資料表查詢並組合資料0.0923038960
使用 WITH 查詢並組合資料3956.86894E+44
使用 PVIOT 查詢並組合資料2.4524782

我很驚訝 WITH 使用時間是 PVIOT 的 161 倍,我推論 WITH 並非原先想像先完成查詢結果集放在記憶體內的暫時資料表 (@MSSQL2017) ,應該是 T-SQL 字串的變數,因此每次 LEFT JOIN 都從 1.58 億筆內資料撈,導致 Cross 乘積 達到 6.86894E+44 那麼大。

也還好我實際系統沒用 WITH ,直接跳過去用 PVIOT ,少了踩雷,難怪如前篇般,我根本想不起 WITH 這個關鍵字。

去掉 WITH 查詢後,重跑執行計畫:

將執行計劃時間整理如下表:

動作時間(秒)估計筆數
插入資料到暫存資料庫2.956202809
從暫存資料表查詢並組合資料0.0873038960
使用 PVIOT 查詢並組合資料2.4144782

PVIOT 的確有增速效益,但並不如想像中明顯,大概為 1.26 倍。

這篇最大的心得是 WITH 是個雷… PVIOT 加速其實也有限。

Categories: 資料庫, SQL, 工作點滴, 技術分享 | 標籤: | 發表留言

文章分頁導航

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: