4

I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an INSERT INTO is taking like 20 minutes while doing a SELECT INTO is taking seconds. The DB statistics are up to date and we are running daily maintenance plans so not sure what is going on. I know that SELECT INTO bypasses the logs which would make it faster, but I as not assuming that the INSERT INTO would be so much worse.

EDIT: Based on comments adding some more details

Why would the INSERT INTO be taking so much longer than the SELECT INTO for a temp table.

Query example below. I am shredding XML and inserting into a temp Table I have both the INSERT INTO and SELECT INTO commented out. The INSERT INTO for just 100 records is showing 382,000+ logical reads while the SELECT INTO is showing none.

;with cteScen AS (
SELECT    DENSE_RANK() OVER ( ORDER BY scenario_node ) AS scenario ,
        RANK() OVER ( PARTITION BY scenario_node ORDER BY scen_val ) AS level_order ,
        scen_val.value('text-value[1]',
                       'varchar(100)') AS 'scen_value' ,
        scen_val.value('(distribution/@id)[1]',
                       'int') AS 'dist'
FROM      @scenarioXML.nodes('/scenarios/scenario')
        AS scenario ( scenario_node )
        CROSS APPLY scenario.scenario_node.nodes('./values/value')
        AS x ( scen_val ))
--INSERT INTO #tmpTbl(Scenario, Level_Order, Scenario_Value, Distribution_ID)
Select top 100 *
--INTO #tmpTbl
From cteScen

Any thoughts?

  • 4
    This all works much better if you actually ask a question. – RBarryYoung Apr 25 '13 at 3:29
  • 2
    "Any thoughts?" Yes, but you need to show the queries you have. Without that there will be too much of guessing. – Mikael Eriksson Apr 25 '13 at 3:34
  • 4
    Actual Execution plans for both cases as well please. – StrayCatDBA Apr 25 '13 at 3:49
  • 1
    Thanks for the update. My guess would have been that your XML source was not of XML type but that you cast to XML in your query. That can in some circumstances lead to very bad performance due to a deferred compute scalar in the query plan. Clearly it is not so here. – Mikael Eriksson Apr 25 '13 at 3:58
  • Thanks @MikaelEriksson I am just really confused as to why it is taking so much longer for the INSERT INTO. I would expect more of a hit because of the logging, but not minutes opposed to seconds. – scarpacci Apr 25 '13 at 4:08
5

There are a couple of connect items that might be relevant here.

An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1
Poor xml performance with table variable
Bad performance when inserting data from element-centric documents
INSERT from .nodes with element-based XML has poor performance on SP2 with x64

I have not been able to reproduce what you see with your query so I can not say for sure what is happening for you but one issue was that there was an eager spool step in the query plan that was not needed. That eager spool can be removed by using a trace flag in the first fixed versions of SQL Server. I am not sure if that is needed in later versions. The eager spool was in there "due to a general halloween protection logic". That might explain why you see the bad performance when you insert to an already existing table. If you have a look at the query plans for both your queries you should see the difference if that is the case.

There are also some workarounds suggested that you can try and I think you should try to specify the text node when you fetch text-value.

scen_val.value('(text-value/text())[1]', 'varchar(100)') AS 'scen_value' ,

If you don't do that SQL Server will aggregate all text values below text-value. For your XML you probably don't see a difference in the result but if text-value was a mixed content node with both text and sub nodes you would.

declare @XML xml = '
<root>
  <item>item text <subitem>sub item text</subitem>
  </item>
</root>'

select @XML.value('(/root/item)[1]', 'varchar(50)') as C1,
       @XML.value('(/root/item/text())[1]', 'varchar(50)') as C2

Result:

C1                           C2
---------------------------- ----------------------------
item text sub item text      item text 
| improve this answer | |
-1

You should change your insertion to INSERT INTO #tmpTbl WITH (TABLOCK) .

In my experience, insert into #temptable with(tablock) nearly always has roughly the same performance as select ... into #temptable, whereas omitting with(tablock) often results in worse performance.

Probably the difference here is that the table lock allows the insert into to use a bulk-logged inserts. It's also possible that the SQL engine is somehow treating the insertion differently when there is a table lock (e.g., making different parallelization decisions).

| improve this answer | |
  • Note that I don't think the parallelization difference I mentioned applies to SQL 2008, but it's probably relevant in some of the newer editions. – Brian Nov 28 '18 at 14:25
-5

Use OPTION ( OPTIMIZE FOR ( '@Your_XML_Variable' = NULL ) )

It will avoid compilation of new execution plans, thus making your query faster.

Read here: http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.