使用 nodes() 将 XML 类型分解为存储过程中的行集

Question

我有 SP，我调用以下示例方式 (调用不是来自 SQL，而是来自 .net 程序)

或

    -- run with a few grantees
    exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/><grantee id="101"/></grantees>'
   -- takes about 1 sec with > 59s on xml decomp

或许

   -- run with lots of grantees (approx 2000)
   exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/>....<grantee id="2001"/></grantees>'
   -- takes about 5 sec with > 4s on xml decomp

或许

   -- run with mega loads of grantees (approx 12000)
   exec someproc 99999, '<grantees><grantee id="99"/><grantee id="100"/>....<grantee id="12001"/></grantees>'
   -- takes about 1 min with > 59s on xml decomp

而且我发现 xml 分解是最慢的部分(每种情况下约占查询的 96% - 相信我，我正在插入/删除/更改大量数据过程的其余部分)。

我很好奇我分解 XML 的方式是否是给定输入集的最佳方式。我使用 XML 的标准只是向 SP 传递一些整数 - 因此，我们非常感谢收到任何关于更好方法的建议。

create procedure someproc(@id int, @users xml = '<grantees/>') as
begin
        -- decompose the users into a row set
        declare @allUsers table (
            id int
        )

        insert into @allUsers (id)
        select distinct grantee.value('@id', 'int') uno 
           from @users.nodes('/grantees/grantee') grantees(grantee)
           where isnull(grantee.value('@id', 'int'), 0) > 0

    select * from @allUsers

    -- other stuff happens
end

Answer 1

由于您不能使用表参数，请尝试传入 CSV 字符串并让存储过程为您将其拆分为行。

在 SQL Server 中有多种拆分字符串的方法。本文介绍了几乎所有方法的优缺点:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

您需要创建一个拆分函数。这是拆分函数的用法:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL但是在 SQL Server 中拆分字符串的方法有很多种，请参阅前面的链接，其中解释了每种方法的优缺点。

要使 Numbers Table 方法起作用，您需要执行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置 Numbers 表后，创建此拆分函数:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(   ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO 

您现在可以轻松地将 CSV 字符串拆分为一个表，然后根据需要加入或使用它:

CREATE PROCEDURE YourProcedure
(
    @CSV_Param   varchar(1000)
)
AS

--just an example of what you can do
UPDATE t
    SET Col1=...
    FROM dbo.FN_ListToTable(',',@CSV_Param) dt
        INNER JOIN TBL_USERS                 t ON  CAST(dt.value AS INT)=t.id

GO

只需从适用于您的大型 CSV 集的文章(CLR、循环等)中选择最佳字符串拆分函数，您应该会获得更好的性能。