SQLServer地址搜索性能优化(2)-木庄网络博客

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 测试存储过程：