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

I’m trying to insert some data from a XML document into a variable table. What blows my mind is that the same select-into (bulk) runs in no time while insert-select takes ages and holds SQL server process accountable for 100% CPU usage while the query executes.

I took a look at the execution plan and INDEED there’s a difference. The insert-select adds an extra “Table spool” node even though it doesn’t assign cost. The “Table Valued Function [XML Reader]” then gets 92%. With select-into, the two “Table Valued Function [XML Reader]” get 49% each.

  • Search all columns of a table for a value?
  • How to use calculated column value to another column in the same SQL Query
  • Entity Framework connecting to SQLEXPRESS not SQL Compact
  • Need substitute for using Cross Apply with SQL Server 2000
  • What are locking issues in OLAP?
  • Executing SSIS Package as SQLAgent Job
  • Please explain “WHY is this happening” and “HOW to resolve this (elegantly)” as I can indeed bulk insert into a temporary table and then in turn insert into variable table, but that’s just creepy.

    I tried this on SQL 10.50.1600, 10.00.2531 with the same results

    Here’s a test case:

    declare @xColumns xml
    declare @columns table(name nvarchar(300))
    if OBJECT_ID('tempdb.dbo.#columns') is not null drop table #columns
    insert @columns select name from sys.all_columns
    set @xColumns = (select name from @columns for xml path('columns'))
    delete @columns
    print 'XML data size: ' + cast(datalength(@xColumns) as varchar(30))
    --raiserror('selecting', 10, 1) with nowait
    --select ColumnNames.value('.', 'nvarchar(300)') name
    --from @xColumns.nodes('/columns/name') T1(ColumnNames)
    raiserror('selecting into #columns', 10, 1) with nowait
    select ColumnNames.value('.', 'nvarchar(300)') name
    into #columns
    from @xColumns.nodes('/columns/name') T1(ColumnNames)
    raiserror('inserting @columns', 10, 1) with nowait
    insert @columns
    select ColumnNames.value('.', 'nvarchar(300)') name
    from @xColumns.nodes('/columns/name') T1(ColumnNames)

    Thanks a bunch!!

    2 Solutions collect form web for “Why insert-select to variable table from XML variable so slow?”

    This is a bug in SQL Server 2008.

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

    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

    alt text

    Plan 2

    alt text

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.