performance of EXPLICIT vs PATH - 11-30-2009 , 07:54 AM
has anyone noticed any performance benefits from changing FOR XML EXPLICIT SELECTs to FOR XML PATH(...) SELECTs? I have
some large EXPLICIT SELECTs that were written during the sql2000 days and haven't been changed for the new XML features.
I only want to spend the time on this if there is the chance of performance benefits.
RE: performance of EXPLICIT vs PATH - 11-30-2009 , 09:51 AM
I'm not aware of any specific performance benefits with FOR XML PATH, apart
from the gains in maintainability, due to the queries being a good deal
I've refactored a fair few on the forums changing 30, 50 or 100 line FOR XML
EXPLICITs to 5 or 10 line FOR XML PATH statements, eg
plus there's other examples:
Quote from Ward Pond's blog:
"...my team recently rewrote one 5000-line SQL Server 2000 user-defined
function (which was basically a 43-level FOR XML EXPLICIT query) in 497 lines
using this new syntax..."
So you could say "development peformance" is improved, ie developers won't
have to spend hours unpicking complex FOR XML EXPLICIT statement and
understanding the universal table, which would pay for itself fairly quickly.
FOR XML PATH isn't perfect though, eg it can add additional namespaces which
is a bit of a nuisance.
I guess there is a lot to weigh up! The best thing to do is try refactoring
one of you candiate FOR XML EXPLICIT queries and test it with your data.
Assess how how much effort it was to refactor, how complex the query was
before and after.
Let us know how you get on!
"Dan Holmes" wrote: