Splitting an XML Type into a Set of Strings in a Stored Procedure Using Nodes ()

Question

I have an SP. I call the following example: (the call is not from SQL, but from the .net program)

or

    -- 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

or maybe

   -- 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

or maybe

   -- 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

And I found that the decomposition of xml is the slowest part (about 96% of the request in each case), and believe me, I insert / delete / change tons of data in the rest of the millet).

I am very curious if my way of decomposing XML is the most optimal way for given sets of input data. My criteria for using XML is to simply pass an integer to the SP — so any suggestions on better methods are gratefully accepted.

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. PRO CON :

" SQL Server 2005 Beyond, " Erland Sommarskog

split. :

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

TSQL, SQL Server, . , PRO CON .

Numbers Table , Numbers, 1 10000:

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

(CLR, loop, whatever), CSV, .

Answer 2

, , , , , XML , SP temp SP. , , XML. , , .