正文
概要组转发过来一封性能改善请求。有一组新划归我们维护的程序中使用了大量的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)型的变量
评论
目前还没有任何评论
登录后才可评论.
