Why insert-select to variable table from XML variable so slow?

by

This is a bug in SQL Server 2008.
Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @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).

More Related Contents:

  1. How to add xml encoding to xml Output in SQL Server
  2. How to split a comma-separated value to columns
  3. Using T-SQL, return nth delimited element from a string
  4. How to get a float result by dividing two integer values using T-SQL?
  5. SQL NVARCHAR and VARCHAR Limits
  6. How to add Active Directory user group as login in SQL Server
  7. Why use the INCLUDE clause when creating an index?
  8. Confused about UPDLOCK, HOLDLOCK
  9. Retrieve column definition for stored procedure result set
  10. Change Notification with Sql Server 2008
  11. Regular Expressions in SQL Server servers?
  12. Unique constraint on multiple columns
  13. An attempt was made to access a socket in a way forbidden by its access permissions
  14. How do I find a stored procedure containing ?
  15. How to find the port for MS SQL Server 2008?
  16. Should every User Table have a Clustered Index?
  17. An attempt to login using SQL authentication failed
  18. How to set monday as first day of week in SQL Server
  19. Drop primary key using script in SQL Server database [duplicate]
  20. How to convert number of minutes to hh:mm format in TSQL?
  21. How to find SQL Server running port?
  22. SQL Server Xml query with multiple namespaces
  23. Temporarily disable all foreign key constraints
  24. How to display two digits after decimal point in SQL Server
  25. Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?
  26. The request failed or the service did not respond in a timely fashion?
  27. What are row, page and table locks? And when they are acquired?
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment