SQLServer地址搜索功能優化

SQLServer地址搜索功能優化。本站提示廣大學習愛好者：（SQLServer地址搜索功能優化）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer地址搜索功能優化正文

這是一個很久以前的例子，如今在整理材料時有意發現，就拿出來再改寫分享。

1.需求

 1.1 根本需求： 依據輸出的地址關鍵字，搜索出完好的地址途徑，耗時要控制在幾十毫秒內。

 1.2 數據庫地址表構造和數據：

 表TBAddress

 表數據

 1.3 例子：

 e.g. 給出一個字符串如“廣 大”，找出地址全途徑中包括有“廣” 和“大”的一切地址，結果如下：

上面將經過4個辦法來完成，再剖析其中的功能優劣，然後選擇一個比擬優的辦法。

 2.創立表和拔出數據

 2.1 創立數據表TBAddress

use test;
go
/* create table */
if object_id('TBAddress') is not null
  drop table TBAddress;
go
create table TBAddress
(
 ID int ,
 Parent int not null ,
 LevelNo smallint not null ,
 Name nvarchar(50) not null ,
 constraint PK_TBAddress primary key ( ID )
);
go
create nonclustered index ix_TBAddress_Parent on TBAddress(Parent,LevelNo) include(Name) with(fillfactor=80,pad_index=on);
create nonclustered index ix_TBAddress_Name on TBAddress(Name)include(LevelNo)with(fillfactor=80,pad_index=on);
go

create table

2.2 拔出數據

use test
go
/*insert data*/
set nocount on
Begin Try
  Begin Tran
  Insert Into TBAddress ([ID],[Parent],[LevelNo],[Name])
    Select 1,0,0,N'中國' Union All 
    Select 2,1,1,N'直轄市' Union All 
    Select 3,1,1,N'遼寧省' Union All 
    Select 4,1,1,N'廣東省' Union All 
    ... ...
    Select 44740,930,4,N'奧依塔克鎮' Union All 
    Select 44741,932,4,N'巴音庫魯提鄉' Union All 
    Select 44742,932,4,N'吉根鄉' Union All 
    Select 44743,932,4,N'托雲鄉'
  Commit Tran
End Try
Begin Catch
  throw 50001,N'拔出數據過程中發生錯誤.' ,1
Rollback Tran
End Catch
go

附件： insert Data

 Note: 數據有44700條，insert代碼比擬長，所以采用附件方式。

3.測試，辦法1

3.1 剖析：

a. 先搜索出包字段Name中含有“廣”、“大”的一切地址記載存入暫時表#tmp。

  b. 再找出#tmp中各個地址到Level 1的全途徑。

    c. 依據步驟2所得的後果，挑選出包括有“廣”和“大”的地址途徑。

d. 依據步驟3挑選的後果，查詢一切到Level n（n為沒有子地址的層編號）的地址全途徑。

3.2 存儲進程代碼：

Use test
Go
if object_ID('[up_SearchAddressByNameV0]') is not null
  Drop Procedure [up_SearchAddressByNameV0]
Go
create proc up_SearchAddressByNameV0 
(
  @Name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max)
 
declare @tmp Table (Name nvarchar(50))
 
set @Name=@Name+' '
 
while patindex('% %',@Name)>0
begin
  set @Name=replace(@Name,' ',' ')  
end
 
set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+''''
insert into @tmp(Name) exec(@sql)
 
if object_id('tempdb..#tmp') is not null drop table #tmp
if object_id('tempdb..#') is not null drop table #
 
create table #tmp(ID int )
 
 
while @Name>''
begin
  insert into #tmp(ID)
  select a.ID from TBAddress a where a.Name like '%'+substring(@Name,1,patindex('% %',@Name)-1)+'%' 
 
  set @Name=Stuff(@Name,1,patindex('% %',@Name),'')
end
 
 
;with cte_SearchParent as
(
  select a.ID,a.Parent,a.LevelNo,convert(nvarchar(500),a.Name) as AddressPath from TBAddress a where exists(select 1 from #tmp x where a.ID=x.ID) 
  union all
  select a.ID,b.Parent,b.LevelNo,convert(nvarchar(500),b.Name+'/'+a.AddressPath) as AddressPath
    from cte_SearchParent a
    inner join TBAddress b on b.ID=a.Parent
      --and b.LevelNo=a.LevelNo -1
      and b.LevelNo>=1
)
select a.ID,a.AddressPath 
  into #
  from cte_SearchParent a 
  where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp))
 
;with cte_result as
(
  select a.ID,a.LevelNo,b.AddressPath
    from TBAddress a 
      inner join # b on b.ID=a.ID
  union all
  select b.ID,b.LevelNo,convert(nvarchar(500),a.AddressPath+'/'+b.Name) As AddressPath
    from cte_result a
      inner join TBAddress b on b.Parent=a.ID
        --and b.LevelNo=a.LevelNo+1
            
)
select distinct a.ID,a.AddressPath 
  from cte_result a 
  where not exists(select 1 from TBAddress x where x.Parent=a.ID)
  order by a.AddressPath 
Go

procedure:up_SearchAddressByNameV0

 3.3 執行查詢：

exec up_SearchAddressByNameV0 '廣 大'

共前往195行記載。

3.4 客戶端統計信息：

均勻的執行耗時:  244毫秒

4.測試，辦法2

 辦法2是參照辦法1，並借助全文索引來優化辦法1中的步驟1。也就是在name列上樹立全文索引，在步驟1中，經過全文索引搜索出包字段Name中含有“廣”、“大”的一切地址記載存入暫時表#tmp，其他步驟堅持不變。

 4.1 創立全文索引

use test
go
/*create fulltext index*/
if not exists(select 1 from sys.fulltext_catalogs a where a.name='ftCatalog')
begin
create fulltext catalog ftCatalog As default;
end
go
--select * From sys.fulltext_languages    
create fulltext index on TBAddress(Name language 2052 ) key index PK_TBAddress
go   
alter fulltext index on dbo.TBAddress add(Fullpath language 2052)
go

Note:  在Name列上創立全文索引運用的言語是簡體中文（Simplified Chinese）

4.2 存儲進程代碼：

Use test
Go
if object_ID('[up_SearchAddressByNameV1]') is not null
  Drop Procedure [up_SearchAddressByNameV1]
Go
create proc up_SearchAddressByNameV1 
(
  @Name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max),@contains nvarchar(500)
 
declare @tmp Table (Name nvarchar(50))
 
while patindex('% %',@Name)>0
begin
  set @Name=replace(@Name,' ',' ')  
end
 
set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+''''
set @contains='"'+replace(@Name,' ','*" Or "')+'*"'
 
insert into @tmp(Name) exec(@sql)
 
if object_id('tempdb..#') is not null drop table #
 
;with cte_SearchParent as
(
  select a.ID,a.Parent,a.LevelNo,convert(nvarchar(2000),a.Name) as AddressPath from TBAddress a where exists(select 1 from TBAddress x where contains(x.Name,@contains) And x.ID=a.ID) 
  union all
  select a.ID,b.Parent,b.LevelNo,convert(nvarchar(2000),b.Name+'/'+a.AddressPath) as AddressPath
    from cte_SearchParent a
    inner join TBAddress b on b.ID=a.Parent
      --and b.LevelNo=a.LevelNo -1
      and b.LevelNo>=1
)
select a.ID,a.AddressPath 
  into #
  from cte_SearchParent a 
  where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp))
 
;with cte_result as
(
  select a.ID,a.LevelNo,b.AddressPath
    from TBAddress a 
      inner join # b on b.ID=a.ID
  union all
  select b.ID,b.LevelNo,convert(nvarchar(2000),a.AddressPath+'/'+b.Name) As AddressPath
    from cte_result a
      inner join TBAddress b on b.Parent=a.ID
        --and b.LevelNo=a.LevelNo+1
            
)
select distinct a.ID,a.AddressPath 
  from cte_result a 
  where not exists(select 1 from TBAddress x where x.Parent=a.ID)
  order by a.AddressPath 
Go

procedure:up_SearchAddressByNameV1

4.3測試存儲進程：

exec up_SearchAddressByNameV1 '廣 大'

共前往195行記載。

4.4 客戶端統計信息：

均勻的執行耗時:  166毫秒

5.測試，辦法3

在辦法2中，我們在Name列上創立全文索引進步了查詢功能，但我們不只僅局限於一兩個辦法，上面我們引見第3個辦法。

第3個辦法，經過修正表的構造和創立全文索引。在表TBAddress添加多一個字段FullPath存儲各個地址到Level 1的全途徑，再在FullPath列上創立全文索引，然後直接經過全文索引來搜索FullPath列中包括“廣”和“大”的記載。

5.1 新添加字段FullPath，並更新列FullPath數據：

use test;
go
/*alter table */
if not exists ( select 1
            from sys.columns a
            where a.object_id = object_id('TBAddress')
                and a.name = 'Fullpath' )
  begin
     alter table TBAddress add Fullpath nvarchar(200);
  end;
go
create nonclustered index IX_TBAddress_FullPath on dbo.TBAddress(Fullpath) with(fillfactor=80,pad_index=on);
go
/*update TBAddress */
with  cte_fullPath
     as ( select ID, Parent, LevelNo, convert(nvarchar(500), isnull(Name, '')) as FPath, Fullpath
        from dbo.TBAddress
        where LevelNo = 1
        union all
        select A.ID, A.Parent, A.LevelNo, convert(nvarchar(500), B.FPath + '/' + isnull(A.Name, '')) as FPath, A.Fullpath
        from TBAddress as A
            inner join cte_fullPath as B on A.Parent = B.ID
       )
   update a
    set   a.Fullpath = isnull(b.FPath, a.Name)
    from dbo.TBAddress a
        left join cte_fullPath b on b.ID = a.ID;
go

5.2 在列FullPath添加全文索引：

alter fulltext index on dbo.TBAddress add(Fullpath language 2052)

5.3 存儲進程代碼：

Use test
Go
if object_ID('[up_SearchAddressByNameV2]') is not null
  Drop Procedure [up_SearchAddressByNameV2]
Go
create proc up_SearchAddressByNameV2
(
  @name nvarchar(200)
)
As
declare @contains nvarchar(500)
set nocount on
set @contains='"'+replace(@Name,' ','*" And "')+'*"'

select id,FullPath As AddressPath from TBAddress a where contains(a.FullPath,@contains) and not exists(select 1 from TBAddress x where x.Parent=a.ID) order by AddressPath

Go

procedure:up_SearchAddressByNameV2

5.4 測試存儲進程：

exec up_SearchAddressByNameV2 '廣 大'

共前往195行記載。

5.5 客戶端統計信息：

均勻的執行耗時:  20.4毫秒

6.測試，辦法4

 直接運用Like對列FullPath停止查詢。

 6.1存儲進程代碼：

Use test
Go
if object_ID('[up_SearchAddressByNameV3]') is not null
  Drop Procedure [up_SearchAddressByNameV3]
Go
create proc up_SearchAddressByNameV3
(
  @name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max)
 
declare @tmp Table (Name nvarchar(50))
 
set @Name=rtrim(rtrim(@Name))
 
while patindex('% %',@Name)>0
begin
  set @Name=replace(@Name,' ',' ')  
end
 
set @sql='select id,FullPath As AddressPath 
  from TBAddress a where not exists(select 1 from TBAddress x where x.Parent=a.ID)
  ' 
set @sql +='And a.FullPath like ''%' +replace(@Name,' ','%'' And a.FullPath Like ''%')+'%'''
exec (@sql) 
Go

procedure:up_SearchAddressByNameV3

6.2 測試存儲進程：

exec up_SearchAddressByNameV3 '廣 大'

 共前往195行記載。

6.3 客戶端統計信息

均勻的執行耗時:  34毫秒

7.小結

這裡經過一個復雜的表格，對辦法1至辦法4作比擬。

從均勻耗時方面剖析，一眼就知道辦法3比擬契合開端的需求（耗時要控制在幾十毫秒內）。

當然還有其他的辦法，如經過順序完成，把數據一次性加載至內存中，再經過順序寫的算法停止搜索，或經過其他工具如Lucene來完成。不論哪一種辦法，我們都是選擇最優的辦法。實踐的任務經歷通知我們，在實踐使用中，多選擇和測試不同的辦法來，選擇其中一個滿足我們環境的，而且是最優的辦法。