Shredding XML with nodes()/value() in SQL Server has performance issues when you query a lot of columns. There is one nested loop join with a call to a xml function for each column.
Query plan with 3 columns:
Query plan with 5 columns:
Just imagine what it would look like with more than 150 columns.
Another option for you is to use OPENXML. It does not have the same problems with many columns.
Your query would look something like this:
declare @H int;
declare @X xml;
exec sys.sp_xml_preparedocument @H output,
@X;
select C1,
C2,
C3
from
openxml(@H, 'Table/row', 0)
with (
C1 int,
C2 int,
C3 int
);
exec sys.sp_xml_removedocument @H;
For me, using 150 columns and 1000 rows took about 14 seconds with nodes()/value() and 3 seconds with OPENXML.
Vote for a change.
Code used for testing;
drop table T;
go
declare @C int = 150;
declare @S nvarchar(max);
declare @X xml;
declare @N int = 1000;
declare @D datetime;
set @S = 'create table T('+
stuff((
select top(@C) ', '+N'C'+cast(row_number() over(order by 1/0) as nvarchar(3)) + N' int'
from sys.columns
for xml path('')
), 1, 2, '') + ')'
exec sp_executesql @S;
set @S = 'insert into T select top(@N) '+
stuff((
select top(@C) ',1'
from sys.columns as c1
for xml path('')
), 1, 1, '') + ' from sys.columns as c1, sys.columns as c2';
exec sp_executesql @S, N'@N int', @N;
set @X = (
select *
from dbo.T
for xml raw, root('Table')
);
set @S = 'select '+
stuff((
select top(@C) ', '+N'T.X.value(''@C'+cast(row_number() over(order by 1/0) as nvarchar(3)) + N''', ''int'')'
from sys.columns
for xml path('')
), 1, 2, '') + ' from @X.nodes(''Table/row'') as T(X)'
set @D = getdate();
exec sp_executesql @S, N'@X xml', @X;
select datediff(second, @D, getdate());
set @S = 'declare @H int;
exec sp_xml_preparedocument @H output, @X;
select *
from openxml(@H, ''Table/row'', 0)
with (' +
stuff((
select top(@C) ', C'+cast(row_number() over(order by 1/0) as nvarchar(3))+ ' int'
from sys.columns
for xml path('')
), 1, 2, '') + ');
exec sys.sp_xml_removedocument @H';
set @D = getdate();
exec sp_executesql @S, N'@X xml', @X
select datediff(second, @D, getdate());
