[T-SQL] WITH 通用資料表運算式:暫時資料表


在微軟文件是寫暫存資料表,不過我覺得會跟 tempdb 裡面的暫存資料表混淆,所以我把暫存改為暫時。

WITH 我用到的機會不多,所以我以為我在 blog 上寫過跟 WITH 相關的主題,以便提醒自己。前天我要在 SSMS 下個 T-SQL ,要使用到 WITH ,但太久沒用了,完全想不起來關鍵字,google 暫存資料表不是舊的 tempdb ,不然就是 MSSQL 2016 以後支援的記憶體暫存資料表,根本找不到我想要的,翻了好久才翻到,所以這次要記下來,不然下次找不到。

在一個複雜或是資料龐大的查詢中,如果後續還要繼續對這個查詢做處理,最好把他寫到暫存資料表後再重複利用。

MSSQL 很早就有這功能,就是利用 tempdb ,使用 T-SQL 語法去處理,可以不用刪暫存資料表,SQL Server 會在斷線後自動刪除。

暫存資料表的命名就是以 # 為起頭。

詳見:https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

這裡面的缺點就是建立暫存資料表時,硬碟會有大量的 IO ,所以從 SQL Server 2016 起,支援在記憶體內建立暫存資料表,以節省硬碟 IO 並加速查詢。

另一個在 SQL Server 也支援很久的就是暫時資料表。

這篇的 SQL 語法可改用 PVIOT 加速,下次來說,這篇主要是介紹 WITH。

詳見:https://docs.microsoft.com/zh-tw/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

因為不用建立暫存資料表,所以上圖的 1/2/3/6 行都沒了,只剩下 4/5 行。上圖的第 4 行轉成 WITH 語法就是下圖的 1 ~ 3 行,上圖的第 5 行為了強調重複利用暫存資料表,把它展開成 4 ~ 18 行。WITH 建立的這篇稱為暫時資料表。

從這個案例來說明,原先的資料在 Data_Hour 超過 2 億筆 (row) ,如果每次 JOIN 都從原始資料撈,會浪費大量的 IO 與計算時間。所以透過暫存資料表或暫時資料表,可以加速查詢。

暫時資料表也可以多個,甚至類似巢狀,把上圖 1 ~ 3 行拆成兩個,如 1 ~ 7 行,8 行以後維持不變。

這邊是假設第二次查詢比較複雜,所以先建立 Data_Init 查詢結果後,再由 Data_CTE 經過複雜的 WHERE 運算得到新的暫時資料表,給下方的查詢使用。

我要找 WITH 是想到有個庫齡計算的 T-SQL 語法可以透過 WITH 加速,但太久沒用,一直想不起來關鍵字,自己記錄一篇提醒自己。

Categories: 工作點滴, 技術分享 | 標籤: | 1 則迴響

文章分頁導航

One thought on “[T-SQL] WITH 通用資料表運算式:暫時資料表

  1. 引用通告: [T-SQL] 評估 PVIOT 加速資料組成效益 | 鄭子璉

發表迴響

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

WordPress.com 標誌

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

Google photo

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

Twitter picture

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

Facebook照片

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

連結到 %s

在WordPress.com寫網誌.

%d 位部落客按了讚: