Possible duplicate:
Why insert - select a table variable from an XML variable so slowly?
I am using the following SQL code to insert multiple rows of data into a table. Data is transferred to the stored procedure using the 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 correct number of records, etc., when I call the stored procedure.
Everything is beautiful and dandy, that is, until we start the monkey with the database compatibility level.
The code above worked fine while we kept the database compatibility level at 90 (SQL 2005). When we set the compatibility level to 100 (SQL 2008), unit tests are not executed because the stored procedure using the above code does not work.
Single tests drop the database, recreate it from scripts and run tests in a new database, so no - I think that the question of the "old compatibility level" sticks around.
Using SQL Management Studio, I compiled a quick SQL test script. Using the same XML block, I change the DB compatibility level, crop the table, and then use the code above to insert 650 rows. When a level is 90 (SQL 2005), it starts in milliseconds. When level 100 (SQL 2008), it sometimes takes more than a minute, sometimes it works in milliseconds.
I would appreciate any understanding anyone could have in that.
EDIT
The script takes a minute to work with my actual data, which has more rows than shown here, is a real table and has an index. In the following code example, the difference is between milliseconds and about 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 will uncomment the 2 lines at the top, select them and run just that (the ALTER DATABASE statement), then comment out the 2 lines, deselect the text and run it all.
When I change from 90 to 100, it works 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 works in milliseconds all the time. Just to play with him too. I am using the standard version of SQL Server 2008 R2.