1

I have a stored procedure that takes in XML parameter. I am then selecting from this parameter and inserting into a temp table.

All is well if the XML parameter is small in length. However, when passing a large XML value in, the performance drops off.

Simply selecting from the XML parameter is fine, the issue is when I try to insert from the select.

DECLARE @OptionalTagXml XML

SET @OptionalTagXml = 'some very long XML string'

CREATE TABLE  #OptionalTags  (
        [value] varchar(100), 
        [description] varchar(100)
    )

-- Introducing this line causes very slow performance, the select by itself is fine.
--INSERT INTO #OptionalTags ([value], [description])    
SELECT TagValue.value('(./value)[1]', 'varchar(100)') AS [value],
        TagValue.value('(./description)[1]', 'varchar(100)') AS [description]     
FROM @OptionalTagXml.nodes('/tags/tag') AS T(TagValue)

DROP TABLE #OptionalTags

An example XML structure is here:

SET @OptionalTagXml = '<tags><tag><description>ViewPermissionCustomerId</description><value>1028996</value><group>0</group></tag><tag><description>ViewPermissionBrandId</description><value>2</value><group>0</group></tag><tag><description>ViewPermissionBrandId</description><value>1</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1028997</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1033094</value><group>0</group></tag><tag><description>ViewPermssionCustomerId</description><value>1028999</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1028998</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1029000</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1029001</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1033368</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1028882</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1033369</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1029002</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1030235</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1029003</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1033370</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1029005</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1033371</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1028995</value><group>0</group></tag><tag><description>ViewPermissionCustomerId</description><value>1034356</value><group>0</group></tag></tags>'

Unfortunately I cannot add a problematic example, as it's length is way in excess of the character limit for a post, but to put in perspective, the above sample is 1994 characters, whereas a problematic one can be in the region of 50,000 characters.

Any help in understanding this problem, or any alternative approaches would be appreciated.

3

0

Browse other questions tagged or ask your own question.