今天跟同事討論用T-SQL查資料表中最大值的方法,一群人總共想出三種: SELECT TOP 1 + ORDER BY, SELECT MAX, 再來是用CURSOR的FETCH LAST。三種做法,哪一個最有效率呢? 初步想起來,用CURSOR是最笨重的,肯定最慢。剩下的兩種,MAX()是Aggregate Function,依據我過去寫SQLCLR自訂Aggregate Function的經驗,每一列的資料都要送入Funtion中比較,應該會輸給內建的ORDER BY吧?

找來一個有150萬筆資料的Table,做了以下的實驗:

--方法1 用ORDER BY+TOP
select top 1 dst from netlog order by dst desc

--方法2 用MAX

select max(dst) from netlog

--方法3 用CURSOR

declare @dst varchar(15)

declare cur scroll cursor for select dst from netlog order by dst

open cur

fetch last from cur into @dst

select @dst

close cur

deallocate cur

實驗結果證明我之前的推論是錯的,MAX以750ms奪冠,TOP 1 + ORDER BY以921ms居次,CURSOR則以7203ms被甩到連車尾燈都看不到。仔細分析,ORDER BY必須一筆筆理出所有ROW的順序,對MIN或MAX來說純粹是做虛工,浪費資源卻沒有任何貢獻,是該敗陣。(這裡有一篇相關文章)

結論是,如果你只是要查最大或最小值時,MAX()與MIN()會比TOP 1 + ORDER BY更有效率!

另外,SQL 2005的SQL Server Management Studio(SSMS)的Cient Statistics功能比起SQL 2000 Query Analyzer豪華許多,會自動幫你進行多次執行結果的比較,還會用紅、綠、黑箭頭代表上漲、下跌跟平盤(我好像開始有職業病傾向了)。找不到啟動選單的人可以看以下的圖例:


Comments

Be the first to post a comment

Post a comment


57 - 29 =