正文
概要組轉發過來一封性能改善請求。有一組新劃歸我們維護的程序中使用了大量的varchar(max)類型，希望我們將它統一改成varchar(4000)類型，據說這樣可以顯著的改善性能。
同事甲試著測試了其中一個程序，報告說：果不其然，改成varchar(4000)後，性能提高了5倍！我非常非常吃驚。按照自己的判斷，改動後可能會有所提高，但達到這種驚人的程度，就有點匪夷所思了。
網上查閱了一些資料，總的來說，結論是一致的：盡可能使用滿足實際數據長度要求的數據類型、而不是MAX類型，有助於提高性能。道理上很好理解，這樣做節約內存，可以提高Cache的命中率，減少I/O的次數，從而達到提高性能的效果。
但是還是不相信效果會如此的驚人。查到今年3月份發表的一篇文章（http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/），作者做了一係列的測試，並說明在實際的應用中，改動了一處，取得了25%的性能提高。
忍不住拿文章中的代碼，在我自己的環境中測試了一下。結果很出人意料，完全摸不著頭腦。比如Assignment測試，分別使用varchar(max)與varchar(8000)，多次測試結果的變動範圍很大，似乎用varchar(8000)平均起來要稍快一點，但很難說有很明顯的優勢。其他幾項測試的結果也是一樣的混沌不清。
完全抓瞎！
再重新仔細地分析資料，發現資料中在做性能分析時有一個特點，大多數討論都是圍繞著表的列的類型定義中采用varchar(max)或varchar(N)，就像前麵文章中的Data Access測試。再回頭看看我們維護的程序代碼，完全不是這麽回事。
實際用的表的設計中，沒有任何一個字段采用了varchar(max)型，而在程序裏對這些字段的保存變量的定義中，大量的采用了varchar(max)。很明顯，是程序員在編程時貪圖方便，不願意一個一個變量的設定準確的長度值。當然，這樣也不是沒有好處，那就是當式樣變更、改動了表中字段的長度時不用修改程序。
重新編寫了一個測試程序，盡量地仿照實際程序的流程。（1）變量定義用varchar(max)（10個字段10個變量）；（2）用遊標Cursor把表1中的數據取出，存入varchar(max)變量中；（3）對varchar(max)變量進行加工處理；（4）把編輯後的varchar(max)變量值Insert到表2中；（5）遊標Cursor循環至結束。
變量定義先後用varchar(max)和varchar(N)，表1中的數據先後用1萬件和10萬件。最後得出的測試結果還是混沌不清，幾乎沒有區別。
結論隻有一個，表定義中如果沒有使用varchar(max)型、僅僅是程序中的變量定義的話，修改成varchar(N)型，沒有證據表明會對性能產生什麽積極的影響。至於同事那個見鬼的5倍效果，隻有再驗證、再驗證、再驗證了。
頭疼。。。5倍的結果，已經報告給客戶了哦。。。
------------------------------------------------------------------------------
【注】通過反複的測試，終於發現了問題所在。問題出現在遊標循環中的一條集計查詢上：
SELECT @xx = count_big(*) FROM tbxxx WHERE 字段1 = @yy ... ...
當@yy 變量的類型定義是varchar(max)或varchar(N)時，MSSQL選擇了截然不同的兩種執行計劃。雖然都是用了同一個索引，而且是index seek，但varchar(max)時的執行計劃更複雜、更費時。加上實際項目中，這一條SQL文的被查詢表是一個10萬條記錄以上的較大的表，結果造成了執行時間的顯著增加。
結論：WHERE語句中盡量避免使用varchar(max)型的變量
同事甲試著測試了其中一個程序，報告說：果不其然，改成varchar(4000)後，性能提高了5倍！我非常非常吃驚。按照自己的判斷，改動後可能會有所提高，但達到這種驚人的程度，就有點匪夷所思了。
網上查閱了一些資料，總的來說，結論是一致的：盡可能使用滿足實際數據長度要求的數據類型、而不是MAX類型，有助於提高性能。道理上很好理解，這樣做節約內存，可以提高Cache的命中率，減少I/O的次數，從而達到提高性能的效果。
但是還是不相信效果會如此的驚人。查到今年3月份發表的一篇文章（http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/），作者做了一係列的測試，並說明在實際的應用中，改動了一處，取得了25%的性能提高。
忍不住拿文章中的代碼，在我自己的環境中測試了一下。結果很出人意料，完全摸不著頭腦。比如Assignment測試，分別使用varchar(max)與varchar(8000)，多次測試結果的變動範圍很大，似乎用varchar(8000)平均起來要稍快一點，但很難說有很明顯的優勢。其他幾項測試的結果也是一樣的混沌不清。
完全抓瞎！
再重新仔細地分析資料，發現資料中在做性能分析時有一個特點，大多數討論都是圍繞著表的列的類型定義中采用varchar(max)或varchar(N)，就像前麵文章中的Data Access測試。再回頭看看我們維護的程序代碼，完全不是這麽回事。
實際用的表的設計中，沒有任何一個字段采用了varchar(max)型，而在程序裏對這些字段的保存變量的定義中，大量的采用了varchar(max)。很明顯，是程序員在編程時貪圖方便，不願意一個一個變量的設定準確的長度值。當然，這樣也不是沒有好處，那就是當式樣變更、改動了表中字段的長度時不用修改程序。
重新編寫了一個測試程序，盡量地仿照實際程序的流程。（1）變量定義用varchar(max)（10個字段10個變量）；（2）用遊標Cursor把表1中的數據取出，存入varchar(max)變量中；（3）對varchar(max)變量進行加工處理；（4）把編輯後的varchar(max)變量值Insert到表2中；（5）遊標Cursor循環至結束。
變量定義先後用varchar(max)和varchar(N)，表1中的數據先後用1萬件和10萬件。最後得出的測試結果還是混沌不清，幾乎沒有區別。
結論隻有一個，表定義中如果沒有使用varchar(max)型、僅僅是程序中的變量定義的話，修改成varchar(N)型，沒有證據表明會對性能產生什麽積極的影響。至於同事那個見鬼的5倍效果，隻有再驗證、再驗證、再驗證了。
頭疼。。。5倍的結果，已經報告給客戶了哦。。。
------------------------------------------------------------------------------
【注】通過反複的測試，終於發現了問題所在。問題出現在遊標循環中的一條集計查詢上：
SELECT @xx = count_big(*) FROM tbxxx WHERE 字段1 = @yy ... ...
當@yy 變量的類型定義是varchar(max)或varchar(N)時，MSSQL選擇了截然不同的兩種執行計劃。雖然都是用了同一個索引，而且是index seek，但varchar(max)時的執行計劃更複雜、更費時。加上實際項目中，這一條SQL文的被查詢表是一個10萬條記錄以上的較大的表，結果造成了執行時間的顯著增加。
結論：WHERE語句中盡量避免使用varchar(max)型的變量
評論
目前還沒有任何評論
登錄後才可評論.
