波斯马BOSSMA Information Technology

SQL Server 2014内存优化表之存储过程中判断包含字符串算法

发布时间:2015年1月12日 / 分类:DataBase / 10,744 次浏览 / 评论

在SQL Server 2014中提供了内存优化表,以提供更快的事务处理,支持更多的处理并发。由于使用了新的存储架构,内存优化表相比传统磁盘表性能提高了不少,但也存在很多的限制。比如不能使用发布订阅,不支持varchar数据类型,不能进行复杂的条件查询,还不支持很多的内置函数,比如判断某个字符或某几个字符是否包含在字段中,在传统磁盘表中可以使用CHARINDEX或者PATINDEX,但是内存优化表不支持。解决这个问题我目前只想到两个方法:第一种查询出数据后在程序中进行判断,第二种在存储过程中利用支持的函数进行判断。这篇文章将介绍第二种方法。

为什么要使用存储过程呢?

内存优化表中使用的存储过程不是普通的存储过程,称之为本机编译的存储过程,详情可看官方文档

按照官方的说法,本机存储过程是在创建时编译的,直接编译为处理器可执行的代码,适合执行性能要求比较高的处理,这也是使用内存优化表的原因之一。

官方文档还提到如果只是执行一条查询语句,则使用本机编译存储过程和使用Transact-SQL相比并无性能优势,本机编译存储过程特别适合以下操作:

聚合。
嵌套循环联接。
多语句选择、插入、更新和删除操作。
复杂表达式。
程序逻辑,如条件语句和循环。

本机编译存储过程中判断包含字符串

先来看一个应用场景:有一张内存优化表保存了用户的爱好,所有的爱好保存在一个字段中,用逗号隔开,现在需要做一个查询更新操作,如果用户具有指定的爱好则返回,如果不具有则添加指定的爱好到用户全部爱好中。

建表语句:

CREATE TABLE [dbo].[UserFavorite]
(
	[UserID] [nvarchar](40) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
	[Favorites] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,

	 PRIMARY KEY NONCLUSTERED HASH 
	(
		[UserID]
	)WITH ( BUCKET_COUNT = 100000)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

添加一些测试数据:

insert into UserFavorite(UserID,Favorites) values('U1',',A,B,C,D,')
insert into UserFavorite(UserID,Favorites) values('U2',',A,B,')
insert into UserFavorite(UserID,Favorites) values('U3',',A,C,')
insert into UserFavorite(UserID,Favorites) values('U4',',C,D,')
insert into UserFavorite(UserID,Favorites) values('U5',',B,D,')

这里用户的每种爱好用逗号隔开,为了方便处理,前后都增加了逗号。

在程序中进行处理时首先根据UserID从数据库中查询出用户信息,然后判断用户是否具备某个爱好,如果不具备,则添加后更新到数据库,这其中设计到两次数据库操作。我们可以将这个逻辑转移到存储过程中,程序与数据库只需交互一次,按照官方的说法应该可以提升性能。

在本机编译存储过程中查询、更新都很好实现,重点是判断用户是否已经具备某个爱好,由于支持的字符操作函数有限,无法直接判断;还曾想通过自定义函数的方式实现,很不幸也不支持;最后只能利用仅有的几个字符串操作函数来实现:

LEN、LTRIM、RTRIM 和 SUBSTRING。

下边直接把存储过程贴出来了,截取字符串部分是此存储过程的核心:

CREATE PROCEDURE [dbo].[Proc_UserFavorite_SelectOrUpdate]
@UserID nvarchar(20),             -- 参数1:用户ID
@Favorite nvarchar(10),           -- 参数2:某个爱好
@Favorites nvarchar(200) output   -- 参数3:用户的全部爱好

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS

BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
		
		 -- 多个爱好分割字符
		DECLARE @SplitChar nchar(1) = N',' COLLATE Latin1_General_100_BIN2

	    -- 根据用户ID查询用户爱好信息
		select @Favorites=Favorites from dbo.UserFavorite where UserID=@UserID

		-- 没有用户的爱好记录
		if @@ROWCOUNT <=0
			begin
				-- 设置要存入的爱好
				set @Favorites =  @SplitChar + @Favorite + @SplitChar

				-- 添加用户的爱好记录
				insert into dbo.UserFavorite values(@UserID,@Favorites)
			end
		else
			begin

				-- 比较@Favorite是否包含在用户爱好中
				DECLARE @IsExsitFavorite int=0    -- 为1表示@Favorite已经存在
				DECLARE @TempCutStr nvarchar(40)  -- 临时截取到的字符串,要和@Favorite进行比较
				DECLARE @TempCutStrStart int=0    -- 截取开始的位置
				DECLARE @TempCutStrEnd int=0      -- 截取结束的位置
				DECLARE @CurrentChar nvarchar(1)  -- 截取过程中当前位置的字符
				declare @FavoritesLength int = len(@Favorites) -- 已经存在的@Favorites的长度
				DECLARE @i int = 1

				-- 遍历所有字符,用于截取字符和比较
				while @i <= @FavoritesLength
					begin
						-- 重新设置tripid开始截取位置
						set @CurrentChar = substring(@Favorites,@i,1)

						-- 如果当前字符为分个字符,则设置截取开始或结束位置
						if  @CurrentChar COLLATE Latin1_General_100_BIN2 = @SplitChar
							begin
								if @TempCutStrStart <= 0
									set @TempCutStrStart = @i
								else -- 位置大于0时,再遇到分隔符则为结束位置
									set @TempCutStrEnd = @i 
							end

						-- @TempCutStrEnd大于0表示发现了第2个分隔符,这时可以截取字符串进行比较了
						if @TempCutStrEnd > 0
							begin
								-- 截取字符串,如果能匹配传入的@Favorite,则说明已经包含
								set @TempCutStr = substring(@Favorites,@TempCutStrStart, @TempCutStrEnd - @TempCutStrStart + 1)
								if @TempCutStr COLLATE Latin1_General_100_BIN2 = @SplitChar + @Favorite + @SplitChar COLLATE Latin1_General_100_BIN2
									begin
										set @IsExsitFavorite = 1
										return -- 退出循环
									end

								-- 重新设置开始位置和结束为止
								set @TempCutStrStart = @TempCutStrEnd
								set @TempCutStrEnd = 0
							end
						
						set @i=@i+1
					end

				if @IsExsitFavorite <= 0
					begin
						set @Favorites = @Favorites + @Favorite + @SplitChar
						update dbo.UserFavorite set [Favorites]=@Favorites  where [UserID]=@UserID
					end
			end
END


GO

测试脚本:

DECLARE	@return_value int,
		@Favorites nvarchar(200)

EXEC	@return_value = [dbo].[Proc_UserFavorite_SelectOrUpdate]
		@UserID = N'U1',
		@Favorite = N'A',
		@Favorites = @Favorites OUTPUT

SELECT	@Favorites as N'@Favorites'

SELECT	'Return Value' = @return_value

GO

 

截取算法原理:

1、确定分割字符,字符串首位和末尾都是分割字符;

2、遍历字符,根据当前字符是否为分割字符确定截取开始位置和结束位置;

3、将截取的字符串与传入的字符串比较是否相等;

4、如果存在相等则包含,不存在相等则不包含。

 

后记

这里的场景是字符串具有分隔符的情况,对于只是简单的判断字符串包含这里没有实现,对这个程序稍加改造即可,区别是截取开始位置和结束位置的确定。

那么这种实现的性能如何呢?

基于上边的存储过程,执行1W次在1秒内,10W次在2秒钟。

有兴趣的可以在自己的环境下测试下,看看能不能满足需求,或者对此存储过程进行一些优化。

 

本博客所有文章如无特别注明均为原创。
复制或转载请以超链接形式注明转自波斯马,原文地址《SQL Server 2014内存优化表之存储过程中判断包含字符串算法

关键字:

建议订阅本站,及时阅读最新文章!
【上一篇】 【下一篇】

发表评论