Thursday, December 29, 2011

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.

 

2 comments:

  1. Hola,
    una recomendación sobre el nombre del procedure:
    Es mejor evitar poner SP_ como prefijo ya que el SQL Server lo considera como un elemento del sistema y como tal la performance de su ejecución puede variar.

    Saludos!

    ReplyDelete
    Replies
    1. Gracias! lo tomaré en cuenta la próxima vez =)

      Delete