SQL數(shù)據(jù)庫中濫用臨時(shí)表和排序的解決優(yōu)化是怎樣的

這篇文章給大家介紹SQL數(shù)據(jù)庫中濫用臨時(shí)表和排序的解決優(yōu)化是怎樣的，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

游標(biāo)、臨時(shí)表、觸發(fā)器、COLLATE等等
無可厚非、這些都是好東西，我為什么今天要花時(shí)間來寫這些東西呢？
是因?yàn)槲野l(fā)現(xiàn)慢慢的很多人用久了這些東西之后會形成一種習(xí)慣，不管解決什么問題動不動都會把它們搬出來，由此我看到了很多漂亮的代碼在性能效率面前卻顯得不那么優(yōu)秀。

好了廢話不多說開始進(jìn)入正題吧。

今天的案例

場景：

需要通過用戶輸入的姓名關(guān)鍵字來搜索用戶。用戶輸入關(guān)鍵字'x'來搜索用戶（數(shù)據(jù)來源于表[Name字段中]或內(nèi)存[List<UserInfo>]中）

要求：

得到的結(jié)果排序應(yīng)為：

x

xia

xiao

yx

即：

1. 包含x字母的結(jié)果均應(yīng)顯示出來

2. 首字母匹配的結(jié)果應(yīng)該排在前面(如x開頭)

3. 在條件2相同的前提下更短的結(jié)果應(yīng)排在前面(如x排在xia前面)

各位大俠能否給出一套C#與SQL Server(2008)的解決方案？

補(bǔ)充：

如果能一起解決中文問題最好，如搜索'x'

得到的結(jié)果排序應(yīng)為：

x

xiani

夏榮

肖小笑

楊星

即將漢字的拼音首字母納入在內(nèi)，不知SQL Server是否支持這一特性的搜索？

感謝[學(xué)習(xí)的腳步]這位網(wǎng)友提出來的問題

其實(shí)要解決這個(gè)問題不難，無非就是漢字轉(zhuǎn)拼音首字母

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

先給出解決方案一

---------------------準(zhǔn)備工作 開始-------------------------------
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
-------------------------------------------------------------------------------
--建立漢字轉(zhuǎn)拼音首字母函數(shù)
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ā) ', '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
---------------------準(zhǔn)備工作 結(jié)束-------------------------------

--正式查詢
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

這個(gè)解決方案已經(jīng)滿足查詢要求

其它都不管 我們重點(diǎn)來看看這次寫的這個(gè)函數(shù)

象這樣的漢字轉(zhuǎn)拼音函數(shù)在網(wǎng)上一搜一大把 今天我就要舉例幾個(gè)方案讓大家對優(yōu)化及開銷有個(gè)清楚的概念

解決方案一寫的函數(shù)實(shí)在是太糟糕了（以上及接下來舉出的案例并無冒犯任何雷同及原創(chuàng)代碼之意，還請多多包涵）

為什么這么說呢

這是它的執(zhí)行計(jì)劃

它用了臨時(shí)表并且排序

表插入開銷0.01  表掃描開銷0.003 表排序0.011

估計(jì)總開銷0.0246

實(shí)際執(zhí)行：我拿1萬行數(shù)據(jù)調(diào)用此函數(shù)花了我20幾秒、一個(gè)查詢操作你愿意等20多秒嗎

所以看到這樣的執(zhí)行計(jì)劃實(shí)在很抱歉

解決方案二

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),

關(guān)于SQL數(shù)據(jù)庫中濫用臨時(shí)表和排序的解決優(yōu)化是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。