February 27, 2009 at 7:12 am
I have a stored procedure that accepts an XML string, inserts it into a temp table and then does some work. The issue that I'm having is related to inserting it into the temp table. The XML string that I am passing in has 30000 nodes. In our development environment that whole stored procedure takes about 15 seconds to run. In our validation environment, it has run for more than 15 minutes before I killed it. I'm at a loss as to what the cause could be. The hardware is identical with the exception that the validation environment has more memory.
I used a much smaller XML string in validation so that I could compare the execution plans. The structure of the execution plans is the same. The only difference between the two is that in development the majority of work is being done in Table valued function (XML Reader), in validation the majority of the work is being done in Table valued function (XML Reader with XPath filter).
I'm a complete novice when it comes to XML. What exactly is this telling me?
Thanks,
Greg
February 27, 2009 at 7:19 am
Here are the two execution plans.
February 27, 2009 at 11:21 am
Although I never figured out why the same stored procedure was behaving differently in the two different environments, I did resolve this issue. I decided to take a completely different approach.
The insert in the original stored procedure looked something like this:
SELECT
X.id.value('Col1[1]', 'VARCHAR(50)') AS Col1,
ISNULL(X.id.value('Col2[1]', 'INT'), 0) AS Col2,
ISNULL(X.id.value('Col3[1]', 'VARCHAR(10)'), '') AS Col3
INTO #Temp_Table
FROM @XMLText.nodes('/ROOT/RandomData') AS X(id)
I replaced it with this:
EXEC sp_xml_preparedocument @Handle OUTPUT, @XMLText
SELECT Col1, Col2, Col3
INTO #Temp_Table
FROM OPENXML(@Handle, '/ROOT/RandomData', 2) WITH
(Col1VARCHAR(50),
Col2 INT,
Col3VARCHAR(10))
EXEC sp_xml_removedocument @Handle
By using OPENXML instead, the original stored procedure went from 15 to 10 seconds in development. More importantly, it also runs in 10 seconds in validation. Hopefully this will help someone if they run into similar performance issues.
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply