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.
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.
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
00:01:01
@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 |
|
| valign= "Top" width= "197" >
SQL Server stringtotable performance test