locked
xml shredding .query slow performance RRS feed

  • Question

  • Hi,

    I have a problem with performance when shredding parts of XML from XML. (the .query syntax)

    I'm shredding around 100,000 large XMLs and inserting to a new table.

    I have no problem shredding using .value (about 30 seconds for 100,000 XMLs)

    e.g

    insert into table1
    select
    XMLcolumn.value('header_tag[1]/@field1[1]','int') as field1
    ,XMLcolumn.value('header_tag[1]/@field2[1]','datetime') as field2
    FROM table2

    however as soon as I add the .query (in bold below) the performance is extremly slow.

    insert into table1
    select
    XMLcolumn.value('header_tag[1]/@field1[1]','int') as field1
    ,XMLcolumn.value('header_tag[1]/@field2[1]','datetime') as field2
    ,XMLcolumn.query('header_tag/second_tag') as XMLcolumn2
    FROM table2

    Any Ideas?

    Wednesday, March 20, 2013 12:04 PM

Answers

  • OK, this was the fastest one I've got today at ~35seconds:

    -- Clear plan cache and buffer pool DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO truncate table table1 insert into table1 ( field1, field2, XMLColumn2 ) select ht.c.value('(@field1)[1]','int') as field1 ,ht.c.value('(@field2)[1]','datetime') as field2 ,st.c.query('.') as XMLColumn2 FROM table2 t CROSS APPLY t.XMLcolumn.nodes('(header_tag)[1]') ht(c) CROSS APPLY ht.c.nodes('(second_tag)[1]') st(c)

    --option ( querytraceon 8649 ) -- force parallel plan


    I might have a look at making the xml typed ( ie adding an xml schema collection ) and xml indexing if I get time.
    • Edited by wBobEditor Saturday, March 23, 2013 10:06 AM
    • Marked as answer by Iric Wen Thursday, March 28, 2013 8:56 AM
    Friday, March 22, 2013 7:36 PM
    Answerer
  • There are Selective XML Indexes but in SQL 2012 only.
    • Marked as answer by Iric Wen Thursday, March 28, 2013 8:56 AM
    Saturday, March 23, 2013 3:07 PM
    Answerer

All replies

  • XML Indexes can transform performance, so might be worth a try.  But if you think about it, .query returns XML so potentially could be rather large.  Would this extra column write a lot of data?

    What version of SQL Server are you using?

    Wednesday, March 20, 2013 1:33 PM
    Answerer
  • XMLs are roughly 20kb (* 100,000 XMLs)

    I'm using 2008

    Wednesday, March 20, 2013 1:47 PM
  • Maybe restructuring your query slightly might help - it did in my simple test rig.

    This query is semantically slightly different to yours - I'm assuming you only have one header_tag.  Try this:

    insert into table1 ( field1, field2, XMLColumn2 )
    select 
    	 ht.c.value('@field1','int') as field1
    	,ht.c.value('@field2','datetime') as field2 
    	,ht.c.query('second_tag') as  XMLColumn2
    FROM table2 t
    	CROSS APPLY t.XMLcolumn.nodes('header_tag') ht(c)

    Wednesday, March 20, 2013 2:18 PM
    Answerer
  • Hi wBob,

    It was more or less the same time for me. Would OPENXML work here?  I have about 50 nodes

    Thursday, March 21, 2013 4:09 PM
  • Interesting, it halved the time on my tests.   OPENXML can be faster for large pieces of XML, however it can only work with one piece of XML at a time, so you would have to create a cursor to run through your table one at a time.  You could also try the .nodes method with a cursor to see how they compare.  OPENXML also has a well-known feature in that it can allocate 1/8th of the total server memory when processing any piece of xml.  I tend to avoid it for this reason, even if I can get a few ms difference on a query.

    Can you tell me a bit more about your actual INSERT?  Are you inserting into a normal table, temp table or table variable?  There was a bug in SQL 2008 regarding INSERT performance into table variables so it would be good to rule out.

    Thursday, March 21, 2013 4:20 PM
    Answerer
  • I'm inserting to a normal table with an identity as a PK. When I use the .query the query becomes single threaded, with only .value the query is multi. Any idea why that is?
    Friday, March 22, 2013 11:45 AM
  • I can't reproduce this on my system.  With my test rig, I get serial plans, although was able to induce a parallel plan with a trace flag (I wouldn't recommend doing this in production).  Simply using the .query method does not prevent parallelism as far as I can tell.

    It may be worth updating statistics and checking fragmentation on the tables involved.  Can you also confirm your SQL Server version number?  eg SELECT @@version

    Here is my test rig;  it sets up 100,000 rows of sample data in tempdb, with XML of 15-30KB.  Try working through it and see if you can work out what's different to your setup:

    WARNING!  DO NOT RUN THIS SCRIPT ON A PRODUCTION SYSTEM AS IT USES DBCC DROPCLEANBUFFERS AND FREEPROCCACHE.

    USE tempdb
    GO
    
    SET NOCOUNT ON
    GO
    
    IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
    CREATE TABLE table1
    (
    	field1		INT,
    	field2		DATETIME,
    	XMLColumn2	XML
    )
    GO
    
    IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
    CREATE TABLE table2
    (
    	rowId	INT IDENTITY PRIMARY KEY,
    	XMLColumn XML
    )
    GO
    
    INSERT INTO table2 ( XMLColumn )
    SELECT *
    FROM
    	(
    	SELECT 
    		CAST( RAND() * 10000 AS INT ) AS "@field1", 
    		DATEADD( day, 40000, RAND() * 1000 ) AS "@field2",
    		( 
    		SELECT name, database_id, source_database_id, create_date, compatibility_level, collation_name, user_access, user_access_desc, is_read_only, is_auto_close_on, is_auto_shrink_on, state, state_desc, is_in_standby, is_cleanly_shutdown, is_supplemental_logging_enabled, snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model, recovery_model_desc, page_verify_option, page_verify_option_desc, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on, is_ansi_null_default_on, is_ansi_nulls_on, is_ansi_padding_on, is_ansi_warnings_on, is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on, is_quoted_identifier_on, is_recursive_triggers_on, is_cursor_close_on_commit_on, is_local_cursor_default, is_fulltext_enabled, is_trustworthy_on, is_db_chaining_on, is_parameterization_forced, is_master_key_encrypted_by_server, is_published, is_subscribed, is_merge_published, is_distributor, is_sync_with_backup, service_broker_guid, is_broker_enabled, log_reuse_wait, log_reuse_wait_desc, is_date_correlation_on 
    		FROM sys.databases FOR XML RAW, TYPE 
    		) AS "second_tag"
    	FOR XML PATH('header_tag'), TYPE
    	) x(y)
    GO 100000
    
    
    
    ------------------------------------------------------------------------------------------------
    -- Test Block 01 START
    -- Original query
    ------------------------------------------------------------------------------------------------
    
    -- Clear plan cache and buffer pool
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    GO
    
    truncate table table1
    
    insert into table1 ( field1, field2 )
    select 
    	 XMLcolumn.value('header_tag[1]/@field1[1]','int') as field1
    	,XMLcolumn.value('header_tag[1]/@field2[1]','datetime') as field2 
    FROM table2
    go
    
    -- Test Block 01 END
    ------------------------------------------------------------------------------------------------
    
    
    ------------------------------------------------------------------------------------------------
    -- Test Block 02 START
    ------------------------------------------------------------------------------------------------
    
    -- Clear plan cache and buffer pool
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    GO
    
    truncate table table1
    
    insert into table1 ( field1, field2 )
    select 
    	 ht.c.value('@field1','int') as field1
    	,ht.c.value('@field2','datetime') as field2 
    FROM table2 t
    	CROSS APPLY t.XMLcolumn.nodes('header_tag') ht(c)
    go
    
    -- Test Block 02 END
    ------------------------------------------------------------------------------------------------
    
    
    ------------------------------------------------------------------------------------------------
    -- Test Block 03 START
    ------------------------------------------------------------------------------------------------
    
    -- Clear plan cache and buffer pool
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    GO
    
    truncate table table1
    
    insert into table1 ( field1, field2, XMLColumn2 )
    select 
    	 ht.c.value('@field1','int') as field1
    	,ht.c.value('@field2','datetime') as field2 
    	,ht.c.query('second_tag') as  XMLColumn2
    FROM table2 t
    	CROSS APPLY t.XMLcolumn.nodes('header_tag') ht(c)
    --option ( querytraceon 8649 )	-- force parallel plan
    go
    
    -- Test Block 03 END
    ------------------------------------------------------------------------------------------------

    • Edited by wBobEditor Friday, March 22, 2013 3:45 PM
    Friday, March 22, 2013 1:04 PM
    Answerer
  • very interesting

    test1=1.40

    test2=1.03

    test3=1.02

    test3 with querytraceon=29sec

    let me reproduce this test with my full code and I'll get back to you... thanks!!

    Friday, March 22, 2013 1:50 PM
  • You might want to double-check you cleared the cache ( ie ran the DBCC statements ) before re-running test3 otherwise you might have a false result.  I can't reproduce that on my 8-core system - the parallel plan runs in about the same time as the serial.

    I'm testing on version number Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64).  What's yours?

    As mentioned, I would not recommend using the trace flag in a production environment.  We're simply using it here to diagnose a problem.

    Friday, March 22, 2013 2:18 PM
    Answerer
  • version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

    actually my test results were incorrect. test 2 and 3 were much slower than test 1 :(. in the code you provided 'header_tag' needed to be replaced with only 'header'

    CROSS APPLY t.XMLcolumn.nodes('header_tag'=CROSS APPLY t.XMLcolumn.nodes('header')

    Friday, March 22, 2013 2:43 PM
  • So that's a very old version of SQL 2008 - the RTM.  SQL 2008 is up to Service Pack 3 (SP3) now, plus several cumulative updates.  As best practice you should try and keep up-to-date with service packs. 

    This performance issue could be to do with the older version.

    I will fix up my test rig and come back to you.

    Friday, March 22, 2013 3:08 PM
    Answerer
  • OK, this was the fastest one I've got today at ~35seconds:

    -- Clear plan cache and buffer pool DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO truncate table table1 insert into table1 ( field1, field2, XMLColumn2 ) select ht.c.value('(@field1)[1]','int') as field1 ,ht.c.value('(@field2)[1]','datetime') as field2 ,st.c.query('.') as XMLColumn2 FROM table2 t CROSS APPLY t.XMLcolumn.nodes('(header_tag)[1]') ht(c) CROSS APPLY ht.c.nodes('(second_tag)[1]') st(c)

    --option ( querytraceon 8649 ) -- force parallel plan


    I might have a look at making the xml typed ( ie adding an xml schema collection ) and xml indexing if I get time.
    • Edited by wBobEditor Saturday, March 23, 2013 10:06 AM
    • Marked as answer by Iric Wen Thursday, March 28, 2013 8:56 AM
    Friday, March 22, 2013 7:36 PM
    Answerer
  • Fastest for me as well. I had to remove the [1] from the second cross apply because there can be more than one 'second_tag'. This slows it down a bit, but still faster(40%) than test 3. Will try with my XMLs on Monday

    I had a brief look at indexing, but more or less had the same results. While the select and insert was more efficient, updating the index took time.

    Is it possible to have a filtered index on XML?

    Saturday, March 23, 2013 2:50 PM
  • There are Selective XML Indexes but in SQL 2012 only.
    • Marked as answer by Iric Wen Thursday, March 28, 2013 8:56 AM
    Saturday, March 23, 2013 3:07 PM
    Answerer