SQL Server stringtotable Performance test

• Cause of the problem:

Most recently done project DB data volume is relatively large (basically one months of data is 1 billion), while the project proc parameter contains ID stitching string, id stitching string format: 1,2,4,5,100,301. When the amount of data is very small, there is no problem, but once the amount of data reached billion, the operation will be time-consuming, such as: when such a parameter ID stitching string contains 100,000 IDs (we actually have so many IDs to be uploaded to the database, and such an ID is removed from the library, And then after the program's filtering the remaining IDs), statements like this:

Declare @IDS nvarchar(Max);Set @IDS='10w ID string with comma-separated characters';SelectT10.TEXT, T10. Name fromDx. M asT10Inner JoinDbo. Stringtotable (@IDS,',') asT11 onT10.id=T11.id;

The data has not been queried for 18 hours.

Note:

Virtual machine configuration: Memory: 64g;cpu number of cores: 40.

• DBA recommends:

I tested, performance is still possible. In parsing 5,000 comma within the performance of the line, too much, the performance of the rapid decline.

The original version is actually quite common, and performance is better than after the rewrite (in the case of a very long string). But there is also the problem that if the string is too long, the performance drops sharply.

If there is really a string of more than 5W commas. This SQL Server consumes a lot of performance on the execution plan.

(You can also test to parse 5,000 comma string and parse 5W string gap, not 5000 string consumption time *10 linear relationship)

So you should write a loop that is part of the process.

such as the following two ways:

1. Each interception of the first 1W string, parsed out after inserting into the temporary table, and then after parsing, inserted into the temporary table, loop processing. The last temporary table is associated with the actual table.

INSERT INTO #t1

Select ID

From dbo.stringtotable (@ string 1)

INSERT INTO #t1

Select ID

From Dbo.stringtotable (@ string 2)

2. In the way, each time the where condition in part. The result is then union all up.

Similar to the following

Select ID

From Table A

where ID in (@ string 1)

UNION ALL

Select ID

From Table A

where ID in (@ string 2)

Both methods are feasible. In the case of short strings, the second method should be better. Strings are longer, the first one should be better.

• Test code:

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= the;Set @EndIndex=0;Set @SplitChar=',';IF EXISTS(SELECT *  fromTempdb.dbo.sysobjectswhereId=object_id(N'tempdb: #StringToTableEntry_Temp10'))    Begin        Drop Table#StringToTableEntry_Temp10; End        Create Table#StringToTableEntry_Temp10 (IDINT); while(LEN(@LastChars)>@Step)Begin        Set @EndIndex= charindex(@SplitChar,@LastChars,@Step); Set @CurrentTempChars=SubString(@LastChars,0,@EndIndex); --INSERT into temp table    Insert  into#StringToTableEntry_Temp10SelectId fromDbo. StringToTable2 (@CurrentTempChars,','); Set @LastChars=SubString(@LastChars,@EndIndex+1,LEN(@LastChars)-@EndIndex+1)     --Select @LastChars as Lastchars;     Set @EndIndex=@EndIndex+@Step; EndIf LEN(@LastChars)>0 Begin    Insert  into#StringToTableEntry_Temp10SelectId fromDbo. StringToTable2 (@LastChars,',');EndSelect COUNT(0) from#StringToTableEntry_Temp10

StringToTable2 function:

ALTER FUNCTION [dbo].[stringtotable](    @ids [nvarchar](Max),    @separator [Char](1))RETURNS @IdsTable TABLE(    [Id] INT  not NULL) asBEGIN    IF( Right(@ids,1)=@separator)        BEGIN            SET @ids=SUBSTRING(@ids,0,LEN(@ids)); END    --better performance in the following ways    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

• Test results:

00:01:01

valign= "Top" width= "197" >

@MRE_MROOIDS contains ID records	@Step length	execution time
100,000	100000	00:09:15
100,000	20000	00:03:48
100,000	10000	00:01:57
100,000

SQL Server stringtotable performance test