SQL Server address search performance optimization and SQL server performance optimization

SQL Server address search performance optimization and SQL server performance optimization

This is an example of a long time ago. Now, I have no intention of discovering the materials, so I will take it out and try again.

1. Requirements

1.1 Basic Requirements: Search for the complete address path based on the entered address keyword, which takes dozens of milliseconds.

1.2 database address table structure and data:

Table TBAddress

 

Table Data

 

1.3 example:

E.g. Give a string such as "wide and big" and find all addresses in the full path of the address that contain "wide" and "Big". The result is as follows:

In the following four methods, we will analyze the performance advantages and disadvantages, and then select a better method.

2. Create a table and insert data

2.1 create a data table 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 Insert data

Use testgo/* insert data */set nocount onBegin Try Tran in Tran Insert Into TBAddress ([ID], [Parent], [LevelNo], [Name]) Select 1, 0, 0, N 'China' Union All Select, 1, N 'municipalities 'Union All Select, 1, N 'liaoning province' Union All Select, 1, 1, N 'guangdong province 'Union All ...... select 44740,930, 4, N 'oritake town 'Union All Select 44741,932, 4, N 'bayonkuruti township' Union All Select 44742,932, 4, N 'gigen township 'Union All Select 44743,932, 4, N 'toyun xiang' Commit TranEnd TryBegin Catch throw 50001, n' occurs during data insertion. ', 1 Rollback TranEnd Catchgo

Attachment: insert Data

Note:There are 44700 data records, and the insert code is relatively long, so the attachment form is used.

3. Test, method 1

3.1 analysis:

 

A. First, search for all address records containing "wide" and "Big" in the package field Name and save them to the temporary table # tmp.

B. Then find the full path from # tmp address to Level 1.

C. Based on the result obtained in step 2, filter out the address paths that contain "wide" and "large.

D. Based on the filtering result in step 3, query all the addresses in Level n (n is the layer number without sub-addresses.

3.2 Stored Procedure Code:

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 perform a query:

Exec up_SearchAddressByNameV0 'large exten'

A total of 195 rows of records are returned.

3.4 client statistics:

Average execution time: 244 milliseconds

4. Test, method 2

Method 2 is Step 1 in method 1 with reference to method 1 and full-text index optimization. That is, create a full-text index on the name column. in step 1, the full-text index is used to search for all address records containing "wide" and "Big" in the package field Name and save them to the temporary table # tmp. The other steps remain unchanged.

4.1 create a full-text index

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:The language used to create a full-text index on the Name column is Simplified Chinese (Simplified Chinese)

4.2 Stored Procedure Code:

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 Test the stored procedure:

Exec up_SearchAddressByNameV1 'large and wide'

A total of 195 rows of records are returned.

4.4 client statistics:

Average execution time: 166 milliseconds

5. Test, method 3

In method 2, we create a full-text index on the Name column to improve the query performance, but we are not limited to two methods. Next we will introduce 3rd methods.

You can modify the table structure and create a full-text index by using the 3rd methods. Add one more field FullPath to the table TBAddress to store the full path from each address to Level 1, and then create a full-text index on the FullPath column, then, the full-text index is used to search for records with "wide" and "large" contained in the FullPath column.

5.1 Add the field FullPath and update the column FullPath data:

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 add full-text indexes in the FullPath column:

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

5.3 Stored Procedure Code:

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 test the stored procedure:

Exec up_SearchAddressByNameV2 'broad data'

A total of 195 rows of records are returned.

5.5 client statistics:

Average execution time: 20.4 milliseconds

6. Test, Method 4

Directly use Like to query the FullPath column.

6.1 Stored Procedure Code:

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 test the stored procedure:

Exec up_SearchAddressByNameV3 'broad data'

A total of 195 rows of records are returned.

6.3 client statistics

 

Average execution time: 34 Ms

7. Summary

Here we use a simple table to compare methods 1 to 4.

 

From the average time consumption analysis, we can see at a glance that method 3 meets the initial requirement (the time consumption should be controlled within dozens of milliseconds ).

Of course, there are other methods, such as program implementation, loading data to the memory at a time, then searching through the algorithm written by the program, or using other tools such as Lucene. No matter which method, we choose the best method. The actual work experience tells us that in practical applications, we choose different methods and test them to select one of them that meets our environment and is the best method.