问题起因: 最近做的项目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_Temp10Select 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_Temp10Select 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
本文原创发布php中文网,转载请注明出处,感谢您的尊重!