locked
XML vs Inner Join RRS feed

  • Question

  •  

    Hi,

     

    Before starting some profiling tests I would like to know wich is the most performant :

    • Querying a table that contains XML columns with xquey
    • Querying  a table using some inner joins on tables that contais varchars

     

    Regards,

     

    Wednesday, July 7, 2010 2:20 PM

Answers

  • These are not really an apples to apples comparison.  I believe you are ultimately trying to decide if you should parse out the XML elements or leave them in XML format.  This is a very gray area because a lot of factors come into play.  from my experience and what I have read it depends on the size of the XML and the number of rows returned by your query.  In some cases it will be a lot more benefical to shred the elements into columns that are indexed oppose to looking through an entire XML document (which can be up to 2 GB per row).

    Test each method and see which works better for your environment.  With all of that said, I have seen better results in shredding the elements into columns and using the relational engine to do its job... after all this is what SQL Server is made to do.


    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Monday, July 12, 2010 4:00 AM
    • Marked as answer by Link.fr Monday, July 12, 2010 11:13 AM
    Wednesday, July 7, 2010 5:35 PM
  • >>Querying a table that contains XML columns with xquey
    >>Querying  a table using some inner joins on tables that contais varchars

    Generally, inner join would perform faster than XQuery in my opinion.  

    But again it depends on many factors including indexes you have on your varchar columns as well as whether you have XML indexes for your XML column.

    You will really have to test this with your data and compare the execution plan generated by query optimizer for both the cases.

    • Edited by Chirag Shah Wednesday, July 7, 2010 2:42 PM content
    • Marked as answer by Link.fr Monday, July 12, 2010 11:13 AM
    Wednesday, July 7, 2010 2:26 PM

All replies

  • >>Querying a table that contains XML columns with xquey
    >>Querying  a table using some inner joins on tables that contais varchars

    Generally, inner join would perform faster than XQuery in my opinion.  

    But again it depends on many factors including indexes you have on your varchar columns as well as whether you have XML indexes for your XML column.

    You will really have to test this with your data and compare the execution plan generated by query optimizer for both the cases.

    • Edited by Chirag Shah Wednesday, July 7, 2010 2:42 PM content
    • Marked as answer by Link.fr Monday, July 12, 2010 11:13 AM
    Wednesday, July 7, 2010 2:26 PM
  • These are not really an apples to apples comparison.  I believe you are ultimately trying to decide if you should parse out the XML elements or leave them in XML format.  This is a very gray area because a lot of factors come into play.  from my experience and what I have read it depends on the size of the XML and the number of rows returned by your query.  In some cases it will be a lot more benefical to shred the elements into columns that are indexed oppose to looking through an entire XML document (which can be up to 2 GB per row).

    Test each method and see which works better for your environment.  With all of that said, I have seen better results in shredding the elements into columns and using the relational engine to do its job... after all this is what SQL Server is made to do.


    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Monday, July 12, 2010 4:00 AM
    • Marked as answer by Link.fr Monday, July 12, 2010 11:13 AM
    Wednesday, July 7, 2010 5:35 PM
  • After profiling tests, Inner joins are faster.

     

    Thanks,

    Monday, July 12, 2010 11:13 AM