以前在學校習慣用子查詢去做一些複雜運算,但到工作上才發現有「暫存」寫法。
不是說子查詢不能用,只是在效率上會比暫存差,所以盡量改為TempTable或CTE,以下說明兩種暫存寫法....
TempTable個人工作經驗用法
1.定義暫存表格式, 把搜尋資料表的結果新增到暫存表
Create table #TempName(Column ColumnType...);
Insert into #TempName Select * from Table where Table.Column = Value;
2.定義暫存表格式, 把執行預存程序的結果新增到暫存表
Create table #TempName(Column ColumnType...);
Insert into #TempName Exec StoredProcName ParamValue...;
3.搜尋資料表的結果新增到暫存表
Select * Into #TempName from Table where Table.Column = Value;
4.搜尋資料表A的結果新增到暫存表,以暫存表的值更新資料表B的欄位值
Select * Into #TempName from TableA where TableA.Column = Value;
Update TableB Set T.Column2 = TT.Column2
From TableB T Join #TempName TT On T.Column1 = TT.Column1;
1跟2適用:3嚴謹版(麻煩版),好處是鎖定欄位值屬性,在寫Select會注意到搜尋欄位屬性。
3適用:1跟2縮減版(懶惰版),如果要新增到資料表,需確定搜尋欄位是否有對應(字串欄位不能寫入數字欄位)。
4適用:Update界大神,常見案例A表更新B表,甚至連A表自更都行。((遭毆
CTE個人工作經驗用法
1.搜尋資料表A的結果新增到CTE
With CTEName As(
Select * from TableA where TableA.Column = Value;
)
2.搜尋資料表A的結果新增到CTE,以CTE的值更新資料表B的欄位值
With CTEName As(
Select * from TableA where TableA.Column = Value;
)
Update TableB Set T.Column2 = C.Column2
From TableB T Join CTEName C On T.Column1 = C.Column1;
1適用:跟TempTable 3一樣。((遭毆
2適用:跟TempTable 4一樣。((遭毆
TempTable與CTE差異:
TempTable | CTE | |
資料暫存位置 | 硬體 | 記憶體 |
用暫存表次數 | 多次 | 1次 |
清除暫存表 | 後面需Drop Table #TempName | 用過暫存表後清除 |
注意寫法 | #是區域,##是全域 | WITH前面需加「;」 |
禁用函數 | 無 | Order By, Into |
遞迴功能 | 無 | Inner Join |
適用資料量 | 硬體幾TB且效率高,因此適用資料量多 | 大量使用者查詢會把記憶體佔滿,因此適用資料量少 |
資料庫效率 | 等查詢結果後寫入硬體,因此是看查詢結果效率,整體上效率高 | 有遞迴需等待深層執行後,重整串接後才寫入記憶體,整體上效率低 |
參考資料:
留言列表