SQL数据库中滥用临时表和排序的解决优化是怎样的

135次阅读

共计 3696 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章给大家介绍 SQL 数据库中滥用临时表和排序的解决优化是怎样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

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

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

今天的案例

场景：

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

要求：

得到的结果排序应为：

x

xia

xiao

yx

即：

包含 x 字母的结果均应显示出来

首字母匹配的结果应该排在前面(如 x 开头)

在条件 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),

关于 SQL 数据库中滥用临时表和排序的解决优化是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

正文完