OPENXML vs nodes()

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 27, 2012 at 9:33 am

  #263057

  Hi,

  In our invoice SP we pass the data as XML to send all the rows at once to the database.

  To go through all the rows what's the best to use: OPENXML or nodes()?

  I read some articles about the 1/8 memory used by sp_xml_preparedocument and to use ASAP sp_xml_removedocument.

  Also OPENXML is better with long XML data and nodes for smaller one.

  In any case if the XML data will be used more than once it should be stored in a table variable...

  I created some test data to analyze the performance:

  DECLARE @x XML = '<invoice><details><detail><product>1</product><qnt>3</qnt></detail><detail><product>2</product><qnt>2</qnt></detail><detail><product>4</product><qnt>3</qnt></detail><detail><product>10</product><qnt>3</qnt></detail><detail><product>21</product><qnt>3</qnt></detail><detail><product>31</product><qnt>3</qnt></detail><detail><product>33</product><qnt>3</qnt></detail></details></invoice>'

  DECLARE @hnd INT

  EXEC sp_xml_preparedocument @hnd OUTPUT, @x

  INSERT INTO xmlTest SELECT ProductId, Quantity FROM OPENXML(@hnd, '/invoice/details/detail', 2) WITH (ProductId INT 'product', Quantity FLOAT 'qnt')

  EXEC sp_xml_removedocument @hnd

  GO

  DECLARE @x XML = '<invoice><details><detail><product>1</product><qnt>3</qnt></detail><detail><product>2</product><qnt>2</qnt></detail><detail><product>4</product><qnt>3</qnt></detail><detail><product>10</product><qnt>3</qnt></detail><detail><product>21</product><qnt>3</qnt></detail><detail><product>31</product><qnt>3</qnt></detail><detail><product>33</product><qnt>3</qnt></detail></details></invoice>'

  INSERT INTO xmlTest SELECT t.c.value('product[1]', 'INT') AS ProductId, t.c.value('qnt[1]', 'INT') AS Quantity FROM @x.nodes('/invoice/details/detail') t(c)

  When running these two queries the Execution Plan says OPENXML takes 9% and nodes 91% of total time.

  The statistics IO:

  Table 'xmlTest'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Worktable'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (7 row(s) affected)

  Table 'xmlTest'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Worktable'. Scan count 2, logical reads 180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (7 row(s) affected)

  Apparently nodes is slower and has more reads...

  I then, to test in a multi thread environment, used SQLQueryStress at http://www.datamanipulation.net/sqlquerystress/sqlquerystressdownload.asp.

  I ran 10 iterations with 4 threads...

  The 1st two times they both took the same time (11.6s, 12.8s)... But after that nodes took 7.8s and OPENXML kept taking 10 to 11sec.

  My machine isn't a dedicated SQL Server, has other services and apps running (it's a developer's machine) and so I'm not sure these times are rigged...

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  September 27, 2012 at 2:58 pm

  #1543113

  Generally speaking I have found in the past that OPENXML is faster if you need all the XML. XQuery is faster when searching for a value within the XML (assuming proper XML indexes).

  
  
  A.J.
  DBA with an attitude

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 27, 2012 at 3:23 pm

  #1543126

  A.J. Wilbur (9/27/2012)

  ---

  Generally speaking I have found in the past that OPENXML is faster if you need all the XML. XQuery is faster when searching for a value within the XML (assuming proper XML indexes).

  No searching... just "transforming" the XML into a table to process the details' data (in this case the invoice's lines).

  OPENXML can be faster, in some case, but what about the "myth" (or not) about the 1/8 of memory used?!

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 27, 2012 at 6:40 pm

  #1543154

  I tried this simple test - on SQL 2012 Xquery wins - interesting to see if 2008 is the same.

  -- Prepare some bigger XML data

  DECLARE @xml XML

  DECLARE @detail VARCHAR(4000) = '<detail><product>1</product><qnt>3</qnt></detail><detail><product>2</product><qnt>2</qnt></detail><detail><product>4</product><qnt>3</qnt></detail><detail><product>10</product><qnt>3</qnt></detail><detail><product>21</product><qnt>3</qnt></detail><detail><product>31</product><qnt>3</qnt></detail><detail><product>33</product><qnt>3</qnt></detail>'

  DECLARE @details VARCHAR(MAX) = ''

  SELECT @details = @detail + @details

  FROM master..spt_values

  WHERE spt_values.type='P'

  SET @xml = '<invoice><details>' + @details + '</details></invoice>'

  -- Use a dump variable to prevent returning data to the client

  DECLARE @dump INT;

  -- First test OpenXml

  PRINT 'OPENXML'

  PRINT '======='

  SET STATISTICS IO ON ;

  SET STATISTICS TIME ON;

  DECLARE @hnd INT

  EXEC sp_xml_preparedocument @hnd OUTPUT, @xml

  SELECT @dump=ProductId, @dump=Quantity FROM OPENXML(@hnd, '/invoice/details/detail', 2) WITH (ProductId INT 'product', Quantity FLOAT 'qnt')

  EXEC sp_xml_removedocument @hnd

  SET STATISTICS TIME OFF;

  SET STATISTICS IO OFF;

  -- Now XQuery with /text() which tends to be quicker

  PRINT 'XQuery With /text()'

  PRINT '==================='

  SET STATISTICS IO ON ;

  SET STATISTICS TIME ON;

  SELECT @dump=t.c.value('(product/text())[1]', 'INT') , @dump=t.c.value('(qnt/text())[1]', 'INT') FROM @xml.nodes('/invoice/details/detail') t(c)

  SET STATISTICS TIME OFF;

  SET STATISTICS IO OFF;

  -- And finally XQuery without /text() - should be slower

  PRINT 'XQuery'

  PRINT '======'

  SET STATISTICS IO ON ;

  SET STATISTICS TIME ON;

  SELECT @dump=t.c.value('product[1]', 'INT') , @dump=t.c.value('qnt[1]', 'INT') FROM @xml.nodes('/invoice/details/detail') t(c)

  SET STATISTICS TIME OFF;

  SET STATISTICS IO OFF;

  My results:

  OPENXML

  =======

  SQL Server Execution Times:

  CPU time = 172 ms, elapsed time = 167 ms.

  SQL Server Execution Times:

  CPU time = 483 ms, elapsed time = 490 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  XQuery With /text()

  ===================

  SQL Server Execution Times:

  CPU time = 203 ms, elapsed time = 200 ms.

  XQuery

  ======

  SQL Server Execution Times:

  CPU time = 375 ms, elapsed time = 375 ms.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]