Speeding Up your Queries with XPath

Finally I'm posting again, the time has been very tough with me... but I'm here again with one subject that I was thinking to post long time ago.

This is something that I found really curious when I was helping someone, this person asked me why in certain part of an stored procedure the programmer used the text() function to improve its execution time.

Actually, I didn't have idea at that moment why that function gave that kind of benefit, I mean, we had this:

CREATE PROCEDURE [dbo].[SP_Test]

@xml xml

AS

Insert   Into dbo.temp_table

Select   Id1 =          D.Item.value('Id1[1]','bigint'),

         Id2 =          D.Item.value('Id2[1]','int'),

         Text1 =        D.Item.value('Text1[1]','varchar(50)'),

         Text2 =        D.Item.value('Text2[1]','varchar(50)'),

         Id3 =          D.Item.value('Id3[1]','int'),

         Text3 =        D.Item.value('Text3[1]','int'),

         Text4 =        D.Item.value('Text4[1]','int'),

         Text5 =        D.Item.value('Text5[1]','int'),

         Id4 =          D.Item.value('Id4[1]','varchar(10)')    

From     @xml.nodes('/Node1/Node2') as D(Item)

And this procedure was changed to this:

CREATE PROCEDURE [dbo].[SP_Test]

@xml xml

AS

Insert   Into dbo.temp_table

Select   Id1 =          D.Item.value('(Id1/text())[1]','bigint'),

         Id2 =          D.Item.value('(Id2/text())[1]','int'),

         Text1 =        D.Item.value('(Text1/text())[1]','varchar(50)'),

         Text2 =        D.Item.value('(Text2/text())[1]','varchar(50)'),

         Id3 =          D.Item.value('(Id3/text())[1]','int'),

         Text3 =        D.Item.value('(Text3/text())[1]','int'),

         Text4 =        D.Item.value('(Text4/text())[1]','int'),

         Text5 =        D.Item.value('(Text5/text())[1]','int'),

         Id4 =          D.Item.value('(Id4/text())[1]','varchar(10)')    

From     @xml.nodes('/Node1/Node2') as D(Item)

What did we get using text() function? Well, we got an execution time of 5 seconds instead of the original 2 minutes and 23 seconds for getting text values from XML. Pretty cool right?.

Doing some research I realized that using text() function is also using XPath instead of using the UDX (extended operator) query functions that Microsoft SQL Server has. If you look at the execution plan of the query you will see that not using XPath functions makes you use more UDX functions that could hit considerably the performance.