iT邦幫忙

1

[MSSQL]SQL語法執行速度,哪一個會比較好?

各位前輩好

我們公司的SQL Server 為2005,掛在WorkStation虛擬機器上面,有SQL語法執行效能的疑問如下:

原始語法

SELECT
	b.PromCase,
	a.DepCode,
	SUM (a.Amount),
	SUM (a.SaleMoney)
FROM
	(
		CPTBONUS_1G0_History AS a
		LEFT JOIN CPTBONUS_DocMt_History AS b ON a.DocNum = b.DocNum
	)
LEFT JOIN CPTBONUS_promClass_History AS c ON b.PromClass = c.ClassID
WHERE
	b.PromCase = 'B'
AND a.saveym = 201809
AND b.saveym = 201809
AND c.saveym = 201809
GROUP BY
	b.PromCase,
	a.DepCode
ORDER BY
	b.PromCase,
	a.DepCode

執行時間
https://ithelp.ithome.com.tw/upload/images/20181126/20017294qrvgRbEJ0z.jpg

修改後語法

SELECT
	b.PromCase,
	a.DepCode,
	SUM (a.Amount),
	SUM (a.SaleMoney)
FROM
	(SELECT * FROM CPTBONUS_1G0_History WHERE saveym = 201809) AS a
LEFT JOIN (SELECT * FROM CPTBONUS_DocMt_History WHERE saveym = 201809) AS b ON a.DocNum = b.DocNum
LEFT JOIN (SELECT * FROM CPTBONUS_promClass_History WHERE saveym = 201809) AS c ON b.PromClass = c.ClassID
WHERE
	b.PromCase = 'B'
GROUP BY
	b.PromCase,
	a.DepCode
ORDER BY
	b.PromCase,
	a.DepCode

執行時間
https://ithelp.ithome.com.tw/upload/images/20181126/20017294WnO0DhyQht.jpg

兩個差異不到1秒,這樣比較建議使用哪一種方法呢? 另外,執行時間需要2分鐘多,是否有加快的方法呢?

謝謝!

MSSQL嗎?
b7307024 iT邦新手 4 級 ‧ 2018-11-26 17:46:30 檢舉
是的,抱歉,已修改問題標籤 及內容!
0
darwin0616
iT邦新手 2 級 ‧ 2018-11-26 17:41:43

可以試試把資料量增加2倍、3倍、 ... 、N倍 來測試看看!
這樣比較SQL語法差異應該能測出來!

學習寫程式來塞假資料也是資訊業中常會經歷的過程!/images/emoticon/emoticon10.gif
資料『做』的好也是有學問的!

b7307024 iT邦新手 4 級 ‧ 2018-11-26 17:48:26 檢舉

使用 BETWEEN 201809 AND 201810 測試中.. ^^

b7307024 iT邦新手 4 級 ‧ 2018-11-26 17:59:09 檢舉

darwin0616大,資料量大的話,差異真的變大了,差92.435秒,看來還是要學習正確的觀念,感謝您!!

那目前有"當前資料表"及"歷史資料表",從我的SQL可以看出是查詢歷史資料,當我查詢"當前資料表"速度就很快,請問有辦法改善查詢歷史資料時速度快一點嗎?

小魚 iT邦高手 1 級 ‧ 2018-11-26 19:03:25 檢舉

要看你是怎麼查的啊...

2
rogeryao
iT邦高手 7 級 ‧ 2018-11-26 20:54:00

1.CPTBONUS_promClass_History AS c 未使用,去除
2.理論上,多一次子查詢伺服器就需多運算一次
3.應避免 SELECT * FROM 語法(只需放入你要的欄位)

SELECT b.PromCase,a.DepCode,SUM (a.Amount),SUM (a.SaleMoney)
FROM CPTBONUS_1G0_History AS a
LEFT JOIN CPTBONUS_DocMt_History AS b ON a.saveym=b.saveym 
AND a.DocNum = b.DocNum
WHERE a.saveym = 201809
AND	b.PromCase = 'B'
GROUP BY b.PromCase,a.DepCode
ORDER BY b.PromCase,a.DepCode
b7307024 iT邦新手 4 級 ‧ 2018-11-29 15:05:11 檢舉

rogeryao

因我是直接拿前人寫的語法來測試執行,沒注意到C表沒用到,真是抱歉!然而,你說的2及3點,我受教了,尤其是第3點,下次寫SQL時會更加注意。

最後執行慢的問題,我是參考poiu124pat圓頭人的建議,建立index才解決的,但還是很謝謝你喔!

0
poiu124pat
iT邦新手 3 級 ‧ 2018-11-26 22:26:01

SELECT
b.PromCase,
a.DepCode,
SUM (a.Amount),
SUM (a.SaleMoney)
from
CPTBONUS_1G0_History a,
CPTBONUS_DocMt_History b,
CPTBONUS_promClass_History c
where
a.saveym='201809' and b.saveym='201809' and c.saveym='201809' and
a.DocNum = b.DocNum and
b.PromClass = c.ClassID and
b.PromCase = 'B'
GROUP BY
b.PromCase,
a.DepCode
ORDER BY
b.PromCase,
a.DepCode

你的Select沒有用到C Table,為什麼要把C放進來???

如果你的where 常常用到saveym,你的join也會用到saveym,把這個欄位放到index中,不用改與法查詢的速度就會有差了..

update..
剛剛看錯了..如果把C Table拿掉,rogeryao提供的答案應該是唯一解..
但我還是認為應該是照我說的去改index才會比較快。

另外,MS SQL的查詢器不是有提供ms sql management studio,這個軟體本身就有查詢成本的檢視功能,可以看到查詢成本去優化阿!

語法...
打錯字

b7307024 iT邦新手 4 級 ‧ 2018-11-29 15:48:00 檢舉

感謝poiu124pat大回覆

C表沒用到去除及使用index是正解,建了index後速度差好多,上網查詢成本檢視的工具,有初步使用了一下,還在摸索中。

1
圓頭人
iT邦新手 2 級 ‧ 2018-11-27 08:39:29

撈的慢,有3個原因
1.寫法有問題,導致table scan
2.撈太多,導致網路傳輸需要比較久的時間
3.where的欄位沒有建Index,導致table scan

第1個問題,可以參考這裡
第2個問題,可以只撈需要的欄位
針對第3個問題,Miscrosoft SQL Server Managerment Studio工具裡面有一個Database Engine Tuning Advisor
可以幫忙偵側,且建議建立哪些index
非常好用,可以試用看看哦~

b7307024 iT邦新手 4 級 ‧ 2018-11-29 16:06:04 檢舉

圓頭人

我是先針對你說的第3點,研究如何建立index,建立完畢後測試速度就只能以飛快來形容,約0.033s就執行完成。

不過關於Database Engine Tuning Advisor這個工具,我不管怎樣都找不到,在2005及2008 R2的Managerment Studio工具都找不到說,請問有辦法協助嗎??感謝您!

2
純真的人
iT邦高手 1 級 ‧ 2018-11-27 09:39:05

這樣..你試試..看幾秒以及資料是否正確..

declare @Tab1 table(
	DepCode nvarchar(50)
	,DocNum int
	,Amount int
	,SaleMoney int
)

declare @Tab2 table(
	PromCase nvarchar(50)
	,DocNum int
)

insert into @Tab1
select DepCode
,DocNum
,Amount
,SaleMoney
from CPTBONUS_1G0_History
where saveym = 201809

insert into @Tab2
select PromCase
,DocNum
from CPTBONUS_DocMt_History
where saveym = 201809
and PromCase = 'B'

SELECT b.PromCase,
a.DepCode,
SUM (a.Amount),
SUM (a.SaleMoney)
FROM @Tab2 as b
left join @Tab1 as a on a.DocNum = b.DocNum
GROUP BY
b.PromCase,
a.DepCode
ORDER BY
b.PromCase,
a.DepCode
b7307024 iT邦新手 4 級 ‧ 2018-11-29 16:06:59 檢舉

感謝您,最後使用建立index的方式排除速度慢的問題了!

好唷~~

我要發表回答

立即登入回答