FOR XML ... TYPE much slower than FOR XML?

Question

Running SQL Server 2014. I have a stored procedure that returns a quite large XML. It goes something like this:

SELECT(
   ...
   FOR XML PATH (N''), ROOT, TYPE

Now, that query runs in 1 second. If I remove TYPE it runs in around half the time:

SELECT(
   ...
   FOR XML PATH (N''), ROOT

Obviously, the latter returns an nvarchar(max) instead of an xml. I want xml data, but if I ask for xml it gets slower! If I want to fetch xml data on the client, is it really necessary to convert it to xml using the TYPE directive above?

Q: Anyway, why is FOR XML ... TYPE significantly slower than FOR XML ...? Is there any way to improve the conversion?

Answer 1

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...