Fast XML, slow XML

Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data.

The process works by selecting a chunk of rows from the source as XML, which is passed through the application to the target server, where it's shredded into a global temp table. (The source and target databases can be on 2 different instances.) That process repeats until all the data required is in the temp table on the target instance. Finally, the temp table records are consolidated into the actual target database table.

The problem we're having is that in some situations, the second chunk is extremely slow, with very high CPU usage, and it just goes nowhere. We're able to reproduce the issue in our hosting environment, but not in dev or QA. Some of our clients are having this problem, too -- one of them let it run overnight and finally killed it the next morning after running for 18 (!) hours. In that case, I'm not sure how far along it got; I can't get past the second chunk in hosting after waiting for ~2 hours.

This is the statement batch for the first chunk:

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

SELECT
    n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
    n.x.value(N'field2[1]', 'smallint') AS field2,
    ... (8 more fields of various types) ...
    INTO [##target_2994] /*******/
    FROM @src.nodes('Rows[1]/Row') n(x);

And this is the batch for the second and subsequent chunks, which is the problem:

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

INSERT INTO [dbo].[##target_2994] /*******/
    SELECT
        n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
        n.x.value(N'field2[1]', 'smallint') AS field2,
        ... (8 more fields of various types) ...
        FROM @src.nodes('Rows[1]/Row') n(x);

Here's what I've looked at so far:

• It's not a blocking issue: wait stats are 99% `S

There is a comparable issue logged on connect for the version you're having a problem with - An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1.


Using SQL2008, when shredding XML using the nodes query, the
performance is fine when just SELECTing. However, when you want to
INSERT the data to a table/temptable/tablevariable the performance
becomes very poor.

StackExchange Database Administrators Q#27264, answer score: 4