Which one is optimised Query to read XML node ?

Question

To read the follwoing XML i have tried with 4 samples . But when i look at Execution Plan Method 1 is taking 0%? does that mean OPENXML is an optimised one ? pls clarify my doubt ?

DECLARE @foo XML 
SELECT @foo = 
'<harrys>
  <harry>
    <fish>0.015000000000</fish>
    <bicycle>2008-10-31T00:00:00+01:00</bicycle>
    <foo>ü</foo>
  </harry>
  <harry>
    <fish>0.025000000000</fish>
    <bicycle>2008-08-31T00:00:00+01:00</bicycle>
    <foo>ä</foo>
  </harry>
</harrys>'
-- Method 1 
DECLARE @v_DOC INT  
EXEC SP_XML_PREPAREDOCUMENT @v_DOC OUTPUT, @foo  
SELECT * 
FROM   OPENXML (@v_DOC, '/harrys/harry', 2) 
WITH (  ProviderID VARCHAR(30) 'fish',  
       Priority VARCHAR(100)  'bicycle',  
       TransactionID VARCHAR(200) 'foo'    ) AS a  

EXEC SP_XML_REMOVEDOCUMENT @v_DOC  

-- Method 2
SELECT
CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
CAST(y.item.query('data(foo)') AS varchar(25)) FROM
@foo.nodes('/*') x(item)
CROSS APPLY
x.item.nodes('./*') AS y(item)  

-- Method 3
SELECT
CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
CAST(x.item.query('data(foo)') AS varchar(25)) FROM
@foo.nodes('harrys/harry') x(item)  

-- Method 4
SELECT
CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
CAST(y.item.query('data(foo)') AS varchar(25)) FROM
@foo.nodes('/harrys') x(item)
CROSS APPLY
x.item.nodes('./harry') AS y(item)

Answer 1

0

Thanks all for the reply .. Will Check this out with Server for performance.

answered Jan 18 '12 at 09:58 AM

Rajasekar
41 ● 1 ● 1 ● 1

How did you get on with that?

Jan 28 '12 at 03:39 AM SQL Kiwi

(comments are locked)

Answer 2

Using the nodes method does have the advantage that you don't need to remember to clean up a document handle afterward. Good estimates are almost impossible when XML methods are used, so do not draw any conclusions from these. More generally, estimated costs in query plans are mainly useful internally to the optimizer, and are not intended to be compared between different queries. The fact that SSMS provides a percentage estimated batch cost is extremely misleading to users. OPENXML and nodes() are documented as having very similar performance overall.

As far as the specific query is concerned, I find the following form to be more efficient than any of the four in the question:

SELECT 
    harry.node.value('(./fish/text())[1]', 'varchar(30)'),
    harry.node.value('(./bicycle/text())[1]', 'char(25)'),
    harry.node.value('(./foo/text())[1]', 'varchar(25)')
FROM @foo.nodes('/harrys/harry') AS harry(node)

Answer 3

I totally agree with @Grant Fritchey. Most of the time XQUERY methods especially with filters do offer more optimal performance. In your case, the actual costs in the execution plan with OPENXML are hidden since it involves an outside reference MSXML.dll. Moreover, the cost of preparing and removing the document in case of OPENXML is not reflected there as well, but such kind of cost would have been added for rest of the methods. OPENXML is quite Memory intensive and XQUERY methods tends to be more processor intensive in some cases. So the all popular solution "it depends" would fit here again.

But one major drawback with OPENXML is if you have to traverse the table with XML fields, you are left with no other choice but to do it row by row.

Answer 4

0

Those costs are estimated costs, so you can't use them as measures of performance. Instead, monitor the server using extended events (or trace if you're on 2000) and capture the execution of the queries so you can compare I/O, CPU, and execution time (although execution time is the most variable of the measures, so in order for it to be right you need to take it several times and use the average). That will tell you which is better.

In general, the XQuery methods of @foo.nodes is slightly better for performance than OPENXML, but only slightly and not always. That's what I've seen in my performance and with my testing.

answered Jan 18 '12 at 03:48 AM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

+1 from here

Jan 18 '12 at 05:06 AM Usman Butt

+1 for the scary DBA

Jan 18 '12 at 05:27 AM SQL Kiwi

Ha! And here I gave you a +1.

Jan 18 '12 at 06:27 AM Grant Fritchey ♦♦

(comments are locked)

Which one is optimised Query to read XML node ?

Follow this question

Related Questions