I've been working on improving a stored procedure. In most instances, it runs fairly quickly but I've encountered one situation where it takes about a minute and a half to run.

And it appears that part of the problem is using table variables. So, I am working on changing some of them to more flexible temporary tables. Which isn't bad but I have a multi-statement table valued function which I use, and you can't use temporary tables in a function.

So I pull the function apart and rewrite it as a sproc using a temporary table. And this is where the first lie comes from. The function and sproc both parse a section of XML passed in. While the execution plan lies about the cost of multi-statement table functions, placing this into a stored procedure gave me my first strange reading of the day.

Parsing the XML using SELECT tbl.col.value('@AttributeShortName', 'nvarchar(256)') shortname FROM @SearchXML2.nodes('//Search') tbl(col) led to the execution plan telling me that this statement took 75% of the resources in the overall plan. The other, similar statement, took 25%. And neither of these were pain points, I know because the function call returned in millisecond time while a later insert statement took most of the resources.

So I changed these to use sp_xml_preparedocument and saw an improvement. Now these two XML translations only took up 50% of the resources between them. All of which doesn't help me in identifying the real problem.