波斯马BOSSMA Information Technology

在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秒钟。

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

关键字: charindex 内存优化表 本机编译存储过程