波斯码BOSSMA Information Technology

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

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

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

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

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

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

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

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

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

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

建表语句:

添加一些测试数据:

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

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

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

LEN、LTRIM、RTRIM 和 SUBSTRING。

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

测试脚本:

 

截取算法原理:

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

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

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

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

 

后记

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

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

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

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

 

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

关键字:

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

发表评论