谈谈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有的时候不要养成一种先入为主、自作聪明的观念。

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

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

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

SQLServer汉字转全拼音函数

原文: http://blog.csdn.net/roy_88/article/details/8772631 最近用到项目,当输入错别字时也需要匹配,直接网上找到一篇直接改写加了拼音排序,...
  • yenange
  • yenange
  • 2017年04月17日 09:53
  • 449

PostgreSQL获取汉字拼音首字母函数

本文转自http://blog.qdac.cc/?p=1281 --© 2014,swish,原版首发:http://blog.qdac.cc/?p=1281,自由使用,保留版权 CREATE OR...
  • vnsoft
  • vnsoft
  • 2015年10月17日 21:38
  • 1449

MySQL中文汉字转拼音的自定义函数和使用实例(首字的首字母)

fristPinyin : 此函数是将一个中文字符串的第一个汉字转成拼音字母 (例如:"中国人"->Z) 复制代码 代码如下: CREATE FUNCTION `fristPinyin`(P_...
  • u012436346
  • u012436346
  • 2017年03月11日 16:40
  • 593

Oracle中文转拼音函数

最近两个项目都提出从中文转拼音的需求。我以前的做法是:从微软拼音输入法导出字库,建立中文拼音对照表,然后写一个函数从对照表查拼音。这种方法虽然思路简单,但是返回拼音的错误率太高,而且对每个字都要去查一...
  • wzy0623
  • wzy0623
  • 2016年12月28日 15:16
  • 3004

Mysql数据库 汉字转拼音全拼

-- 创建汉字拼音对照临时表 CREATE TABLE IF NOT EXISTS `t_base_pinyin` ( `pin_yin_` varchar(255) CHARACTER SET ...
  • u011713224
  • u011713224
  • 2014年02月13日 09:49
  • 1597

Java汉字排序(汉字转拼音用pinyin4j)

Pinyin4j中的pinyinutil类代码   package test;      import net.sourceforge.pinyin4j.PinyinHelper; ...
  • cc20032706
  • cc20032706
  • 2014年06月11日 16:02
  • 1202

根据汉字的拼音首字母搜索数据库(附 LINQ 调用方法)

我们经常需要使用拼音首字母来检索数据库,特别是应用于医院、商店等行业软件中。譬如搜索“zgr”就可以搜索所有包含“中国人”的记录。那么如果来实现才能即高效又方便呢? 方法一:先查询出所有记录,然...
  • u012543266
  • u012543266
  • 2014年03月26日 21:39
  • 2173

sql语句按照汉字拼音首字母排序

oracle : 在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值 SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序 SCHINES...
  • fygkchina
  • fygkchina
  • 2015年11月18日 16:17
  • 1174

SQL Server中如何把一个任意汉字转换为该汉字拼音第一个字母

其实我想写这篇文章已经很久了,只是各种条件限制,以至于我不能把我所学到觉得有益于大家的共享出来。我希望说的这个技术能对大家有用,以前我做一个大型系统的时候,曾经自己开发了一套控件,其中之一是可以模糊查...
  • Sorder
  • Sorder
  • 2005年01月06日 12:34
  • 1920

Android 拼音转汉字的第三方处理

一、前言 在我们的开发中,有时会有这样的需求,就是联系人列表按照拼音顺序排列(如通讯录)。于是,我也在网上搜到了许多这类的文章,就两种最常见的做法在此进行简单的比较和分析 二、汉字转拼音的方法 使用第...
  • wanniu
  • wanniu
  • 2017年03月23日 10:54
  • 536
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:谈谈SQL数据库中滥用临时表、排序的解决方案优化(举例:汉字转拼音函数)
举报原因:
原因补充:

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