Performance impact when using XML columns in a table with MS SQL 2008

Question

I am using a simple table with 6 columns, 3 of which are of XML type, not schema-constrained. When the table reaches a size around 120,000 or 150,000 rows, I see a dramatic performance cost in doing any query in the table. For comparison, I have another table, which grows in size at about the same rate, but only contain scalar types (int, datetime, a few float columns). That table performs perfectly fine even after 200,000 rows. And by the way, I am not using XQuery on the xml columns, i am only using regular SQL query statements.

Some specifics: both tables contain a DateTime field called SampleTime. a statement like (it's in a stored procedure but I show you the actual statement)

SELECT MAX(sampleTime) SampleTime
FROM dbo.MyRecords
WHERE PlacementID=@somenumber

takes 0 seconds on the table without xml columns, and anything from 13 to 20 seconds on the table with XML columns. That depends on which drive I set my database on. At the moment it sits on a different spindle (not C:) and it takes 13 seconds.

Has anyone seen this behavior before, or have any hint at what I am doing wrong? I tried this with SQL 2008 EXPRESS and the full-blown SQL Server 2008, that made no difference. Oh, one last detail: I am doing this from a C# application, .NET 3.5, using SqlConnection, SqlReader, etc..

I'd appreciate some insight into that, thanks!

Sam

Answer 1

Do you have an index on PlacementID and sampleTime in that order?

Both the size of the table and columns types are irrelevant if an index can satisfy the query ("covering")

Answer 2

We noticed significant performance problems when the size of individual xml rows surpassed 64kb. Not sure if you are in that range or not, but it was the difference between nearly instant queries and those taking upwards of 60 seconds.

At the end of the day we extracted out all of the queryable data into normal sql tables to perform our searches on. Incidentally, that was the last time we used the xml data type.