来谈谈SQL数据库中滥用临时表、排序的解决方案优化(举例汉字转拼音函数)

来谈谈SQL数据库中滥用临时表、排序的解决方案优化(举例:汉字转拼音函数)游标、临时表、触发器、COLLATE等等...无可厚非、这些都是好东西我为什么今天要花时间来写这些东西呢？是因为我发现慢慢的很多人用久了这些东西之后会形成一种习惯，不管解决什么问题动不动都会把它们搬出来由此我看到了很多漂亮的代码在性能效率面前却显得不那么优秀好了废话不多说开始进入正题吧今天的案例场景：需要通过用户输入的姓名关键字来搜索用户。用户输入关键字'x'来搜索用户（数据来源于表[Name字段中]或内存[ListUserInfo]中）要求：得到的结果排序应为：xxiaxiaoyx即：1.包含x字母的结果均应显示出来2.首字母匹配的结果应该排在前面(如x开头)3.在条件2相同的前提下更短的结果应排在前面(如x排在xia前面)各位大侠能否给出一套c#与sqlserver(2008)的解决方案？补充：如果能一起解决中文问题最好，如搜索'x'得到的结果排序应为：xxiani夏荣肖小笑杨星即将汉字的拼音首字母纳入在内，不知sqlserver是否支持这一特性的搜索？感谢[学习的脚步]这位网友提出来的问题其实要解决这个问题不难，无非就是汉字转拼音首字母---------------------------------------------------------------------------------------------先给出解决方案一代码---------------------准备工作开始-------------------------------ifobject_id('zhuisuos')isnotnulldroptablezhuisuosgocreatetablezhuisuos(namevarchar(100))insertintozhuisuosvalues('追索')insertintozhuisuosvalues('追索2')insertintozhuisuosvalues('xia')insertintozhuisuosvalues('dxc')insertintozhuisuosvalues('x')insertintozhuisuosvalues('xx')insertintozhuisuosvalues('xiani')insertintozhuisuosvalues('yx')insertintozhuisuosvalues('夏荣')insertintozhuisuosvalues('肖小笑')insertintozhuisuosvalues('杨星')go---------------------------------------------------------------------------------建立汉字转拼音首字母函数ifobject_id('fn_getpy1')isnotnulldropfunctionfn_getpy1goGOcreatefunction[dbo].fn_getpy1(@strnvarchar(4000))returnsnvarchar(4000)asbegindeclare@str_lenint,@resultnvarchar(4000)declare@zhuisuotable(firstspellnchar(1)collateChinese_PRC_CI_AS,letternchar(1))set@str_len=len(@str)set@result=''insertinto@zhuisuo(firstspell,letter)select'吖','A'unionallselect'八','B'unionallselect'嚓','C'unionallselect'咑','D'unionallselect'妸','E'unionallselect'发','F'unionallselect'旮','G'unionallselect'铪','H'unionallselect'丌','J'unionallselect'咔','K'unionallselect'垃','L'unionallselect'嘸','M'unionallselect'拏','N'unionallselect'噢','O'unionallselect'妑','P'unionallselect'七','Q'unionallselect'呥','R'unionallselect'仨','S'unionallselect'他','T'unionallselect'屲','W'unionallselect'夕','X'unionallselect'丫','Y'unionallselect'帀','Z'while@str_len0beginselecttop1@result=letter+@result,@str_len=@str_len-1from@zhuisuowherefirstspell=substring(@str,@str_len,1)orderbyfirstspelldescif@@rowcount=0select@result=substring(@str,@str_len,1)+@result,@str_len=@str_len-1endreturn(@result)end---------------------准备工作结束---------------------------------正式查询declare@strvarchar(10)set@str='x'createtable#result(namevarchar(100)null,idintnull,lensintnull)insertinto#resultselectname,1,len(name)fromzhuisuoswherenamelike@str+'%'insertinto#resultselectname,2,len(name)fromzhuisuoswherenamelike'%'+@str+'%'andnamenotlike@str+'%'insertinto#resultselectname,3,len(name)fromzhuisuoswheredbo.fn_getpy1(name)like@str+'%'andnamenotlike@str+'%'andnamenotlike'%'+@str+'%'insertinto#resultselectname,4,len(name)fromzhuisuoswheredbo.fn_getpy1(name)like'%'+@str+'%'anddbo.fn_getpy1(name)notlike@str+'%'andnamenotlike@str+'%'andnamenotlike'%'+@str+'%'selectnamefrom#resultorderbyid,lensdroptable#result这个解决方案已经满足查询要求其它都不管我们重点来看看这次写的这个函数象这样的汉字转拼音函数在网上一搜一大把今天我就要举例几个方案让大家对优化及开销有个清楚的概念解决方案一写的函数实在是太糟糕了(以上及接下来举出的案例并无冒犯任何雷同及原创代码之意，还请多多包涵)为什么这么说呢这是它的执行计划它用了临时表并且排序表插入开销0.01表扫描开销0.003表排序0.011估计总开销0.0246实际执行：我拿1万行数据调用此函数花了我20几秒、一个查询操作你愿意等20多秒吗所以看到这样的执行计划实在很抱歉解决方案二代码createfunction[dbo].[fn_getpy2](@Strvarchar(500)='')returnsvarchar(500)asbegindeclare@strlenint,@returnvarchar(500),@iiintdeclare@nint,@cchar(1),@chnnchar(1)select@strlen=len(@str),@return='',@ii=0set@ii=0while@ii@strlenbeginselect@ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)if@chn'z'select@n=@n+1,@c=casechnwhen@chnthenchar(@n)else@cendfrom(selecttop27*from(selectchn='吖'unionallselect'八'unionallselect'嚓'unionallselect'咑'unionallselect'妸'unionallselect'发'unionallselect'旮'unionallselect'铪'unionallselect'丌'--becausehaveno'i'unionallselect'丌'unionallselect'咔'unionallselect'垃'unionallselect'嘸'unionallselect'拏'unionallselect'噢'unionallselect'妑'unionallselect'七'unionallselect'呥'unionallselect'仨'unionallselect'他'unionallselect'屲'--no'u'unionallselect'屲'--no'v'unionallselect'屲'unionallselect'夕'unionallselect'丫'unionallselect'帀'unionallselect@chn)asaorderbychnCOLLATEChinese_PRC_CI_AS)asbelseset@c=@chnset@return=@return+@cendreturn(@return)end这是很聪明的一个解决方案，它巧妙的运用了排序使其利用序号位置intASCII代码转换为字母这个方案能很漂亮的将汉字转为拼音那么我们来看看它的执行计划是怎样的看完之后也不得不为这个漂亮之举感到惋惜排序开销0.01156总估计开销大概0.01159实际执行：我拿1万行数据调用此函数花了10几秒当然它比解决方案一效率要高出一倍之多解决方案三既然解决方案一大部分开销花在表插入及排序上面那么我们把里面的临时表拿出来新建一个物理表并且建上主键让它聚集索引会怎样呢代码createfunction[dbo].[fn_getpy3](@strnvarchar(4000))returnsnvarchar(4000)asbegindeclare@str_lenint,@resultnvarchar(4000)set@str_len=len(@str)set@result=''while@str_len0beginselecttop1@result=letter+@result,@str_len=@str_len-1fromtransition_spellwherefirstspell=substring(@str,@str_len,1)orderbyfirstspelldescif@@rowcount=0select@result=substring(@str,@str_len,1)+@result,@str_len=@str_len-1endreturn(@result)end物理建表代码我就没有提供了直接参考解决方案一临时表果然，此方案总开销只花了0.003实际执行：我拿1万行数据调用此函数花了4～5秒左右没有了临时表，没有了插入，没有了排序这个简单的方法比漂亮的解决方案二效率更高---------------------------------------------------------------------------------------------------------------现在