T-SQL Tuesday #002: Is it XML, or Not?!?

The query optimizer is a finicky thing, and sometimes it doesn't understand exactly what you're trying to do until you give it a bit more information. The situation I'm going to describe in this post is one such case. By providing the optimizer with ever-so-slightly more data, it's possible to make some XML processing over 300 times faster.

Here's the situation: The XML I was working with was stored in a table, but not typed as XML. Rather, it had been typed as VARCHAR(MAX). This presents an interesting conundrum for the optimizer: should the query be optimized as though operations are being done on a string, or on XML?

If you would like to follow along with the examples below, here's some DDL and an INSERT statement to populate a test table using AdventureWorks data:

Running this code will put one row into the temp table, with an XML document containing all of the product data from the AdventureWorks Production.Product table. And now, just for kicks, what if we want to pull all of the ProductIDs out of that document? Simple enough...

This code isn't especially interesting or puzzling in and of itself. It converts the document(s) in the table to XML, runs them through the .nodes() function to produce one row per product node, and pulls out the first ProductID attribute found. If you've actually run the code on your end at this point, you know why I was puzzled: This code takes a full 20 seconds to run on my end. Which is a bit extreme, considering that there are only 504 products in the AdventureWorks database. In the real situation, the documents were several times bigger, and 20 seconds was over an hour in some cases. And that just wouldn't do.

And so much head-scratching ensued. And teeth gnashing. And cursing of the SQL Server programmability team. You know, a typical day at the office.

I pulled apart my code, put it back together again, and considered writing a CLR UDF to do the processing. But then I tried something on a whim:

So was a cursor and document-by-document processing the answer? At first, it seemed so. But after further messing around I noticed something: adding TOP(1), so that only a single row was processed, wasn't taking too long. Could it be that the query processor was doing a lot more work than necessary, like converting the text to XML 504 times?

The TYPE directive can be used with FOR XML to make your query return the XML document typed as XML rather than typed as a string. Perhaps it would work here?

The empty path expression is needed because the TYPE directive only works in conjunction with a valid FOR XML mode. Using an empty path has zero net effect on the actual XML produced in this case. But using the TYPE directive has quite a huge effect: A reduction in query time to around 58 milliseconds on my end. The 30,000% speedup I promised you earlier.

So why does this work? A quick peek at the plans indicates that I was correct. Here's the first part of the plan for the first version of the query:

Notice the "UDX" iterator? That's an XML iterator that handles the conversion to typed XML. And in the first case, we don't get one, even though we've "technically" converted the string to XML at that point.

This story was puzzling, and somewhat arcane, but it has a moral that stretches far beyond this simple example: Only by giving the query optimizer much more information than any rational person might think necessary did we get a plan that does the right thing. And that is quite often the case when working with SQL Server. CHECK constraints, foreign keys, UNIQUE constraints, the DISTINCT keyword, GROUP BY, APPLY, and various other constructs are more than just ways to define your requirements or the output you're looking for. They can be used to provide information to the query optimizer to help it determine the best way to process your data. Information that can make your query return in a second instead of an hour. Information that will make your users happy and your project a success.

The secret to writing high performance T-SQL? Step out of your human mind. Un-puzzle. Be the optimizer. And until next month, thank you for reading this entry in T-SQL Tuesday #002!

Sorry, but I can't debug something like that remotely. If you'd like e-mail me using the "Email" link on the upper righthand corner of the page and we'll set up a consulting arrangement so that I can get into your server and check things out.

In SQL 2k, there was no XML data type so subqueries that used FOR XML could not return XML, so they returned nvarchar instead. TYPE was added in SQL 2k5 and I often describe it as "and I really meant XML". It allows any XML subquery to actually return XML rather than nvarchar. The CTE is no exception to this.

The pain is the need for backwards compatibility where asking for XML doesn't return XML in a subquery.

Regards,

Greg

October 6, 2014 2:12 AM

Leave a Comment

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.