SqlServerStringToTable性能测试

问题起因： 最近做的项目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个字符串，解析出来之后插入到临时表，然后在解析后面的，在插入到临时表，循环处理。最后临时表和实际表进行关联。

select id

from dbo.stringtotable(@字符串1‍)

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长度

执行时间

查看更多关于SqlServerStringToTable性能测试的详细内容...