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

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

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

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

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

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

函数要求实现功能

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

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

解决方案一：

解决方案一

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

 declare @strlen int 
 select @strlen=len(@str),@re=''
while @strlen>0
 begin     
      select top 1 @re=py+' '+@re,@strlen=@strlen-1 
      from @t a where chr      order by chr collate Chinese_PRC_CS_AS_KS_WS  desc 
if @@rowcount=0
        select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
   end
return(@re)
end

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

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

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

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

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

解决方案二

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

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

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

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

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

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

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

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

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

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

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

解决方案三

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

我们来看它的执行计划

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

估计子树大小为0

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

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

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