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来实现。不管哪一种方法,我们都是选择最优的方法。实际的工作经验告诉我们,在实际应用中,多选择和测试不同的方法来,选择其中一个满足我们环境的,而且是最优的方法。

(0)

相关推荐

  • SQL Server数据库性能优化技术第1/2页

    设计1个应用系统似乎并不难,但是要想使系统达到最优化的性能并不是一件容易的事.在开发工具.数据库设计.应  用程序的结构.查询设计.接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能.本文以SQL  Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出了一些有益的建议. 1 数据库设计  要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案.在实际工作中,许多SQL  Server方案往往是由于数据库设计得不好导致性能很差.

  • SQL Server数据库的高性能优化经验总结

    本文主要向大家介绍的是正确优化SQL Server数据库的经验总结,其中包括在对其进行优化的实际操作中值得大家注意的地方描述,以及对SQL语句进行优化的最基本原则,以下就是文章的主要内容描述. 优化数据库的注意事项: 1.关键字段建立索引. 2.使用存储过程,它使SQL变得更加灵活和高效. 3.备份数据库和清除垃圾数据. 4.SQL语句语法的优化.(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 5.清理删除日志. SQL语句优化的基本原则: 1.使用索引来更

  • SQL SERVER性能优化综述(很好的总结,不要错过哦)第1/3页

    一.分析阶段 一般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性.可用性.可靠性.安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能是很重要的非功能性需求,必须根据系统的特点确定其实时性需求.响应时间的需求.硬件的配置等.最好能有各种需求的量化的指标. 另一方面,在分析阶段应该根据各种需求区分出系统的类型,大的方面,区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统). 二.设计阶段 设计阶段可以说是以后系统性能的关键阶段,在这个阶段,有一个关系到以后几乎

  • Sql Server查询性能优化之不可小觑的书签查找介绍

    小小程序猿SQL Server认知的成长 1.没毕业或工作没多久,只知道有数据库.SQL这么个东东,浑然分不清SQL和Sql Server Oracle.MySql的关系,通常认为SQL就是SQL Server 2.工作好几年了,也写过不少SQL,却浑然不知道索引为何物,只知道数据库有索引这么个东西,分不清聚集索引和非聚集索引,只知道查询慢了建个索引查询就快了,到头来索引也建了不少,查询也确实快了,偶然问之:汝建之索引为何类型?答曰:... 3.终于受到刺激开始奋发图强,买书,gg查资料终于知道

  • SQLSERVER SQL性能优化技巧

    1.选择最有效率的表名顺序(只在基于规则的优化器中有效) SQLSERVER的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理,在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表,当SQLSERVER处理多个表时,会运用排序及合并的方式连接它们, 首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序:然后扫描第二个表(FROM子句中最后第二个表):最后将所有从第二个表中检索出的记录与第

  • Sql Server 查询性能优化之走出索引的误区分析

    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

  • 开启SQLSERVER数据库缓存依赖优化网站性能

    很多时候,我们服务器的性能瓶颈会是在查询数据库的时候,所以对数据库的缓存非常重要,那么有没有一种方法,可以实现SQL SERVER数据库的缓存,当数据表没有更新时,就从缓存中读取,当有更新的时候,才从数据表中读取呢,答案是肯定的,这样的话我们对一些常用的基础数据表就可以缓存起来,比如做新闻系统的新闻类别等,每次就不需要从数据库中读取了,加快网站的访问速度. 那么如何开启SQLSERVER数据库缓存依赖,方法如下: 第一步:修改Web.Config的<system.web>节的配置,代码如下,让

  • SQLServer地址搜索性能优化

    这是一个很久以前的例子,现在在整理资料时无意发现,就拿出来再改写分享. 1.需求 1.1 基本需求: 根据输入的地址关键字,搜索出完整的地址路径,耗时要控制在几十毫秒内. 1.2 数据库地址表结构和数据: 表TBAddress 表数据 1.3 例子: e.g. 给出一个字符串如"广 大",找出地址全路径中包含有"广" 和"大"的所有地址,結果如下: 下面将通过4个方法来实现,再分析其中的性能优劣,然后选择一个比较优的方法. 2.创建表和插入数据

  • JS性能优化笔记搜索整理

    通过网上查找资料了解关于性能优化方面的内容,现简单整理,仅供大家在优化的过程中参考使用,如有什么问题请及时提出,再做出相应的补充修改. 一. 让代码简洁:一些简略的表达方式也会产生很好的优化 eg:x=x+1;在不影响功能的情况下可以简写为x++; 二. 变量名方法名尽量在不影响语意的情况下简单.(可以选择首字母命名) eg:定义数组的长度可以取名为:ArrLen而不需要取为ArrayLength. 三. 关于JS的循环,循环是一种常用的流程控制. JS提供了三种循环:for(;;).while

  • PHP性能优化准备篇图解PEAR安装

    什么是PEAR PEAR是PHP扩展与应用库(the PHP Extension and Application Repository)的缩写.它是一个PHP扩展及应用的一个代码仓库,简单地说,PEAR就是PHP的CPAN. 为什么要安装PEAR PEAR提供了丰富的PHP扩展工具类,简单易用,可用于各个项目之中.Benchmark是PEAR的一个扩展包,提供Timer.Iterate和Profiler三个工具类,可用于性能优化过程中的断点调试获取代码的执行时间. 如何安装PEAR 打开源码安装

  • 网站前端和后台性能优化的34条宝贵经验和方法

    1 减少HTTP请求数量 (Minimize HTTP Requests) tag:content 80%的用户响应时间被花费在前端,而这其中的绝大多数时间是用于下载页面中的图片.样式表.脚本以及Flash这些组件.减少这些组件的数量就可以减少展示页面所需的请求数,而这是提高网页响应速度的关键. 朴素的页面设计当然是减少组件的一种途径,但有没有能兼顾丰富的页面内容和快速的响应速度的方法呢?下面就是一些不错的技巧,能在提供丰富的页面展现的同时,减少Http请求数量: 合并文件,通过把所有脚本置于一

  • 解析MySQL数据库性能优化的六大技巧

    数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间.Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕.如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况.因此,本文将介绍对MySQL进行性能优化的技巧和窍门. 1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性.如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的.并且不要尝试同时使用这两个存储引擎.思考一下:在一个事务处理中,一些数据表使用Inno

  • 19个MySQL性能优化要点解析

    以下就是跟大家分享的19个MySQL性能优化主要要点,一起学习学习. 1.为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的.当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了. 这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的.因为,我们某些查询语句会让MySQL不使用缓存.请看下面的示例: // 查询缓存不开启 $r = mysq

  • 提高jQuery性能优化的技巧

    下面把提高jQuery性能优化技巧给大家分享如下: 缓存变量 DOM遍历是昂贵的,所以尽量将会重用的元素缓存. 复制代码 代码如下: // 糟糕 h = $('#element').height(); $('#element').css('height',h-20); // 建议 $element = $('#element'); h = $element.height(); $element.css('height',h-20); 避免全局变量 jQuery与javascript一样,一般来说

  • Django之使用celery和NGINX生成静态页面实现性能优化

    性能优化原理: 当我们要给client浏览器返回一个页面时,我们需要去数据库查询数据并将数据和基本页面模板渲染形成页面返回给客户端,但如果每一个用户访问时都去查询一次首页的的数据时,当日访问量很大时那么无疑会给数据库查询带来很大的性能问题.为了解决这个问题,我们可以给未登录用户返回一个早就渲染好的静态首页(给已登录的用户返回一个调用缓存数据和个人数据渲染的页面),这样就可以提高网站的性能了. 使用celery生成静态首页 生成静态页面原理: 在一个为静态首页准备的基础模板之上,获取数据,使用dj

  • 浅谈Android性能优化之内存优化

    1.Android内存管理机制 1.1 Java内存分配模型 先上一张JVM将内存划分区域的图 程序计数器:存储当前线程执行目标方法执行到第几行. 栈内存:Java栈中存放的是一个个栈帧,每个栈帧对应一个被调用的方法.栈帧包括局部标量表, 操作数栈. 本地方法栈:本地方法栈主要是为执行本地方法服务的.而Java栈是为执行Java方法服务的. 方法区:该区域被线程共享.主要存储每个类的信息(类名,方法信息,字段信息等).静态变量,常量,以及编译器编译后的代码等. 堆:Java中的堆是被线程共享的,

  • Vue.js九个性能优化技巧(值得收藏)

    目录 Functional components Child component splitting Local variables Reuse DOM with v-show KeepAlive Deferred features Time slicing Non-reactive data Virtual scrolling 总结 参考资料 这篇文章主要参考了 Vue.js 核心成员Guillaume Chau在 19 年美国的 Vue conf 分享的主题:9 Performance se

随机推荐