汉字转全拼音函数优化方案(SQLServer),值得你看看

去年1月份时写了一篇关于优化汉字转拼音函数的解决方案,当时重点在于优化所举案例只能解决汉字转为拼音的首字母。 前不久收到这样一条短消息 随之我从(CSDN,CNBLOG,51CTO等)搜了几个解决方案,性能确实都不大理想。 首先还是感谢园友这个问题,接下来我就从网

去年1月份时写了一篇关于优化汉字转拼音函数的解决方案,当时重点在于优化所举案例只能解决汉字转为拼音的首字母。

前不久收到这样一条短消息

随之我从(CSDN,CNBLOG,51CTO等)搜了几个解决方案,性能确实都不大理想。

首先还是感谢园友这个问题,接下来我就从网上抽取两个案例作优化说明

 

函数要求实现功能

select 函数名 ('你好,我是追索')

返回的结果(ni hao , wo shi zhui suo)

 

解决方案一：

解决方案一 

<span>/*</span><span><br> 根据汉字获取全拼<br> 1.生成所有读音临时表<br> 2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音<br></span><span>*/</span><br>create <span>function</span> dbo.fn_Getquanpin1(@str varchar(100))<br>returns varchar(8000)<br>as<br>begin<br> declare @re varchar(8000)<br> --生成临时表<br> declare @t table(chr nchar(1) collate  Chinese_PRC_CS_AS_KS_WS,py nvarchar(20)) <br> insert into @t select'吖','a' <br> insert into @t select'厑','aes' <br> insert into @t select'哎','ai' <br> insert into @t select'安','an' <br> insert into @t select'肮','ang' <br> insert into @t select'凹','ao' <br> insert into @t select'八','ba' <br> insert into @t select'挀','bai' <br> insert into @t select'兡','baike' <br> insert into @t select'瓸','baiwa' <br> insert into @t select'扳','ban' <br> insert into @t select'邦','bang' <br> insert into @t select'勹','bao' <br> insert into @t select'萡','be' <br> insert into @t select'陂','bei' <br> insert into @t select'奔','ben' <br> insert into @t select'伻','beng' <br> insert into @t select'皀','bi' <br> insert into @t select'边','bian' <br> insert into @t select'辪','uu' <br> insert into @t select'灬','biao' <br> insert into @t select'憋','bie' <br> insert into @t select'汃','bin' <br> insert into @t select'冫','bing' <br> insert into @t select'癶','bo' <br> insert into @t select'峬','bu' <br> insert into @t select'嚓','ca' <br> insert into @t select'偲','cai' <br> insert into @t select'乲','cal' <br> insert into @t select'参','can' <br> insert into @t select'仓','cang' <br> insert into @t select'撡','cao' <br> insert into @t select'冊','ce' <br> insert into @t select'膥','cen' <br> insert into @t select'噌','ceng' <br> insert into @t select'硛','ceok' <br> insert into @t select'岾','ceom' <br> insert into @t select'猠','ceon' <br> insert into @t select'乽','ceor' <br> insert into @t select'叉','cha' <br> insert into @t select'犲','chai' <br> insert into @t select'辿','chan' <br> insert into @t select'伥','chang' <br> insert into @t select'抄','chao' <br> insert into @t select'车','che' <br> insert into @t select'抻','chen' <br> insert into @t select'阷','cheng' <br> insert into @t select'吃','chi' <br> insert into @t select'充','chong' <br> insert into @t select'抽','chou' <br> insert into @t select'出','chu' <br> insert into @t select'膗','chuai' <br> insert into @t select'巛','chuan' <br> insert into @t select'刅','chuang' <br> insert into @t select'吹','chui' <br> insert into @t select'旾','chun' <br> insert into @t select'踔','chuo' <br> insert into @t select'呲','ci' <br> insert into @t select'嗭','cis' <br> insert into @t select'从','cong' <br> insert into @t select'凑','cou' <br> insert into @t select'粗','cu' <br> insert into @t select'汆','cuan' <br> insert into @t select'崔','cui' <br> insert into @t select'邨','cun' <br> insert into @t select'瑳','cuo' <br> insert into @t select'撮','chua' <br> insert into @t select'咑','da' <br> insert into @t select'呔','dai' <br> insert into @t select'丹','dan' <br> insert into @t select'当','dang' <br> insert into @t select'刀','dao' <br> insert into @t select'恴','de' <br> insert into @t select'揼','dem' <br> insert into @t select'扥','den' <br> insert into @t select'灯','deng' <br> insert into @t select'仾','di' <br> insert into @t select'嗲','dia' <br> insert into @t select'敁','dian' <br> insert into @t select'刁','diao' <br> insert into @t select'爹','die' <br> insert into @t select'哋','dei' <br> insert into @t select'嚸','dim' <br> insert into @t select'丁','ding' <br> insert into @t select'丟','diu' <br> insert into @t select'东','dong' <br> insert into @t select'吺','dou' <br> insert into @t select'剢','du' <br> insert into @t select'耑','duan' <br> insert into @t select'叾','dug' <br> insert into @t select'垖','dui' <br> insert into @t select'吨','dun' <br> insert into @t select'咄','duo' <br> insert into @t select'妸','e' <br> insert into @t select'奀','en' <br> insert into @t select'鞥','eng' <br> insert into @t select'仒','eo' <br> insert into @t select'乻','eol' <br> insert into @t select'旕','eos' <br> insert into @t select'儿','er' <br> insert into @t select'发','fa' <br> insert into @t select'帆','fan' <br> insert into @t select'匚','fang' <br> insert into @t select'飞','fei' <br> insert into @t select'吩','fen' <br> insert into @t select'丰','feng' <br> insert into @t select'瓰','fenwa' <br> insert into @t select'覅','fiao' <br> insert into @t select'仏','fo' <br> insert into @t select'垺','fou' <br> insert into @t select'夫','fu' <br> insert into @t select'猤','fui' <br> insert into @t select'旮','ga' <br> insert into @t select'侅','gai' <br> insert into @t select'甘','gan' <br> insert into @t select'冈','gang' <br> insert into @t select'皋','gao' <br> insert into @t select'戈','ge' <br> insert into @t select'给','gei' <br> insert into @t select'根','gen' <br> insert into @t select'更','geng' <br> insert into @t select'啹','geu' <br> insert into @t select'喼','gib' <br> insert into @t select'嗰','go' <br> insert into @t select'工','gong' <br> insert into @t select'兝','gongfen' <br> insert into @t select'兣','gongli' <br> insert into @t select'勾','gou' <br> insert into @t select'估','gu' <br> insert into @t select'瓜','gua' <br> insert into @t select'乖','guai' <br> insert into @t select'关','guan' <br> insert into @t select'光','guang' <br> insert into @t select'归','gui' <br> insert into @t select'丨','gun' <br> insert into @t select'呙','guo' <br> insert into @t select'妎','ha' <br> insert into @t select'咍','hai' <br> insert into @t select'乤','hal' <br> insert into @t select'兯','han' <br> insert into @t select'魧','hang' <br> insert into @t select'茠','hao' <br> insert into @t select'兞','haoke' <br> insert into @t select'诃','he' <br> insert into @t select'黒','hei' <br> insert into @t select'拫','hen' <br> insert into @t select'亨','heng' <br> insert into @t select'囍','heui' <br> insert into @t select'乊','ho' <br> insert into @t select'乥','hol' <br> insert into @t select'叿','hong' <br> insert into @t select'齁','hou' <br> insert into @t select'乎','hu' <br> insert into @t select'花','hua' <br> insert into @t select'徊','huai' <br> insert into @t select'欢','huan' <br> insert into @t select'巟','huang' <br> insert into @t select'灰','hui' <br> insert into @t select'昏','hun' <br> insert into @t select'吙','huo' <br> insert into @t select'嚿','geo' <br> insert into @t select'夻','hwa' <br> insert into @t select'丌','ji' <br> insert into @t select'加','jia' <br> insert into @t select'嗧','jialun' <br> insert into @t select'戋','jian' <br> insert into @t select'江','jiang' <br> insert into @t select'艽','jiao' <br> insert into @t select'阶','jie' <br> insert into @t select'巾','jin' <br> insert into @t select'坕','jing' <br> insert into @t select'冂','jiong' <br> insert into @t select'丩','jiu' <br> insert into @t select'欍','jou' <br> insert into @t select'凥','ju' <br> insert into @t select'姢','juan' <br> insert into @t select'噘','jue' <br> insert into @t select'军','jun' <br> insert into @t select'咔','ka' <br> insert into @t select'开','kai' <br> insert into @t select'乫','kal' <br> insert into @t select'刊','kan' <br> insert into @t select'冚','hem' <br> insert into @t select'砊','kang' <br> insert into @t select'尻','kao' <br> insert into @t select'坷','ke' <br> insert into @t select'肎','ken' <br> insert into @t select'劥','keng' <br> insert into @t select'巪','keo' <br> insert into @t select'乬','keol' <br> insert into @t select'唟','keos' <br> insert into @t select'厼','keum' <br> insert into @t select'怾','ki' <br> insert into @t select'空','kong' <br> insert into @t select'廤','kos' <br> insert into @t select'抠','kou' <br> insert into @t select'扝','ku' <br> insert into @t select'夸','kua' <br> insert into @t select'蒯','kuai' <br> insert into @t select'宽','kuan' <br> insert into @t select'匡','kuang' <br> insert into @t select'亏','kui' <br> insert into @t select'坤','kun' <br> insert into @t select'拡','kuo' <br> insert into @t select'穒','kweok' <br> insert into @t select'垃','la' <br> insert into @t select'来','lai' <br> insert into @t select'兰','lan' <br> insert into @t select'啷','lang' <br> insert into @t select'捞','lao' <br> insert into @t select'仂','le' <br> insert into @t select'雷','lei' <br> insert into @t select'塄','leng' <br> insert into @t select'唎','li' <br> insert into @t select'俩','lia' <br> insert into @t select'嫾','lian' <br> insert into @t select'簗','liang' <br> insert into @t select'蹽','liao' <br> insert into @t select'毟','lie' <br> insert into @t select'厸','lin' <br> insert into @t select'伶','ling' <br> insert into @t select'溜','liu' <br> insert into @t select'瓼','liwa' <br> insert into @t select'囖','lo' <br> insert into @t select'龙','long' <br> insert into @t select'娄','lou' <br> insert into @t select'噜','lu' <br> insert into @t select'驴','lv' <br> insert into @t select'寽','lue' <br> insert into @t select'孪','luan' <br> insert into @t select'掄','lun' <br> insert into @t select'頱','luo' <br> insert into @t select'呣','m' <br> insert into @t select'妈','ma' <br> insert into @t select'遤','hweong' <br> insert into @t select'埋','mai' <br> insert into @t select'颟','man' <br> insert into @t select'牤','mang' <br> insert into @t select'匁','mangmi' <br> insert into @t select'猫','mao' <br> insert into @t select'唜','mas' <br> insert into @t select'庅','me' <br> insert into @t select'呅','mei' <br> insert into @t select'椚','men' <br> insert into @t select'掹','meng' <br> insert into @t select'踎','meo' <br> insert into @t select'瞇','mi' <br> insert into @t select'宀','mian' <br> insert into @t select'喵','miao' <br> insert into @t select'乜','mie' <br> insert into @t select'瓱','miliklanm' <br> insert into @t select'民','min' <br> insert into @t select'冧','lem' <br> insert into @t select'名','ming' <br> insert into @t select'谬','miu' <br> insert into @t select'摸','mo' <br> insert into @t select'乮','mol' <br> insert into @t select'哞','mou' <br> insert into @t select'母','mu' <br> insert into @t select'旀','myeo' <br> insert into @t select'丆','myeon' <br> insert into @t select'椧','myeong' <br> insert into @t select'拏','na' <br> insert into @t select'腉','nai' <br> insert into @t select'囡','nan' <br> insert into @t select'囔','nang' <br> insert into @t select'乪','keg' <br> insert into @t select'孬','nao' <br> insert into @t select'疒','ne' <br> insert into @t select'娞','nei' <br> insert into @t select'焾','nem' <br> insert into @t select'嫩','nen' <br> insert into @t select'莻','neus' <br> insert into @t select'鈪','ngag' <br> insert into @t select'銰','ngai' <br> insert into @t select'啱','ngam' <br> insert into @t select'妮','ni' <br> insert into @t select'年','nian' <br> insert into @t select'娘','niang' <br> insert into @t select'茑','niao' <br> insert into @t select'捏','nie' <br> insert into @t select'脌','nin' <br> insert into @t select'宁','ning' <br> insert into @t select'牛','niu' <br> insert into @t select'农','nong' <br> insert into @t select'羺','nou' <br> insert into @t select'奴','nu' <br> insert into @t select'女','nv' <br> insert into @t select'疟','nue' <br> insert into @t select'瘧','nve' <br> insert into @t select'奻','nuan' <br> insert into @t select'黁','nun' <br> insert into @t select'燶','nung' <br> insert into @t select'挪','nuo' <br> insert into @t select'筽','o' <br> insert into @t select'夞','oes' <br> insert into @t select'乯','ol' <br> insert into @t select'鞰','on' <br> insert into @t select'讴','ou' <br> insert into @t select'妑','pa' <br> insert into @t select'俳','pai' <br> insert into @t select'磗','pak' <br> insert into @t select'眅','pan' <br> insert into @t select'乓','pang' <br> insert into @t select'抛','pao' <br> insert into @t select'呸','pei' <br> insert into @t select'瓫','pen' <br> insert into @t select'匉','peng' <br> insert into @t select'浌','peol' <br> insert into @t select'巼','phas' <br> insert into @t select'闏','phdeng' <br> insert into @t select'乶','phoi' <br> insert into @t select'喸','phos' <br> insert into @t select'丕','pi' <br> insert into @t select'囨','pian' <br> insert into @t select'缥','piao' <br> insert into @t select'氕','pie' <br> insert into @t select'丿','pianpang' <br> insert into @t select'姘','pin' <br> insert into @t select'乒','ping' <br> insert into @t select'钋','po' <br> insert into @t select'剖','pou' <br> insert into @t select'哣','deo' <br> insert into @t select'兺','ppun' <br> insert into @t select'仆','pu' <br> insert into @t select'七','qi' <br> insert into @t select'掐','qia' <br> insert into @t select'千','qian' <br> insert into @t select'羌','qiang' <br> insert into @t select'兛','qianke' <br> insert into @t select'瓩','qianwa' <br> insert into @t select'悄','qiao' <br> insert into @t select'苆','qie' <br> insert into @t select'亲','qin' <br> insert into @t select'蠄','kem' <br> insert into @t select'氢','qing' <br> insert into @t select'銎','qiong' <br> insert into @t select'丘','qiu' <br> insert into @t select'曲','qu' <br> insert into @t select'迲','keop' <br> insert into @t select'峑','quan' <br> insert into @t select'蒛','que' <br> insert into @t select'夋','qun' <br> insert into @t select'亽','ra' <br> insert into @t select'囕','ram' <br> insert into @t select'呥','ran' <br> insert into @t select'穣','rang' <br> insert into @t select'荛','rao' <br> insert into @t select'惹','re' <br> insert into @t select'人','ren' <br> insert into @t select'扔','reng' <br> insert into @t select'日','ri' <br> insert into @t select'栄','rong' <br> insert into @t select'禸','rou' <br> insert into @t select'嶿','ru' <br> insert into @t select'撋','ruan' <br> insert into @t select'桵','rui' <br> insert into @t select'闰','run' <br> insert into @t select'叒','ruo' <br> insert into @t select'仨','sa' <br> insert into @t select'栍','saeng' <br> insert into @t select'毢','sai' <br> insert into @t select'虄','sal' <br> insert into @t select'三','san' <br> insert into @t select'桒','sang' <br> insert into @t select'掻','sao' <br> insert into @t select'色','se' <br> insert into @t select'裇','sed' <br> insert into @t select'聓','sei' <br> insert into @t select'森','sen' <br> insert into @t select'鬙','seng' <br> insert into @t select'閪','seo' <br> insert into @t select'縇','seon' <br> insert into @t select'杀','sha' <br> insert into @t select'筛','shai' <br> insert into @t select'山','shan' <br> insert into @t select'伤','shang' <br> insert into @t select'弰','shao' <br> insert into @t select'奢','she' <br> insert into @t select'申','shen' <br> insert into @t select'升','sheng' <br> insert into @t select'尸','shi' <br> insert into @t select'兙','shike' <br> insert into @t select'瓧','shiwa' <br> insert into @t select'収','shou' <br> insert into @t select'书','shu' <br> insert into @t select'刷','shua' <br> insert into @t select'摔','shuai' <br> insert into @t select'闩','shuan' <br> insert into @t select'双','shuang' <br> insert into @t select'谁','shei' <br> insert into @t select'脽','shui' <br> insert into @t select'吮','shun' <br> insert into @t select'哾','shuo' <br> insert into @t select'丝','si' <br> insert into @t select'螦','so' <br> insert into @t select'乺','sol' <br> insert into @t select'忪','song' <br> insert into @t select'凁','sou' <br> insert into @t select'苏','su' <br> insert into @t select'痠','suan' <br> insert into @t select'夊','sui' <br> insert into @t select'孙','sun' <br> insert into @t select'娑','suo' <br> insert into @t select'他','ta' <br> insert into @t select'襨','tae' <br> insert into @t select'囼','tai' <br> insert into @t select'坍','tan' <br> insert into @t select'铴','tang' <br> insert into @t select'仐','tao' <br> insert into @t select'畓','tap' <br> insert into @t select'忒','te' <br> insert into @t select'膯','teng' <br> insert into @t select'唞','teo' <br> insert into @t select'朰','teul' <br> insert into @t select'剔','ti' <br> insert into @t select'天','tian' <br> insert into @t select'旫','tiao' <br> insert into @t select'怗','tie' <br> insert into @t select'厅','ting' <br> insert into @t select'乭','tol' <br> insert into @t select'囲','tong' <br> insert into @t select'偷','tou' <br> insert into @t select'凸','tu' <br> insert into @t select'湍','tuan' <br> insert into @t select'推','tui' <br> insert into @t select'旽','tun' <br> insert into @t select'乇','tuo' <br> insert into @t select'屲','wa' <br> insert into @t select'歪','wai' <br> insert into @t select'乛','wan' <br> insert into @t select'尣','wang' <br> insert into @t select'危','wei' <br> insert into @t select'塭','wen' <br> insert into @t select'翁','weng' <br> insert into @t select'挝','wo' <br> insert into @t select'乌','wu' <br> insert into @t select'夕','xi' <br> insert into @t select'诶','ei' <br> insert into @t select'疨','xia' <br> insert into @t select'仙','xian' <br> insert into @t select'乡','xiang' <br> insert into @t select'灱','xiao' <br> insert into @t select'楔','xie' <br> insert into @t select'心','xin' <br> insert into @t select'星','xing' <br> insert into @t select'凶','xiong' <br> insert into @t select'休','xiu' <br> insert into @t select'旴','xu' <br> insert into @t select'昍','xuan' <br> insert into @t select'疶','xue' <br> insert into @t select'坃','xun' <br> insert into @t select'丫','ya' <br> insert into @t select'咽','yan' <br> insert into @t select'欕','eom' <br> insert into @t select'央','yang' <br> insert into @t select'吆','yao' <br> insert into @t select'椰','ye' <br> insert into @t select'膶','yen' <br> insert into @t select'一','yi' <br> insert into @t select'乁','i' <br> insert into @t select'乚','yin' <br> insert into @t select'应','ying' <br> insert into @t select'哟','yo' <br> insert into @t select'佣','yong' <br> insert into @t select'优','you' <br> insert into @t select'迂','yu' <br> insert into @t select'囦','yuan' <br> insert into @t select'曰','yue' <br> insert into @t select'蒀','yun' <br> insert into @t select'帀','za' <br> insert into @t select'災','zai' <br> insert into @t select'兂','zan' <br> insert into @t select'牂','zang' <br> insert into @t select'遭','zao' <br> insert into @t select'啫','ze' <br> insert into @t select'贼','zei' <br> insert into @t select'怎','zen' <br> insert into @t select'曽','zeng' <br> insert into @t select'吒','zha' <br> insert into @t select'甴','gad' <br> insert into @t select'夈','zhai' <br> insert into @t select'毡','zhan' <br> insert into @t select'张','zhang' <br> insert into @t select'钊','zhao' <br> insert into @t select'蜇','zhe' <br> insert into @t select'贞','zhen' <br> insert into @t select'凧','zheng' <br> insert into @t select'之','zhi' <br> insert into @t select'中','zhong' <br> insert into @t select'州','zhou' <br> insert into @t select'劯','zhu' <br> insert into @t select'抓','zhua' <br> insert into @t select'专','zhuan' <br> insert into @t select'转','zhuai' <br> insert into @t select'妆','zhuang' <br> insert into @t select'骓','zhui' <br> insert into @t select'宒','zhun' <br> insert into @t select'卓','zhuo' <br> insert into @t select'孜','zi' <br> insert into @t select'唨','zo' <br> insert into @t select'宗','zong' <br> insert into @t select'棸','zou' <br> insert into @t select'哫','zu' <br> insert into @t select'劗','zuan' <br> insert into @t select'厜','zui' <br> insert into @t select'尊','zun' <br> insert into @t select'昨','zuo' <br> <br> declare @strlen int <br> select @strlen=len(@str),@re=''<br> <span>while</span> @strlen>0<br> begin     <br>      select top 1 @re=py+' '+@re,@strlen=@strlen-1 <br>      from @t a where chr      order by chr collate Chinese_PRC_CS_AS_KS_WS  desc <br>      <span>if</span> @@rowcount=0<br>        select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1<br>   end<br> <span>return</span>(@re)<br>end

测试了一串字符这个案例确实能达到汉字转拼音要求,但当我看到此函数内declare table、insert、order by等等这些耗性能的关键词让我感到头疼

我们还是来看看它的执行开销

估计子树大小大于5,这是多么惊人的一个数字。此执行计划后面跟着茫茫多的线条几乎全是表插入

我用一万条数据测试该函数等了我1分多钟都没执行完毕,可见该解决方案性能差到极点。

 

接下来我们看看解决方案二

解决方案二

create <span>function</span> [dbo].[fn_Getquanpin2](@words nvarchar(2000))   <br>returns varchar(8000)   <br>as <br>begin  <br>    declare @word nchar(1)   <br>    declare @pinyin varchar(8000)   <br>    declare @i int <br>    declare @words_len int <br>    declare @unicode int <br>    <span>set</span> @i = 1   <br>    <span>set</span> @words = ltrim(rtrim(@words))   <br>    <span>set</span> @words_len = len(@words)   <br>    <span>while</span> (@i     begin  <br>    <span>set</span> @word = substring(@words, @i, 1)   <br>    <span>set</span> @unicode = unicode(@word)   <br>    <span>set</span> @pinyin = ISNULL(@pinyin +SPACE(1),'')+    <br>    (<span>case</span> when unicode(@word) between 19968 and 19968+20901 then   <br>    (select top 1 py from (   <br>    select 'a' as py,N'厑' as word   <br>    union all select 'ai',N'靉' <br>    union all select 'an',N'黯' <br>    union all select 'ang',N'醠' <br>    union all select 'ao',N'驁' <br>    union all select 'ba',N'欛' <br>    union all select 'bai',N'瓸' --韛兡瓸  <br>    union all select 'ban',N'瓣' <br>    union all select 'bang',N'鎊' <br>    union all select 'bao',N'鑤' <br>    union all select 'bei',N'鐾' <br>    union all select 'ben',N'輽' <br>    union all select 'beng',N'鏰' <br>    union all select 'bi',N'鼊' <br>    union all select 'bian',N'變' <br>    union all select 'biao',N'鰾' <br>    union all select 'bie',N'彆' <br>    union all select 'bin',N'鬢' <br>    union all select 'bing',N'靐' <br>    union all select 'bo',N'蔔' <br>    union all select 'bu',N'簿' <br>    union all select 'ca',N'囃' <br>    union all select 'cai',N'乲' --縩乲  <br>    union all select 'can',N'爘' <br>    union all select 'cang',N'賶' <br>    union all select 'cao',N'鼜' <br>    union all select 'ce',N'簎' <br>    union all select 'cen',N'笒' <br>    union all select 'ceng',N'乽' --硛硳岾猠乽  <br>    union all select 'cha',N'詫' <br>    union all select 'chai',N'囆' <br>    union all select 'chan',N'顫' <br>    union all select 'chang',N'韔' <br>    union all select 'chao',N'觘' <br>    union all select 'che',N'爡' <br>    union all select 'chen',N'讖' <br>    union all select 'cheng',N'秤' <br>    union all select 'chi',N'鷘' <br>    union all select 'chong',N'銃' <br>    union all select 'chou',N'殠' <br>    union all select 'chu',N'矗' <br>    union all select 'chuai',N'踹' <br>    union all select 'chuan',N'鶨' <br>    union all select 'chuang',N'愴' <br>    union all select 'chui',N'顀' <br>    union all select 'chun',N'蠢' <br>    union all select 'chuo',N'縒' <br>    union all select 'ci',N'嗭' --賜嗭  <br>    union all select 'cong',N'謥' <br>    union all select 'cou',N'輳' <br>    union all select 'cu',N'顣' <br>    union all select 'cuan',N'爨' <br>    union all select 'cui',N'臎' <br>    union all select 'cun',N'籿' <br>    union all select 'cuo',N'錯' <br>    union all select 'da',N'橽' <br>    union all select 'dai',N'靆' <br>    union all select 'dan',N'饏' <br>    union all select 'dang',N'闣' <br>    union all select 'dao',N'纛' <br>    union all select 'de',N'的' <br>    union all select 'den',N'扽' <br>    union all select 'deng',N'鐙' <br>    union all select 'di',N'螮' <br>    union all select 'dia',N'嗲' <br>    union all select 'dian',N'驔' <br>    union all select 'diao',N'鑃' <br>    union all select 'die',N'嚸' --眰嚸  <br>    union all select 'ding',N'顁' <br>    union all select 'diu',N'銩' <br>    union all select 'dong',N'霘' <br>    union all select 'dou',N'鬭' <br>    union all select 'du',N'蠹' <br>    union all select 'duan',N'叾' --籪叾  <br>    union all select 'dui',N'譵' <br>    union all select 'dun',N'踲' <br>    union all select 'duo',N'鵽' <br>    union all select 'e',N'鱷' <br>    union all select 'en',N'摁' <br>    union all select 'eng',N'鞥' <br>    union all select 'er',N'樲' <br>    union all select 'fa',N'髮' <br>    union all select 'fan',N'瀪' <br>    union all select 'fang',N'放' <br>    union all select 'fei',N'靅' <br>    union all select 'fen',N'鱝' <br>    union all select 'feng',N'覅' <br>    union all select 'fo',N'梻' <br>    union all select 'fou',N'鴀' <br>    union all select 'fu',N'猤' --鰒猤  <br>    union all select 'ga',N'魀' <br>    union all select 'gai',N'瓂' <br>    union all select 'gan',N'灨' <br>    union all select 'gang',N'戇' <br>    union all select 'gao',N'鋯' <br>    union all select 'ge',N'獦' <br>    union all select 'gei',N'給' <br>    union all select 'gen',N'搄' <br>    union all select 'geng',N'堩' --亙堩啹喼嗰  <br>    union all select 'gong',N'兣' --熕贑兝兣  <br>    union all select 'gou',N'購' <br>    union all select 'gu',N'顧' <br>    union all select 'gua',N'詿' <br>    union all select 'guai',N'恠' <br>    union all select 'guan',N'鱹' <br>    union all select 'guang',N'撗' <br>    union all select 'gui',N'鱥' <br>    union all select 'gun',N'謴' <br>    union all select 'guo',N'腂' <br>    union all select 'ha',N'哈' <br>    union all select 'hai',N'饚' <br>    union all select 'han',N'鶾' <br>    union all select 'hang',N'沆' <br>    union all select 'hao',N'兞' <br>    union all select 'he',N'靏' <br>    union all select 'hei',N'嬒' <br>    union all select 'hen',N'恨' <br>    union all select 'heng',N'堼' --堼囍  <br>    union all select 'hong',N'鬨' <br>    union all select 'hou',N'鱟' <br>    union all select 'hu',N'鸌' <br>    union all select 'hua',N'蘳' <br>    union all select 'huai',N'蘾' <br>    union all select 'huan',N'鰀' <br>    union all select 'huang',N'鎤' <br>    union all select 'hui',N'顪' <br>    union all select 'hun',N'諢' <br>    union all select 'huo',N'夻' <br>    union all select 'ji',N'驥' <br>    union all select 'jia',N'嗧' <br>    union all select 'jian',N'鑳' <br>    union all select 'jiang',N'謽' <br>    union all select 'jiao',N'釂' <br>    union all select 'jie',N'繲' <br>    union all select 'jin',N'齽' <br>    union all select 'jing',N'竸' <br>    union all select 'jiong',N'蘔' <br>    union all select 'jiu',N'欍' <br>    union all select 'ju',N'爠' <br>    union all select 'juan',N'羂' <br>    union all select 'jue',N'钁' <br>    union all select 'jun',N'攈' <br>    union all select 'ka',N'鉲' <br>    union all select 'kai',N'乫' --鎎乫  <br>    union all select 'kan',N'矙' <br>    union all select 'kang',N'閌' <br>    union all select 'kao',N'鯌' <br>    union all select 'ke',N'騍' <br>    union all select 'ken',N'褃' <br>    union all select 'keng',N'鏗' --巪乬唟厼怾  <br>    union all select 'kong',N'廤' <br>    union all select 'kou',N'鷇' <br>    union all select 'ku',N'嚳' <br>    union all select 'kua',N'骻' <br>    union all select 'kuai',N'鱠' <br>    union all select 'kuan',N'窾' <br>    union all select 'kuang',N'鑛' <br>    union all select 'kui',N'鑎' <br>    union all select 'kun',N'睏' <br>    union all select 'kuo',N'穒' <br>    union all select 'la',N'鞡' <br>    union all select 'lai',N'籟' <br>    union all select 'lan',N'糷' <br>    union all select 'lang',N'唥' <br>    union all select 'lao',N'軂' <br>    union all select 'le',N'餎' <br>    union all select 'lei',N'脷' --嘞脷  <br>    union all select 'leng',N'睖' <br>    union all select 'li',N'瓈' <br>    union all select 'lia',N'倆' <br>    union all select 'lian',N'纞' <br>    union all select 'liang',N'鍄' <br>    union all select 'liao',N'瞭' <br>    union all select 'lie',N'鱲' <br>    union all select 'lin',N'轥' --轥拎  <br>    union all select 'ling',N'炩' <br>    union all select 'liu',N'咯' --瓼甅囖咯  <br>    union all select 'long',N'贚' <br>    union all select 'lou',N'鏤' <br>    union all select 'lu',N'氇' <br>    union all select 'lv',N'鑢' <br>    union all select 'luan',N'亂' <br>    union all select 'lue',N'擽' <br>    union all select 'lun',N'論' <br>    union all select 'luo',N'鱳' <br>    union all select 'ma',N'嘛' <br>    union all select 'mai',N'霢' <br>    union all select 'man',N'蘰' <br>    union all select 'mang',N'蠎' <br>    union all select 'mao',N'唜' <br>    union all select 'me',N'癦' --癦呅  <br>    union all select 'mei',N'嚜' <br>    union all select 'men',N'們' <br>    union all select 'meng',N'霥' --霿踎  <br>    union all select 'mi',N'羃' <br>    union all select 'mian',N'麵' <br>    union all select 'miao',N'廟' <br>    union all select 'mie',N'鱴' --鱴瓱  <br>    union all select 'min',N'鰵' <br>    union all select 'ming',N'詺' <br>    union all select 'miu',N'謬' <br>    union all select 'mo',N'耱' --耱乮  <br>    union all select 'mou',N'麰' --麰蟱  <br>    union all select 'mu',N'旀' <br>    union all select 'na',N'魶' <br>    union all select 'nai',N'錼' <br>    union all select 'nan',N'婻' <br>    union all select 'nang',N'齉' <br>    union all select 'nao',N'臑' <br>    union all select 'ne',N'呢' <br>    union all select 'nei',N'焾' --嫩焾  <br>    union all select 'nen',N'嫩' <br>    union all select 'neng',N'能' --莻嗯鈪銰啱  <br>    union all select 'ni',N'嬺' <br>    union all select 'nian',N'艌' <br>    union all select 'niang',N'釀' <br>    union all select 'niao',N'脲' <br>    union all select 'nie',N'钀' <br>    union all select 'nin',N'拰' <br>    union all select 'ning',N'濘' <br>    union all select 'niu',N'靵' <br>    union all select 'nong',N'齈' <br>    union all select 'nou',N'譳' <br>    union all select 'nu',N'搙' <br>    union all select 'nv',N'衄' <br>    union all select 'nue',N'瘧' <br>    union all select 'nuan',N'燶' --硸黁燶郍  <br>    union all select 'nuo',N'桛' <br>    union all select 'o',N'鞰' --毮夞乯鞰  <br>    union all select 'ou',N'漚' <br>    union all select 'pa',N'袙' <br>    union all select 'pai',N'磗' --鎃磗  <br>    union all select 'pan',N'鑻' <br>    union all select 'pang',N'胖' <br>    union all select 'pao',N'礮' <br>    union all select 'pei',N'轡' <br>    union all select 'pen',N'喯' <br>    union all select 'peng',N'喸' --浌巼闏乶喸  <br>    union all select 'pi',N'鸊' <br>    union all select 'pian',N'騙' <br>    union all select 'piao',N'慓' <br>    union all select 'pie',N'嫳' <br>    union all select 'pin',N'聘' <br>    union all select 'ping',N'蘋' <br>    union all select 'po',N'魄' <br>    union all select 'pou',N'哛' --兺哛  <br>    union all select 'pu',N'曝' <br>    union all select 'qi',N'蟿' <br>    union all select 'qia',N'髂' <br>    union all select 'qian',N'縴' <br>    union all select 'qiang',N'瓩' --羻兛瓩  <br>    union all select 'qiao',N'躈' <br>    union all select 'qie',N'籡' <br>    union all select 'qin',N'藽' <br>    union all select 'qing',N'櫦' <br>    union all select 'qiong',N'瓗' <br>    union all select 'qiu',N'糗' <br>    union all select 'qu',N'覻' <br>    union all select 'quan',N'勸' <br>    union all select 'que',N'礭' <br>    union all select 'qun',N'囕' <br>    union all select 'ran',N'橪' <br>    union all select 'rang',N'讓' <br>    union all select 'rao',N'繞' <br>    union all select 're',N'熱' <br>    union all select 'ren',N'餁' <br>    union all select 'reng',N'陾' <br>    union all select 'ri',N'馹' <br>    union all select 'rong',N'穃' <br>    union all select 'rou',N'嶿' <br>    union all select 'ru',N'擩' <br>    union all select 'ruan',N'礝' <br>    union all select 'rui',N'壡' <br>    union all select 'run',N'橍' --橍挼  <br>    union all select 'ruo',N'鶸' <br>    union all select 'sa',N'栍' --櫒栍  <br>    union all select 'sai',N'虄' --簺虄  <br>    union all select 'san',N'閐' <br>    union all select 'sang',N'喪' <br>    union all select 'sao',N'髞' <br>    union all select 'se',N'飋' --裇聓  <br>    union all select 'sen',N'篸' <br>    union all select 'seng',N'縇' --閪縇  <br>    union all select 'sha',N'霎' <br>    union all select 'shai',N'曬' <br>    union all select 'shan',N'鱔' <br>    union all select 'shang',N'緔' <br>    union all select 'shao',N'潲' <br>    union all select 'she',N'欇' <br>    union all select 'shen',N'瘮' <br>    union all select 'sheng',N'賸' <br>    union all select 'shi',N'瓧' --鰘齛兙瓧  <br>    union all select 'shou',N'鏉' <br>    union all select 'shu',N'虪' <br>    union all select 'shua',N'誜' <br>    union all select 'shuai',N'卛' <br>    union all select 'shuan',N'腨' <br>    union all select 'shuang',N'灀' <br>    union all select 'shui',N'睡' <br>    union all select 'shun',N'鬊' <br>    union all select 'shuo',N'鑠' <br>    union all select 'si',N'乺' --瀃螦乺  <br>    union all select 'song',N'鎹' <br>    union all select 'sou',N'瘶' <br>    union all select 'su',N'鷫' <br>    union all select 'suan',N'算' <br>    union all select 'sui',N'鐩' <br>    union all select 'sun',N'潠' <br>    union all select 'suo',N'蜶' <br>    union all select 'ta',N'襨' --躢襨  <br>    union all select 'tai',N'燤' <br>    union all select 'tan',N'賧' <br>    union all select 'tang',N'燙' <br>    union all select 'tao',N'畓' --討畓  <br>    union all select 'te',N'蟘' <br>    union all select 'teng',N'朰' --霯唞朰  <br>    union all select 'ti',N'趯' <br>    union all select 'tian',N'舚' <br>    union all select 'tiao',N'糶' <br>    union all select 'tie',N'餮' <br>    union all select 'ting',N'乭' --濎乭  <br>    union all select 'tong',N'憅' <br>    union all select 'tou',N'透' <br>    union all select 'tu',N'鵵' <br>    union all select 'tuan',N'褖' <br>    union all select 'tui',N'駾' <br>    union all select 'tun',N'坉' <br>    union all select 'tuo',N'籜' <br>    union all select 'wa',N'韤' <br>    union all select 'wai',N'顡' <br>    union all select 'wan',N'贎' <br>    union all select 'wang',N'朢' <br>    union all select 'wei',N'躛' <br>    union all select 'wen',N'璺' <br>    union all select 'weng',N'齆' <br>    union all select 'wo',N'齷' <br>    union all select 'wu',N'鶩' <br>    union all select 'xi',N'衋' <br>    union all select 'xia',N'鏬' <br>    union all select 'xian',N'鼸' <br>    union all select 'xiang',N'鱌' <br>    union all select 'xiao',N'斆' <br>    union all select 'xie',N'躞' <br>    union all select 'xin',N'釁' <br>    union all select 'xing',N'臖' <br>    union all select 'xiong',N'敻' <br>    union all select 'xiu',N'齅' <br>    union all select 'xu',N'蓿' <br>    union all select 'xuan',N'贙' <br>    union all select 'xue',N'瀥' <br>    union all select 'xun',N'鑂' <br>    union all select 'ya',N'齾' <br>    union all select 'yan',N'灩' <br>    union all select 'yang',N'樣' <br>    union all select 'yao',N'鑰' <br>    union all select 'ye',N'岃' --鸈膶岃  <br>    union all select 'yi',N'齸' <br>    union all select 'yin',N'檼' <br>    union all select 'ying',N'譍' <br>    union all select 'yo',N'喲' <br>    union all select 'yong',N'醟' <br>    union all select 'you',N'鼬' <br>    union all select 'yu',N'爩' <br>    union all select 'yuan',N'願' <br>    union all select 'yue',N'鸙' <br>    union all select 'yun',N'韻' <br>    union all select 'za',N'雥' <br>    union all select 'zai',N'縡' <br>    union all select 'zan',N'饡' <br>    union all select 'zang',N'臟' <br>    union all select 'zao',N'竈' <br>    union all select 'ze',N'稄' <br>    union all select 'zei',N'鱡' <br>    union all select 'zen',N'囎' <br>    union all select 'zeng',N'贈' <br>    union all select 'zha',N'醡' <br>    union all select 'zhai',N'瘵' <br>    union all select 'zhan',N'驏' <br>    union all select 'zhang',N'瞕' <br>    union all select 'zhao',N'羄' <br>    union all select 'zhe',N'鷓' <br>    union all select 'zhen',N'黮' <br>    union all select 'zheng',N'證' <br>    union all select 'zhi',N'豒' <br>    union all select 'zhong',N'諥' <br>    union all select 'zhou',N'驟' <br>    union all select 'zhu',N'鑄' <br>    union all select 'zhua',N'爪' <br>    union all select 'zhuai',N'跩' <br>    union all select 'zhuan',N'籑' <br>    union all select 'zhuang',N'戅' <br>    union all select 'zhui',N'鑆' <br>    union all select 'zhun',N'稕' <br>    union all select 'zhuo',N'籱' <br>    union all select 'zi',N'漬' --漬唨  <br>    union all select 'zong',N'縱' <br>    union all select 'zou',N'媰' <br>    union all select 'zu',N'謯' <br>    union all select 'zuan',N'攥' <br>    union all select 'zui',N'欈' <br>    union all select 'zun',N'銌' <br>    union all select 'zuo',N'咗') t    <br>    where word >= @word collate Chinese_PRC_CS_AS_KS_WS    <br>    order by word collate Chinese_PRC_CS_AS_KS_WS ASC) <span>else</span> @word end)   <br>    <span>set</span> @i = @i + 1   <br>    end  <br>    <span>return</span> @pinyin   <br>END 

测试了一串字符这个案例同样能达到汉字转拼音要求,该解决方案还好没让我看到表插入,这回性能应该大有提高

我们来看那看它的执行开销

果然不出所料,估计子树大小在0.017左右, 其中排序开销最大

我用一万条数据测试该函数花了10秒左右

既然排序开销最大那就优化该解决方案去掉order by。

这时估计子树大小就在0.00001之下了，可你会发现此解决方案再怎么优化还是免不了需要常量扫描,where筛选等运算.

我用一万条数据测试该优化后函数花了4～5秒左右

 

出于对性能要求的不满足,打开工具我自己写了一个汉字转拼音的函数

由于时间仓促未加任何注释,有问题可及时提出

最后给出的就是重写的解决方案

解决方案三

create <span>function</span> [dbo].[fn_Getquanpin3](@str varchar(100))<br>returns varchar(8000)<br>as<br>begin<br> declare @re varchar(8000),@crs varchar(10)<br> declare @strlen int <br> select @strlen=len(@str),@re=''<br> <span>while</span> @strlen>0<br> begin  <br>  <span>set</span> @crs= substring(@str,@strlen,1)<br>      select @re=<br>        <span>case</span><br>        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        when @crs        <span>else</span>  @crs end+' '+@re,@strlen=@strlen-1 <br>   end<br> <span>return</span>(@re)<br>end

我们来看它的执行计划

没有排序,没有常量扫描,没有筛选器,没有计算标量

估计子树大小为0

我用一万行测试数据花0~2秒就把所有汉字转换为拼音

 

------------------------------------------------------------------------------

好了,文章就到这里,快要过年了,祝各位园友们新年快乐。