- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章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;gocreate table TBAddress( ID int , Parent int not null , LevelNo smallint not null , Name nvarchar(50) not null , constraint PK_TBAddress primary key ( ID ));gocreate 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 testgo/*insert data*/set nocount onBegin 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 TranEnd TryBegin Catch throw 50001,N'插入數據過程中發生錯誤.' ,1Rollback TranEnd Catchgo
附件: 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 testGoif object_ID('[up_SearchAddressByNameV0]') is not null Drop Procedure [up_SearchAddressByNameV0]Gocreate proc up_SearchAddressByNameV0 ( @Name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=@Name+' ' while patindex('% %',@Name)>0begin 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 #tmpif 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 testgo/*create fulltext index*/if not exists(select 1 from sys.fulltext_catalogs a where a.name='ftCatalog')begincreate fulltext catalog ftCatalog As default;endgo--select * From sys.fulltext_languages create fulltext index on TBAddress(Name language 2052 ) key index PK_TBAddressgo alter fulltext index on dbo.TBAddress add(Fullpath language 2052)go
Note: 在Name列上创建全文索引使用的语言是简体中文(Simplified Chinese) 。
4.2 存储过程代码:
Use testGoif object_ID('[up_SearchAddressByNameV1]') is not null Drop Procedure [up_SearchAddressByNameV1]Gocreate proc up_SearchAddressByNameV1 ( @Name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max),@contains nvarchar(500) declare @tmp Table (Name nvarchar(50)) while patindex('% %',@Name)>0begin 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;gocreate 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 testGoif object_ID('[up_SearchAddressByNameV2]') is not null Drop Procedure [up_SearchAddressByNameV2]Gocreate proc up_SearchAddressByNameV2( @name nvarchar(200))Asdeclare @contains nvarchar(500)set nocount onset @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 AddressPathGo
procedure:up_SearchAddressByNameV2 。
5.4 测试存储过程:
exec up_SearchAddressByNameV2 '广 大'
共返回195行记录.
5.5 客户端统计信息:
平均的执行耗时: 20.4毫秒 。
6.测试,方法4 。
直接使用Like对列FullPath进行查询.
6.1存储过程代码:
Use testGoif object_ID('[up_SearchAddressByNameV3]') is not null Drop Procedure [up_SearchAddressByNameV3]Gocreate proc up_SearchAddressByNameV3( @name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=rtrim(rtrim(@Name)) while patindex('% %',@Name)>0begin 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来实现。不管哪一种方法,我们都是选择最优的方法。实际的工作经验告诉我们,在实际应用中,多选择和测试不同的方法来,选择其中一个满足我们环境的,而且是最优的方法.
最后此篇关于SQLServer地址搜索性能优化的文章就讲到这里了,如果你想了解更多关于SQLServer地址搜索性能优化的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
目前,我有以下设置: A记录: mydomain.com - aaa.aaa.aaa.aaa subdomain.mydomain.com - aaa.aaa.aaa.aaa NS记录: mydoma
有人可以帮助我以最佳方式在流畅的 nHibernate 中映射以下情况吗? Address 类用于 Client 和 Company。如何在 SQL 中最有效地存储它?映射应该是什么样的?我已经考虑过
我正在尝试编写一个 Windows 应用程序,它将在来自 PC 的以太网链接上生成流量。 我想使用 webBrowser 控件不断拉取网页以产生流量。 在这种情况下,我希望每个 webBrowser
我正在编写一个 SIP 堆栈,我需要在消息中插入一个 IP 地址。该地址必须是用于发送消息的地址。我知道目标 IP 并且需要确定将用于发送消息的 NIC(其地址).... 最佳答案 为了扩展 Remy
如何使用 IP 地址获取 MAC 地址,但以下代码不起作用 packet = ARP(op=ARP.who_has,psrc="some ip",pdst = ip) response = srp(p
目前我想知道如何实现对本地无线网络(路由器)的获取请求以获取当前连接到当前连接的 LAN 的所有设备.... 所以我做了一些研究,显然“nmap”是一个终端/命令提示符命令,它将连接的设备返回到本地无
这个问题在这里已经有了答案: 关闭 11 年前。 Possible Duplicates: how to find MAC address in MAC OS X programmatically
我们正在为 ipad 开发一个 iOS 应用程序,它使用 bonjour 与其他设备连接,使用 couchbaseListener 与对等数据库进行复制。我们观察到,每当 [nsnetservice
我创建了 3 个实例,其中 3 个弹性 IP 地址指向这些实例。 我做了 dsc 的 yum 安装:dsc12.noarch 1.2.13-1 @datastax 并且/etc/cassandra/d
我正在尝试获取规模集中所有虚拟机的私有(private) IP 地址列表(没有一个虚拟机故意拥有任何公共(public) IP 地址)。我找到了如何从 az cli 获取此内容,如下所示: az vm
我正在尝试获取规模集中所有虚拟机的私有(private) IP 地址列表(没有一个虚拟机故意拥有任何公共(public) IP 地址)。我找到了如何从 az cli 获取此内容,如下所示: az vm
我正在尝试与该端口上的任何 IP 建立连接。最初,我将其设置为 10.0.0.7,这是我网络上另一台计算机的 IP,因此我可以测试客户端/服务器。但是,我希望它可以与任何计算机一起使用而不必将 IP
作为序言,我开发了自己的 CRM(类似于 SalesForce 或 SAP),其“规模”要小得多,因为它面向服务,而不是销售。我在 Ubuntu 16.04 服务器上使用 MySql 或 MariaD
在我的项目中,我想做如下事情: static void test0(void) { printf("%s [%d]\n", __func__, __LINE__); } static void
我的机器上有两个网卡,配置了两个独立的 IP 地址。两个 IP 地址都属于同一个网络。我是否正确地说,当我创建一个特定于这些 IP 地址之一的套接字时? 更新: 这是我的情况: 我有一个位于 192.
当然,我意识到没有一种“正确的方法”来设计 SQL 数据库,但我想就我的特定场景中的优劣获得一些意见。 目前,我正在设计一个订单输入模块(带有 SQL Server 2008 的 Windows .N
我们将保存大量地址数据(在我公司的眼中,每个客户大约有150.000至500.000行)。 地址数据包含约5列: 名称1 名称2 街(+否) 邮政编码 市 也许以后再添加一些东西(例如电话,邮件等)
好的,我们在生产中实现了 Recaptcha。我们收到错误是因为它无法到达使用该服务所需的 IP 地址。我们为 IP 地址打开一个端口以到达 Google。没问题。我们这样做并显式配置该 IP 地址以
此页面使用 Drupals 联系表发送电子邮件:http://www.westlake.school.nz/contact 问题是,学校员工使用 outlook。当他们收到来自 parent 等的电子
是否可以将用户输入的邮政编码转换为文本框并将其转换为CLLocation?我正在尝试比较其当前位置与地址或邮政编码之间的距离,如果可以从NSString中创建CLLocation,这将很容易。 最佳答
我是一名优秀的程序员,十分优秀!