Did you try to set variables with the results as XML and as VARCHAR(MAX) without displaying them? Maybe the time difference you measure is bound to preparing the viewer? Pasting the first letters into a grid column is faster than creating a well formed, indented, displayable XML...
Sepcifying "TYPE" is not needed in most cases. You really need this with nested XML only. Just play around with aliases, PATH- and ROOT-literals and - of course - with or without TYPE:
And - very important! - try to call this with the surrounding SELECT and without:
SELECT
(
SELECT tbls.TABLE_NAME AS [@TableName]
,(
SELECT COLUMN_NAME AS [@ColumName]
FROM INFORMATION_SCHEMA.COLUMNS AS cols
WHERE cols.TABLE_NAME=Tbls.TABLE_NAME
FOR XML PATH('COLUMN') /*,TYPE*/
) /*AS alias*/
FROM INFORMATION_SCHEMA.TABLES AS Tbls
FOR XML PATH('TABLE'),ROOT('ALL_TABLES') /*,TYPE*/
) /*AS alias*/
I don't know, how you continue with your generated XML. If you transfer it to your application it will be a plain string anyway.
Conclusio: Take the faster approach :-)
By the way...
I do not know your Stored Procedure and what else is done there besides the SELECT...
In most cases it is a bad habit to use SPs just to read data.
If your SP is not more than a wrapper around your SELECT you should think about a (single-statement!) table valued function to retrieve your data.
This function is easily queried and transformed to XML with
SELECT *
FROM dbo.MyFunction(/*Parameters*/)
FOR XML PATH('TheRowsName'),ROOT('TheRootName') [,TYPE]
Or - if you need this as XML everytime, you might define a scalar function delivering XML or VARCHAR(MAX). The re-usability of functions is way better than with SPs...
PATH()
andAUTO()
modes, for example, is significant. Second, have you tried to compare them by running in the reverse order, and / or with cold data cache?AUTO
be faster?dbcc dropcleanbuffers
after each execution to ensure that no data cached previously would skew the comparison.for xml auto()
usually tends to be much slower thanpath()
, not vice versa.