SQL Server XQuery For Idiots

November 30, 2009 at 9:17 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )

I’m still struggling with learning XQuery. My latest little revelation was small, but vital.

The difference between:

@inXML.nodes('/rss/channel/item')

and

@inXML.nodes('/rss[1]/channel[1]/item')

Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like “/rss/channel” so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data.

It’s a little thing, but it made an enormous difference. I’ve still got a long way to go in learning how to use XPath within XQuery.

UPDATED: I modified the title so that it’s clear I’m talking about SQL Server here.

Advertisements

Permalink 6 Comments

XML to Multiple Queries

November 25, 2008 at 3:05 pm (TSQL) (, , )

One of our development teams created a set of queries that are receiving some rather large XML parameters for processing. Unfortunately, the developers didn’t think things through entirely. They passed the same, large, XML string in to the server five times in a row. Needless to say, the performance was substandard since the XML parser had to be instantiated five times. My initial suggestion was to use a wrapper procedure to perform a single load of the XML data and then call the other 5 procedures. I further suggested to load the XML into a temporary table and use that within the other procs. Unfortunately this is all taking place within SQL Server 2000. When my initial set of recommendations was done, we had solved one problem and introduced another. We were getting serious recompiles. If we could move this to SQL Server 2008, there are a number of possible solutions. However, my co-worker, Det (name changed to protect the guilty), came up with a solution that just hadn’t occurred to me. He dropped the temporary table and instead simply passed the XML handle generated in the wrapper procedure to each of the calling procedures. It works like a charm and supplied a huge performance improvement. I wish I had thought of it.

The wrapper proc now looks a bit like this:

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlCollection,
‘<ClientAndIndustriesCollection
xmlns:cl=http://Our.local.def
xmlns:i=http://www.w3.org/2001/XMLSchema-instance
xmlns:a=”http://thds.fmglobal.com/v1.0.0/ClientDataContract”/>&#8217;
 

EXEC myProc1 @hDoc
EXEC myProc2 @hDoc

Permalink Leave a Comment

Breaking Down Complex Execution Plans

June 23, 2008 at 7:52 am (SQL Server 2005, SQL Server 2008, TSQL) (, )

Peter Ward, the editor at SQL Server Performance, has published an article of mine on Breaking Down Complex Execution Plans. I go way beyond the blog entry below and show how the estimated costs in execution plans can mess you up, how to use the XML in execution plans to search through them for costly operations or operations that have mismatched estimated rows & actual rows and some other tips and tricks. Hopefully it’s worth a read.

Permalink Leave a Comment

XML Showplan in 2008

May 28, 2008 at 3:37 pm (SQL Server 2005, SQL Server 2008, TSQL) (, , )

I just found my first fundamental difference between execution plans in SQL Server 2005 and SQL Server 2008. In 2005 when you run an query and have it display an XML execution plan by issuing the statement “SET STATISTICS XML ON” you get a second result set that includes a URL. The same thing happens in 2008. When you click on that URL in 2005 it opens the XML file in a new window. If you want to look at the graphical plan, you have to save the file with  a “*.SQLPLAN” exentension. In 2008 if you click on the URL, it opens a new window, but it shows the graphical plan. What happened to the XML? The developers at Microsoft are smart, that’s what happened. If you then right click on that graphical plan a menu choice “Show Execution Plan XML…” is available. Clicking on that opens the XML plan.

So I went one step further. I knew that the graphical plan in 2005 was just an XML plan, if not so readily accessible (do a “Save As” and force an XML extension on it). So I right clicked on a graphical plan that I generated the old fashioned way. Sure enough, the same menu choice to show the XML was there. Nice functionality.

Permalink Leave a Comment