x

Which one is optimised Query to read XML node ?

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) 
more ▼

asked Jan 18, 2012 at 01:48 AM in Default

Rajasekar gravatar image

Rajasekar
41 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest
Thanks all for the reply .. Will Check this out with Server for performance.
more ▼

answered Jan 18, 2012 at 09:58 AM

Rajasekar gravatar image

Rajasekar
41 1 1 1

How did you get on with that?
Jan 28, 2012 at 03:39 AM SQL Kiwi
(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Jan 18, 2012 at 05:20 AM

SQL Kiwi gravatar image

SQL Kiwi
1.1k 1 4

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jan 18, 2012 at 05:05 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jan 18, 2012 at 03:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 from here
Jan 18, 2012 at 05:06 AM Usman Butt
+1 for the scary DBA
Jan 18, 2012 at 05:27 AM SQL Kiwi
Ha! And here I gave you a +1.
Jan 18, 2012 at 06:27 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150

asked: Jan 18, 2012 at 01:48 AM

Seen: 3399 times

Last Updated: May 17, 2013 at 02:04 AM