Performance comparison of three string merge methods in SQL Server

A stored procedure that merges word Fulu is recently being processed, and in the development of a test system, a string merge function is used to do it directly in SQL.
Example:
Table contents:
Name Content
1 ABC
1 AAA
1 dddd
2 1223
2 FKDJFD
--------------------------------
Results
1 abc,aaa,dddd
2 1223,FKDJFD
Requires an SQL statement, such as:select sum (content) from the table group by name

--the problem, a total of three methods were used, and the respective performance of the three methods were tested separately
1: Create a handler function
2:sql new workaround in version 2005 and above, for XML
3: Example of string merge processing using temporal tables

Description: The following test is based on the hardware configuration of my machine, depending on the hardware configuration, the results may be different.


1: Create a handler function
Description: SQL Full series version

CREATE FUNCTION dbo.f_strhebin (@id int)
RETURNS varchar (8000)
As
BEGIN
DECLARE @r varchar (8000)
SET @r = "
SELECT @r = @r + ', ' + value
From TB
WHERE [email protected]
RETURN STUFF (@r, 1, 1, ")
END
GO
--Call function

SELECt ID, values=dbo.f_strhebin (ID)
From TB
GROUP by ID

These are all records that are merged by ID, and if you want to get a record of the corresponding single ID, you also need to add a statement:
Assumption: The above results are entered into the temp table 3t3:
SELECT Id,max (values) as values from #t3 the GROUP by ID

Go

The analysis results are as follows:
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 7 milliseconds.
The use of SQL full-line version, custom merge function mode

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.
Table ' worktable '. Scan count 1, logical read 4,030 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' TB '. Scan count 2, logical read 46 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 1397254 milliseconds, elapsed time = 1463680 milliseconds.

(1969 rows affected)

(218 rows affected)
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' #t3___000067 '. Scan count 1, logical read 16 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 84 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 2 milliseconds.

Total elapsed time: 24.4 minutes, this is only 1969 records, this method appears to be not available in this case.


2:sql New workaround in version 2005 and above, for XML

Select Id,stuff (
(SELECT '-' + CONVERT (varchar (4), value)
From TB
where id=a.id
ORDER BY ID
FOR XML Path (")
) as values
From TB A
GROUP BY ID

Go

The analysis results are as follows:

SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.
Table ' worktable '. Scan count 439, logical read 32,978 times, physical read 0 times, read 0 times, LOB logic read 319 times, lob physical read 0 times, lob read 0 times.
Table ' TB '. Scan Count 4, logical read 92 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 1856 milliseconds, elapsed time = 1955 milliseconds.

Total time: 2 seconds. However, with the increase in the number of records, the performance is also reduced, when the data record reached 20,000, time consuming nearly 2.5 minutes.

3: Example of string merge processing using temporal tables

SELECT ID, values=cast (value as varchar (8000))
Into #t2 from TB
ORDER by ID

DECLARE @col1 varchar (5), @col2 varchar (8000)
UPDATE #t2 SET
@col2 =case when @col1 =id then @col2 + '-' +values ELSE values END,
@col1 =zo3,
[Email protected]


SELECT Id,max values from #t2 the group by ID

drop table #t2


Go

The analysis results are as follows:


SQL Server parse and compile time:
CPU time = 7 milliseconds, elapsed time = 7 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' TB '. Scan count 2, logical read 46 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 734 milliseconds, elapsed time = 769 milliseconds.

(2012 rows affected)
Table ' #t2___________000000000065 '. Scan count 1, logical read 1677 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 62 milliseconds, elapsed time = 62 milliseconds.

(2012 rows affected)
Table ' #t2__________000000000065 '. Scan count 1, logical read 849 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 16 milliseconds, elapsed time = 7 milliseconds.

(218 rows affected)

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.


Total time: 769ms+62ms+15ms=846ms, time spent 1 seconds less, when the data record reached 20,000 records, the performance loss is not too serious, in about 6-10 seconds. can accept.

From Dotnet Garden

Http://www.cnblogs.com/chillsrc/archive/2011/03/02/1969010.html

Performance comparison of three string merge methods in SQL Server