XML type decomposition to rowset in stored proc using nodes()

I have the SP I call the following example ways (the call is not from SQL rather it is from a .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 perhaps

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

   -- 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'm finding that the xml decomposition is the slowest part (about 96% of the query in each case - and believe me I'm inserting/deleting/altering tonnes of data in the rest of the proc).

I'm very curious if my way of decomposing XML is the most optimal way for the given input sets. My criteria for using XML is simply to pass the SP a number of integers - so any suggestions for better ways are gratefully received.

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

Since you can't use a table parameter, try passing in a CSV sting and have the stored procedure split it into rows for you.

There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

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

You need to create a split function. This is how a split function can be used:

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 but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 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)

Once the Numbers table is set up, create this split function:

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 

You can now easily split a CSV string into a table and join on it or use it however you need:

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

Just select the best string splitting function from the article (CLR, loop, whatever) that works with your large set of CSVs and you should get better performance.

If all you're doing is extracting a single field from a collection of elements, you would probably be better served by simply parsing the XML as a string, extracting the field into an array and either passing the array to the SP or inserting the values into a temp table and passing the name of the table to the SP. Either way, you don't have the database engine parsing the XML. This approach is not the most flexible, so it may not be appropriate if you need to do this for several different types of elements.