OPENXML() and performance
-
Hi. I have a stored procedure, and it's working fine. One of the input parameters is XML (@p_xml) and is processed like this:
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xmlSELECT * INTO #raw_table
FROM
OPENXML(@docHandle, '//NODE', 2)The problem is when the XML is really big (~20 MB with ~35000 nodes). Then the OPENXML statement uses 7-8 minutes to finish. Is there any way to speed up OPENXML, or is there any other workaround ? I have tried both select into and insert into and both temp table and table variable. All with the same result. Could someone help me ? Kjetil
-
Hi. I have a stored procedure, and it's working fine. One of the input parameters is XML (@p_xml) and is processed like this:
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xmlSELECT * INTO #raw_table
FROM
OPENXML(@docHandle, '//NODE', 2)The problem is when the XML is really big (~20 MB with ~35000 nodes). Then the OPENXML statement uses 7-8 minutes to finish. Is there any way to speed up OPENXML, or is there any other workaround ? I have tried both select into and insert into and both temp table and table variable. All with the same result. Could someone help me ? Kjetil
I doubt it. Its not the fast thing around. BTW, with the code shown, you do realise you are locking most of the system tables in your tempdb database for the 7-8 minutes it takes to run. A select into locks system tables for the duration of its run, this could seriously impact other users. Sorry I can't help further.
Bob Ashfield Consultants Ltd
-
I doubt it. Its not the fast thing around. BTW, with the code shown, you do realise you are locking most of the system tables in your tempdb database for the 7-8 minutes it takes to run. A select into locks system tables for the duration of its run, this could seriously impact other users. Sorry I can't help further.
Bob Ashfield Consultants Ltd
Hi. I actually use insert into, but I was to lazy to paste the table declaration in my post :) Kjetil