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.
