Why insert-select to variable table from XML variable so slow?
This is a bug in SQL Server 2008. Use
insert @columns select ColumnNames.value('.', 'nvarchar(300)') namefrom @xColumns.nodes('/columns/name') T1(ColumnNames)OPTION (OPTIMIZE FOR ( @xColumns = NULL ))This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue is available (under traceflag 4130).
The reason for the performance regression is explained in a different connect item
The spool was introduced due to a general halloween protection logic (that is not needed for the XQuery expressions).
Looks to be an issue specific to SQL Server 2008. When I run the code in SQL Server 2005, both inserts run quickly and produce identical execution plans that start with the fragment shown below as Plan 1. In 2008, the first insert uses Plan 1 but the second insert produces Plan 2. The remainder of both plans beyond the fragment shown are identical.
Plan 1
Plan 2
