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

转载 2011年01月18日 09:02:00

游标、临时表、触发器、COLLATE等等……
无可厚非、这些都是好东西,我为什么今天要花时间来写这些东西呢?
是因为我发现慢慢的很多人用久了这些东西之后会形成一种习惯,不管解决什么问题动不动都会把它们搬出来,由此我看到了很多漂亮的代码在性能效率面前却显得不那么优秀。

好了废话不多说开始进入正题吧。


今天的案例

场景:

需要通过用户输入的姓名关键字来搜索用户。用户输入关键字'x'来搜索用户(数据来源于表[Name字段中]或内存[List<UserInfo>]中)

要求:

得到的结果排序应为:

x

xia

xiao

yx

即:

  1. 包含x字母的结果均应显示出来
  2. 首字母匹配的结果应该排在前面(如x开头)
  3. 在条件2相同的前提下更短的结果应排在前面(如x排在xia前面)
各位大侠能否给出一套C#与SQL Server(2008)的解决方案?


补充:

如果能一起解决中文问题最好,如搜索'x'

得到的结果排序应为:

x

xiani

夏荣

肖小笑

杨星

即将汉字的拼音首字母纳入在内,不知SQL Server是否支持这一特性的搜索?

 

感谢[学习的脚步]这位网友提出来的问题

其实要解决这个问题不难,无非就是汉字转拼音首字母

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

先给出解决方案一

---------------------准备工作 开始-------------------------------
if object_id('zhuisuos')is not null
drop table zhuisuos
go

create table zhuisuos
(
name
varchar(100)
)
insert into zhuisuos values('追索')
insert into zhuisuos values('追索2')
insert into zhuisuos values('xia')
insert into zhuisuos values('dxc')
insert into zhuisuos values('x')
insert into zhuisuos values('xx')
insert into zhuisuos values('xiani')
insert into zhuisuos values('yx')
insert into zhuisuos values('夏荣')
insert into zhuisuos values('肖小笑')
insert into zhuisuos values('杨星')
go
-------------------------------------------------------------------------------
--
建立汉字转拼音首字母函数
if object_id('fn_getpy1')is not null
drop function fn_getpy1
go

GO
create   function   [dbo].fn_getpy1
(
@str   nvarchar(4000))
returns   nvarchar(4000)
as
begin
declare   @str_len   int,@result   nvarchar(4000)
declare   @zhuisuo   table
(firstspell  
nchar(1)   collate   Chinese_PRC_CI_AS,
letter  
nchar(1))
set @str_len=len(@str)
set @result= ' '
insert   into   @zhuisuo
(firstspell,letter)
   
select   '', 'A '   union   all   select   '', 'B '   union   all
   
select   '', 'C '   union   all   select   '', 'D '   union   all
   
select   '', 'E '   union   all   select   '', 'F '   union   all
   
select   '', 'G '   union   all   select   '', 'H '   union   all
   
select   '', 'J '   union   all   select   '', 'K '   union   all
   
select   '', 'L '   union   all   select   '', 'M '   union   all
   
select   '', 'N '   union   all   select   '', 'O '   union   all
   
select   '', 'P '   union   all   select   '', 'Q '   union   all
   
select   '', 'R '   union   all   select   '', 'S '   union   all
   
select   '', 'T '   union   all   select   '', 'W '   union   all
   
select   '', 'X '   union   all   select   '', 'Y '   union   all
   
select   '', 'Z '
   
while   @str_len> 0
   
begin
       
select   top   1   @result=letter+@result,@str_len=@str_len-1
           
from   @zhuisuo    
           
where   firstspell <=substring(@str,@str_len,1)
           
order   by   firstspell   desc
       
if   @@rowcount=0
         
select   @result=substring(@str,@str_len,1)+@result,@str_len=@str_len-1
   
end
   
return(@result)
end
---------------------准备工作 结束-------------------------------

--正式查询
declare @str varchar(10)
set @str='x'
create table #result
(name
varchar(100) null,id int null,lens int null)

insert into #result
select name,1,len(name) from zhuisuos
where name like @str+'%'

insert into #result
select name,2,len(name) from zhuisuos
where name like '%'+@str+'%' and name not like @str+'%'

insert into #result
select name,3,len(name) from zhuisuos
where dbo.fn_getpy1 (name) like @str+'%' and name not like @str+'%' and name not like '%'+@str+'%'

insert into #result
select name,4,len(name) from zhuisuos
where dbo.fn_getpy1 (name) like '%'+@str+'%' and dbo.fn_getpy1 (name) not like @str+'%'
 
and  name not like @str+'%' and name not like '%'+@str+'%'

select name from #result
order by id,lens
drop table #result

 

这个解决方案已经满足查询要求

其它都不管 我们重点来看看这次写的这个函数

象这样的汉字转拼音函数在网上一搜一大把 今天我就要举例几个方案让大家对优化及开销有个清楚的概念

 

 

解决方案一写的函数实在是太糟糕了(以上及接下来举出的案例并无冒犯任何雷同及原创代码之意,还请多多包涵)

 

为什么这么说呢

 

这是它的执行计划

它用了临时表并且排序

表插入开销0.01  表扫描开销0.003 表排序0.011

估计总开销0.0246

实际执行:我拿1万行数据调用此函数花了我20几秒、一个查询操作你愿意等20多秒吗

所以看到这样的执行计划实在很抱歉

 

解决方案二

create function [dbo].[fn_getpy2](@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
 
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
 
if @chn>'z'
 
select @n = @n +1
     ,
@c = case chn when @chn then char(@n) else @c end
  
from(
   
select top 27 * from (
    
select chn = ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''  --because have no 'i'
     union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select ''  --no 'u'
     union all select ''  --no 'v'
     union all select ''
    
union all select ''
    
union all select ''
    
union all select ''
    
union all select @chn) as a
   
order by chn COLLATE Chinese_PRC_CI_AS
   )
as b
 
else set @c=@chn
 
set @return=@return+@c
end
return(@return)
end

这是很聪明的一个解决方案,它巧妙的运用了排序使其利用序号位置int ASCII 代码转换为字母

这个方案能很漂亮的将汉字转为拼音

那么我们来看看它的执行计划是怎样的

 

看完之后也不得不为这个漂亮之举感到惋惜

排序开销0.01156

总估计开销大概0.01159

实际执行:我拿1万行数据调用此函数花了10几秒

当然它比解决方案一效率要高出一倍之多

 

解决方案三

既然解决方案一大部分开销花在表插入及排序上面那么我们把里面的临时表拿出来新建一个物理表并且建上主键让它聚集索引会怎样呢

 

create   function   [dbo].[fn_getpy3]
(
@str   nvarchar(4000))
returns   nvarchar(4000)
as
begin
declare   @str_len   int,@result   nvarchar(4000)

set @str_len=len(@str)
set @result= ' '

   
while   @str_len> 0
   
begin
       
select   top   1   @result=letter+@result,@str_len=@str_len-1
           
from   transition_spell    
           
where   firstspell <=substring(@str,@str_len,1)
           
order   by   firstspell   desc
       
if   @@rowcount=0
         
select   @result=substring(@str,@str_len,1)+@result,@str_len=@str_len-1
   
end
   
return(@result)
end

 

物理建表代码我就没有提供了 直接参考解决方案一临时表

 

果然,此方案总开销只花了0.003

实际执行:我拿1万行数据调用此函数花了4~5秒左右

没有了临时表,没有了插入,没有了排序这个简单的方法比漂亮的解决方案二效率更高

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

现在仔细想想 有没有什么方法能让它连聚集索引都不需要呢 这样岂不连0.003的开销都没有了?

 

刚才写出了解决方案四就实现了这一点

create   function   [dbo].[fn_getpy4]
(
@str   nvarchar(4000))
returns   nvarchar(4000)
as
begin
declare   @str_len   int,@result   nvarchar(4000) ,@crs nvarchar(1)

set @str_len=len(@str)
set @result= ' '

   
while   @str_len> 0
   
begin
set  @crs=substring(@str,@str_len,1)
--  @result=b+@result

  
select  @str_len=@str_len-1,@result=
    
case when @crs>='' and @crs<''then 'A'
    
when @crs>='' and @crs<''  then 'B'
    
when @crs>='' and @crs<''  then 'C'
    
when @crs>='' and @crs<''  then 'D'
    
when @crs>='' and @crs<''  then 'E'
    
when @crs>='' and @crs<''  then 'F'
    
when @crs>='' and @crs<''  then 'G'
    
when @crs>='' and @crs<''  then 'H'
    
when @crs>='' and @crs<''  then 'J'
    
when @crs>='' and @crs<''  then 'K'
    
when @crs>='' and @crs<''  then 'L'
    
when @crs>='' and @crs<''  then 'M'
    
when @crs>='' and @crs<''  then 'N'
    
when @crs>='' and @crs<''  then 'O'
    
when @crs>='' and @crs<''  then 'P'
    
when @crs>='' and @crs<''  then 'Q'
    
when @crs>='' and @crs<''  then 'R'
    
when @crs>='' and @crs<''  then 'S'
    
when @crs>='' and @crs<''  then 'T'
    
when @crs>='' and @crs<''  then 'W'
    
when @crs>='' and @crs<''  then 'X'
    
when @crs>='' and @crs<''  then 'Y'
    
when @crs>='' then 'Z'
    
else  @crs  end+@result
   
end
   
return(@result)
end

 

 

估计运算开销 0

实际执行:1万行数据调用此函数只花了1~2秒

 

这样就满足了?

其实解决方案四还有优化的空间、不过这次仅仅只是代码及逻辑上的优化

 

解决方案五

create   function   [dbo].[fn_getpy5]
(
@str   nvarchar(4000))
returns   nvarchar(4000)
as
begin
declare   @str_len   int,@result   nvarchar(4000) ,@crs nvarchar(1)
set @str_len=len(@str)
set @result= ' '
   
while   @str_len> 0
   
begin
set  @crs=substring(@str,@str_len,1)

  
select  @str_len=@str_len-1,@result=
    
case 
    
when @crs>='' then 'Z'
    
when @crs>='' then 'Y'
    
when @crs>='' then 'X'
    
when @crs>='' then 'W'
    
when @crs>='' then 'T'
    
when @crs>='' then 'S'
    
when @crs>='' then 'R'
    
when @crs>='' then 'Q'
    
when @crs>='' then 'P'
    
when @crs>='' then 'O'
    
when @crs>='' then 'N'
    
when @crs>='' then 'M'
    
when @crs>='' then 'L'
    
when @crs>='' then 'K'
    
when @crs>='' then 'J'
    
when @crs>='' then 'H'
    
when @crs>='' then 'G'
    
when @crs>='' then 'F'
    
when @crs>='' then 'E'
    
when @crs>='' then 'D'
    
when @crs>='' then 'C'
    
when @crs>='' then 'B'
    
when @crs>='' then 'A'
    
else  @crs  end+@result
   
end
   
return(@result)
end

 

估计运算开销 0

实际执行:1万行数据调用此函数0~1秒

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

 

好了,这些方案我都写完了、简单的总结一下。

其实不管你写了多少年的SQL有的时候不要养成一种先入为主、自作聪明的观念。

一个优秀的解决方案也许只需最简单的代码就能达到理想的效果。

所以谨以此篇文章来希望让更多的人看到,其实我们生活当中经常所遇到的问题往往都是我们无限的把它复杂化,严重化了。

退一步海阔天空、换个角度想想吧!

相关文章推荐

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

转自:http://www.cnblogs.com/zhuisuo/archive/2012/01/11/2318908.html create function [dbo].[fn_Getquan...

sql 汉字转拼音函数

/* -------------------------------------------------------------  函数: fn_GetPinyin  描述: 汉字转拼音(无数据表...

source insight 汉字问题的解决方案与函数调用关系图配置

【问题】 Source Insight里输入中文,字间距相当的大。 【答案】 1.Options->Style Properties 2. 在左边Style Name下找到Comment Mu...

PHP将汉字转化成拼音的函数(用于取首字母索引或按字母排序中文)

PHP将汉字转化成拼音的函数(用于取首字母索引或按字母排序中文) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 ...

MySQL数据库获取汉字拼音的首字母函数

需求简介:最近的一个项目,想实现如下图所示的显示效果。很明显,如果能够获取对应的汉字词组的拼音首字母就可以实现了,如果是固定的几个汉字,人为的拼一下就可以了,不过项目中有多处功能是需要这个效果的,并且...

win7临时用户登陆解决方案

  • 2014年11月13日 15:11
  • 475B
  • 下载

一个开发需求的解决方案 & Oracle临时表介绍

一、开发需求 最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出ID(主键)及一些主表字段,然后再用这些ID查找最多10张表中对应的记录,主表记录数大约2000万,每张子表的记录数均...
  • bisal
  • bisal
  • 2016年11月13日 23:39
  • 655

Oracle临时表游标未释放导致回滚段空间不足的解决方案

Oracle临时表游标未释放导致回滚段空间不足时会报出多个ORA-01650错误,造成数据库无法运行。本文我们主要就介绍了这一问题的解决方案,接下来就让我们来一起了解一下这部分内容。   先分析一下...

sql汉字转换成拼音的函数

  • 2014年05月30日 16:02
  • 13KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:谈谈SQL数据库中滥用临时表、排序的解决方案优化(举例:汉字转拼音函数)
举报原因:
原因补充:

(最多只允许输入30个字)