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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: