SqlServerStringToTable性能测试
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SqlServerStringToTable性能测试,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3789字,纯文字阅读大概需要6分钟。
内容图文
![SqlServerStringToTable性能测试](/upload/InfoBanner/zyjiaocheng/530/0a889a53867343e895d96a4d411d32e1.jpg)
问题起因: 最近做的项目DB数据量比较大(基本上一个月的数据就是10亿),而工程中Proc参数中包含有id拼接字符串,id拼接字符串格式:1,2,4,5,100,301。当数据量很小的情况下,这样做没有问题,但一旦数据量到达亿级,运行会很耗时,比如:当这样的参数id拼接
- 问题起因:
最近做的项目DB数据量比较大(基本上一个月的数据就是10亿),而工程中Proc参数中包含有id拼接字符串,id拼接字符串格式:1,2,4,5,100,301。当数据量很小的情况下,这样做没有问题,但一旦数据量到达亿级,运行会很耗时,比如:当这样的参数id拼接字符串中包含有10万个id的时候(我们实际应用中确实有这么多个id需要传到数据库,而且这样的id是从库中取出后,又经过程序的筛选后剩余的id),像这样的语句:
Declare @IDS nvarchar(max); Set @IDS='10w个id用逗号分割组成的字符串'; Select T10.TEXT,T10.Name FROM DX.M as T10 inner join dbo.StringToTable(@IDS,',') as T11 on T10.ID=T11.ID;
执行了18个小时还未查询出数据。
备注:
虚拟机配置:内存:64G;CPU核数:40。
- DBA建议:
我测试了下,性能还算可以。在解析5000个逗号之内性能还行,太多了,性能就急速下降了。
最初的那个版本其实还是很常用的,性能要比改写之后的要好一些(在字符串特别长的情况下)。但是同样存在,如果字符串太长,性能急速下降的问题。
如果真的有5W以上逗号的字符串。这个SqlServer在执行计划上会消耗很多性能。
(自己也可以测试一下解析5000个逗号串和解析5W个字符串的差距,并不是5000字符串消耗时间*10的线性关系)
所以应当写一个循环,一次处理一部分。
比如以下两种方法:
1. 每次截取前1W个字符串,解析出来之后插入到临时表,然后在解析后面的,在插入到临时表,循环处理。最后临时表和实际表进行关联。
insert into #t1
select id
from dbo.stringtotable(@字符串1?)
insert into #t1
select id
from dbo.stringtotable(@字符串2)?
2。用in的方式,每次where条件 in 一部分。然后将结果union all起来。
类似如下
select id
from table a
where id in (@字符串1)
union all
select id
from table a
where id in (@字符串2)?
两种方法都可行。在字符串较短的情况下,第二种方法应该好一些。字符串较长,第一种应该好一些。
- 测试代码:
Declare @MRE_MROOIDS Nvarchar(Max); Set @MRE_MROOIDS='2,4,5,396009,'; --Set @MRE_MROOIDS='2,4,5,6,7,8,9,10,11,14,15,16,17,18,20,21,23,24,25,26,29,30'; Declare @SplitChar nvarchar(2); Declare @EndIndex int; Declare @Step int; Declare @LastChars nvarchar(MAX); Declare @CurrentTempChars nvarchar(max); Set @LastChars=@MRE_MROOIDS; Set @Step=5000; Set @EndIndex=0; Set @SplitChar=','; IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects where id=OBJECT_ID(N'tempdb..#StringToTableEntry_Temp10')) Begin Drop Table #StringToTableEntry_Temp10; End Create Table #StringToTableEntry_Temp10(ID INT); While(LEN(@LastChars)>@Step) Begin Set @EndIndex= charindex(@SplitChar,@LastChars,@Step); Set @CurrentTempChars=SubString(@LastChars,0,@EndIndex); -- insert into temp table Insert Into #StringToTableEntry_Temp10 Select Id from dbo.StringToTable2(@CurrentTempChars,','); Set @LastChars=SubString(@LastChars,@EndIndex+1,LEN(@LastChars)-@EndIndex+1) --Select @LastChars as LastChars; Set @EndIndex=@EndIndex+@Step; End If LEN(@LastChars)>0 Begin Insert Into #StringToTableEntry_Temp10 Select Id from dbo.StringToTable2(@LastChars,','); End Select COUNT(0) From #StringToTableEntry_Temp10
StringToTable2函数:
ALTER FUNCTION [dbo].[StringToTable] ( @ids [nvarchar](max), @separator [char](1) ) RETURNS @IdsTable TABLE ( [Id] INT NOT NULL ) AS BEGIN IF(RIGHT(@ids,1)=@separator) BEGIN SET @ids=SUBSTRING(@ids,0,LEN(@ids)); END --下面的方式性能更好 IF(LEN(@ids) > 0) BEGIN DECLARE @i int; SET @i = CHARINDEX(@separator, @ids); WHILE @i > 0 BEGIN INSERT @IdsTable VALUES(LEFT(@ids, @i - 1)); SET @ids = SUBSTRING(@ids, @i + 1, LEN(@ids) - @i); SET @i = CHARINDEX(@separator, @ids); END IF(LEN(@ids) > 0) BEGIN INSERT @IdsTable VALUES(@ids); END END RETURN; END
- 测试结果:
@MRE_MROOIDS包含id记录 |
@Step长度 |
执行时间 |
100,000 |
100000 |
00:09:15 |
100,000 |
20000 |
00:03:48 |
100,000 |
10000 |
00:01:57 |
100,000 |
5000 |
00:01:01 |
内容总结
以上是互联网集市为您收集整理的SqlServerStringToTable性能测试全部内容,希望文章能够帮你解决SqlServerStringToTable性能测试所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。