MERGE, XML Access, and a CTE: Not So Fast

A bit of old news, as I return to the blog in earnest after some time away..

Last summer, in the posts Database Programming: What I’ve Learned About SQL Server 2008 (with a little on SQL Server 2005 thrown in) and Database Programming: Why I Like MERGE, I commented that I’d been able to build a MERGE statement which included XML access within a CTE to perform our basic data maintenance in a new system we were building.

Well, when we got around to performance testing this code, performance was pretty miserable, with logical reads on a worktable in the tens of millions when processing eight thousand rows.

My testing indicated that at least part of the issue related to the use of XML access within a CTE; the entire XML document was being rebuilt and reparsed with each iteration through the CTE. 

The solution in this case was to refactor the query by placing the results of the XML access into a temp table, and accessing that structure in the MERGE statement. This refactoring also removed the CTE, and resulted in a two-orders-of-magnitude performance improvement.

So.. I still like MERGE, but CTEs using XML access? Not so much.

-wp

this copyrighted material was originally posted at https://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites