SqlServer StringToTable性能测试

• 问题起因：

最近做的项目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);
    
    If @EndIndex=0 Begin
        Return;
    End

    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

 

• 测试结果：(以下测试结果均是Set Statitistics Time,IO on测试，如果不开始IO,Timer Statistics会比这个结果块很多)

 

@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（不开启IO,Timer Statistics，耗时12s）