iT邦幫忙

3

SQL 使用TEMP TABLE 跟 使用 WITH 差異

Luke 2019-09-12 15:37:3515641 瀏覽

SQL 使用TEMP TABLE 跟 使用 WITH 差異?

TEMP TABLE 也有分
區域性的暫存資料表(Local),在 tempdb 資料庫中
資料型別的暫存資料表,在記憶體中

SELECT EMP_ID,EMP_NAME ...略
INTO #TEMP_ABC
FROM dbo.SKY_USER WHERE ...略

SELECT DEP_ID,EMP_NAME ...略
INTO #TEMP_DEF
FROM dbo.SKY_DEP AS H
JOIN #TEMP_ABC AS D ON D.EMP_ID= H.ID
WHERE ...略

DROP TABLE #TEMP_ABC;
DROP TABLE #TEMP_DEF;

WITH TEMP_ABC AS (SELECT EMP_ID,EMP_NAME ...略
FROM dbo.SKY_USER WHERE ...略),
TEMP_DEF AS(SELECT DEP_ID,EMP_NAME ...略
FROM dbo.SKY_DEP AS H
JOIN #TEMP_ABC AS D ON D.EMP_ID= H.ID
WHERE ...略)
Zed_Yang iT邦新手 3 級 ‧ 2019-09-12 16:49:06 檢舉
似乎是差在效率上
不管用@table 或是 #table 都額外增加I/O開銷
因此在2005年提供公用表表達式(CTE)
也就是問題中的WITH AS
至於詳細可能要請其他神人回答

參考來源 http://daidos200211.blogspot.com/2012/05/sql-with.html
小魚 iT邦大師 1 級 ‧ 2019-09-12 22:29:08 檢舉
資料庫的效能優化是一門很深的學問,
這只能靠經驗來學習了.
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
6
暐翰
iT邦大師 1 級 ‧ 2019-09-13 20:20:04
最佳解答

WITH .. AS技術被稱為CTE(Common Table Expression),在我的經驗中有三個重要差別 :

1.CTE可以使用原表格索引,temptable不行

舉例:

with cte as (
    select T1.Column,T1.Id from [tableA] T1
    join [TableB] T2 on T1.Id = T2.Id
    where 條件篩選
)
select * from cte
where Id = xxx

https://ithelp.ithome.com.tw/upload/images/20190913/20105988GYdSGg583E.png

2.CTE可以批量對原有表格做DML動作(update,delete,insert)

舉例:
使用CTE更新符合條件欄位資料

with cte as (
    select T1.Column from [tableA] T1
    join [TableB] T2 on T1.Id = T2.Id
    where 條件篩選
)
update cte set Column = 新值;

3.CTE支援遞歸

特別適合查詢有階層關係的資料,像是傳統產業公司展BOM表需求
舉例:
SQL Server 2012 | db<>fiddle


create table t
	([bom_no] varchar(5), [prd_no] varchar(1), [child_bom_no] varchar(5))
;
	
insert into t
	([bom_no], [prd_no], [child_bom_no])
values
	('a->a0', 'b', null),
	('a->a0', 'c', 'c->a0'),
	('a->a0', 'd', 'd->a0'),
	('c->a0', 'e', null),
	('c->a0', 'f', 'f->a0'),
	('d->a0', 'g', null),
	('f->a0', 'h', null),
	('f->a0', 'i', 'i->a0'),
	('i->a0', 'j', null),
	('i->a0', 'k', null),
	('l->a0', 'm', null)	
;

declare @bom_no varchar(55) = 'c->a0';
with cte as (
    select * from T 
    where bom_no = @bom_no
    union all
    select T1.* from T T1
    inner join cte T2 on T1.bom_no = T2.Child_BOM_NO
    where T2.bom_no is not null
)
select @bom_no as top_bom_no,* from cte;
0
Zed_Yang
iT邦新手 3 級 ‧ 2019-09-12 17:20:33

似乎是差在效率上
不管用@table 或是 #table 都額外增加I/O開銷
因此在2005年提供公用表表達式(CTE)
也就是問題中的WITH AS
至於詳細可能要請其他神人回答

參考來源 http://daidos200211.blogspot.com/2012/05/sql-with.html

以下為測試

原始77筆資料
Where條件塞選 61筆資料

https://ithelp.ithome.com.tw/upload/images/20190912/20113932DfRZTKjTtA.png

https://ithelp.ithome.com.tw/upload/images/20190912/20113932OsAZbJiAUd.png

https://ithelp.ithome.com.tw/upload/images/20190912/20113932kjYEGyDDQH.png

https://ithelp.ithome.com.tw/upload/images/20190912/20113932oOuygYc67p.png

如果資料量上千筆甚至上萬筆
相信效能上會差不少

0
Riverwind
iT邦新手 5 級 ‧ 2019-09-13 09:34:10

分別在於效能, 還有寫sql 的難易

0
txstudio
iT邦新手 4 級 ‧ 2019-09-15 15:27:24

WITH 可以當成子查詢方式來看

若只是單一次的查詢中儲存資料表物件的話可以使用

DECLARE @Table TABLE ( ... )

就不會進入 I/O

我要發表回答

立即登入回答