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”/>’Â
EXEC myProc1 @hDoc
EXEC myProc2 @hDoc