表值参数与分割字符串表值函数效率对比

一、预备脚本：

--1. 创建表值参数类型
IF NOT EXISTS (SELECT * FROM sys.types AS t WHERE t.name='type_BigInt')  
BEGIN
	CREATE TYPE dbo.type_BigInt AS TABLE(   
		id BIGINT 
	)
END
GO
--2. 创建分割字符串表值函数
IF OBJECT_ID('[dbo].[Fun_StringToBigIntArray]') IS NOT NULL
	DROP FUNCTION [dbo].[Fun_StringToBigIntArray]
GO
CREATE FUNCTION [dbo].[Fun_StringToBigIntArray](@str NTEXT)
RETURNS @table TABLE ([item] BIGINT PRIMARY KEY)
/*
功能:转换字符串为BIGINT类型的表
select * from [dbo].[Fun_StringToBigIntArray]('1,2,54,5,789,7')
*/
AS
BEGIN
	DECLARE @xml XML;
	SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), ',', ']]></x><x><![CDATA[') + ']]></x>')

	INSERT INTO @table
		SELECT item
		FROM   (SELECT c.value('text()[1]', 'bigint') [item]
				FROM   @xml.nodes('/x') t(c)) t
		WHERE  item IS NOT NULL
		GROUP BY item

	RETURN
END
GO
--3. 创建测试存储过程1
IF EXISTS(SELECT * FROM sys.procedures AS p WHERE p.[object_id]=OBJECT_ID(N'dbo.Proc_TestTableParameter'))
BEGIN
	DROP PROC dbo.Proc_TestTableParameter
END
GO
CREATE PROC dbo.Proc_TestTableParameter
(
	@tvp dbo.type_BigInt READONLY	
)
AS
BEGIN
	SET NOCOUNT ON
	SELECT id FROM @tvp
END
GO
--4. 创建测试存储过程2
IF OBJECT_ID('Proc_TestSplitFunction') IS NOT NULL
BEGIN
	DROP PROC dbo.Proc_TestSplitFunction
END
GO
CREATE PROC dbo.Proc_TestSplitFunction
(
	@str NVARCHAR(MAX)	
)
AS
BEGIN
	SET NOCOUNT ON
	SELECT item FROM dbo.[Fun_StringToBigIntArray](@str)
END
GO

二、测试脚本：

--测试表值参数
DECLARE @i BIGINT,@iMax BIGINT,@tvp dbo.type_BigInt
SET @i=1
SET @iMax=10	--第2、3次改成 100
WHILE @i<=@iMax
BEGIN
	INSERT INTO @tvp(id) VALUES(@i)
	SET @i=@i+1
END
EXEC dbo.Proc_TestTableParameter @tvp

--测试分割字符串表值变量
DECLARE @v NVARCHAR(MAX)
SET @v='1,2,3,4,5,6,7,8,9,10'  --第2、3次改成：'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100'
EXEC dbo.Proc_TestSplitFunction @v

测试结果（单位：秒）：

测试方法	10行，10线程，每线程10次	100行，10线程，每线程10次	100行，100线程，每线程100次
表值参数	0.3437	0.8906	86.7304
分割字符串函数	0.1718	0.2343	12.5101

由上可见， 表值参数对比传统的分割字符串没有优势。

不过，分割字符串函数只能处理一个字段，表值参数在字段较多的情况下就更方便了。