Difference between SQL 2005 and SQL 2008 for inserting multiple rows with XML [duplicate]

Question

Possible Duplicate:
Why insert-select to variable table from XML variable so slow?

I am using the following SQL code for inserting multiple rows of data in a table. The data is passed to the stored procedure using an XML variable :

INSERT INTO MyTable
   SELECT SampleTime = T.Item.value('SampleTime[1]', 'datetime'),
          Volume1 = T.Item.value('Volume1[1]', 'float'),
          Volume2 = T.Item.value('Volume2[1]', 'float')
   FROM @xml.nodes('//Root/MyRecord') T(item)

I have a whole bunch of unit tests to verify that I am inserting the right information, the right number of records, etc.. when I call the stored procedure.

All fine and dandy - that is, until we began to monkey around with the compatibility level of the database.

The code above worked beautifully as long as we kept the compatibility level of the DB at 90 (SQL 2005). When we set the compatibility level at 100 (SQL 2008), the unit tests failed, because the stored procedure using the code above times out.

The unit tests are dropping the database, re-creating it from scripts, and running the tests on the brand new DB, so it's not - I think - a question of the 'old compatibility level' sticking around.

Using the SQL Management studio, I made up a quick test SQL script. Using the same XML chunk, I alter the DB compat level , truncate the table, then use the code above to insert 650 rows. When the level is 90 (SQL 2005), it runs in milliseconds. When the level is 100 (SQL 2008) it sometimes takes over a minute, sometimes runs in milliseconds.

I'd appreciate any insight anyone might have into that.

---

EDIT

The script takes over a minute to run with my actual data, which has more rows than I show here, is a real table, and has an index. With the following example code, the difference goes between milliseconds and around 5 seconds.

--use [master]
--ALTER DATABASE MyDB SET compatibility_level =100

use [MyDB]

declare @xml xml

set @xml = '<?xml version="1.0"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Record>
    <SampleTime>2009-01-24T00:00:00</SampleTime>
    <Volume1>0</Volume1>
    <Volume2>0</Volume2>
  </Record>
..... 653 records, sample time spaced out 4 hours ........
</Root>'

DECLARE @myTable TABLE(
ID int IDENTITY(1,1) NOT NULL,
    [SampleTime] [datetime] NOT NULL,
    [Volume1] [float] NULL,
    [Volume2] [float] NULL)

INSERT INTO @myTable
    select 
        T.Item.value('SampleTime[1]', 'datetime') as SampleTime,
            Volume1 = T.Item.value('Volume1[1]', 'float'),
            Volume2 = T.Item.value('Volume2[1]', 'float')
    FROM @xml.nodes('//Root/Record') T(item)

I uncomment the 2 lines at the top, select them and run just that (the ALTER DATABASE statement), then comment the 2 lines, deselect any text and run the whole thing.

When I change from 90 to 100, it runs all the time in 5 seconds (I change the level once, but I run the series several times to see if I have consistent results). When I change from 100 to 90, it runs in milliseconds all the time. Just so you can play with it too. I am using SQL Server 2008 R2 standard edition.

Answer 1

This is a bug in SQL Server 2008.

It can be fixed by enabling TraceFlag 4130 as this repro shows.

USE [MyDB]

--Comment / Uncomment the next two lines as appropriate to test the effect with the TF turned on or off
DBCC TRACEON(4130,-1)
--DBCC TRACEOFF(4130,-1)

GO
DBCC FREEPROCCACHE
GO
declare @xmlstring varchar(max) = '<?xml version="1.0"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'

SELECT @xmlstring += 
  '<Record>
    <SampleTime>2009-01-24T00:00:00</SampleTime>
    <Volume1>0</Volume1>
    <Volume2>0</Volume2>
  </Record>'
FROM master..spt_values  
WHERE type='P' AND number BETWEEN 1 AND 653

set @xmlstring += '</Root>'

DECLARE @xml xml =  @xmlstring
SELECT @xml

DECLARE @myTable TABLE(
ID int IDENTITY(1,1) NOT NULL,
    [SampleTime] [datetime] NOT NULL,
    [Volume1] [float] NULL,
    [Volume2] [float] NULL)

INSERT INTO @myTable
    select 
        T.item.value('SampleTime[1]', 'datetime') as SampleTime,
            Volume1 = T.item.value('Volume1[1]', 'float'),
            Volume2 = T.item.value('Volume2[1]', 'float')
    FROM @xml.nodes('//Root/Record') T(item)

Various other workarounds are mentioned in the relevant Microsoft Connect Site item.

Answer 2

By the way, anybody else who is experiencing this problem may find that this post on SQLServerCentral is of some use. It appears that subtle differences in how the x-path portion of the query is formulated can have a large effect on the performance depending on the compatibility setting.

Originally I couldn't find a whole lot of explanation on why the compatibility setting changes the performance so significantly but since then Martin Smith's answer and the corresponding issue reported on Microsoft Connect site explain that this is likely a bug in SQL 2008 and that there are a hotfix as well as several other workarounds that exist with the one I in the original link on SQLServerCentral being one of them.