optimization of XML string query
-
I am a bit new to the use of XML strings in stored procedures and I am having performance issues. My assumption is that my Xquery syntax is not optimal.
I have an XML string of the format
value value value where there are 1->n
elements. This string is being passed to a stored procedure for parsing. I originally used a cursor to parse through XML string to parse out the values of each element. The performance was not optimal and my analyses indicated that the performance issue was related to the use of a cursor. I changed to a simple while loop and performance degraded further.
I believe my performance issue is related to my Xpath syntax. My select is
select @xValue = x.nextValue.value('(./xCoord)[1],'int'),
@yValue = x.nextValue.value('(./yCoord)[1],'int')
from @passXMLstring.nodes('//shapeDef/verticeDef') as x(nextValue)
where x.nextValue.value('(./ident)[1],'int') = @passValue
I feel certain my query statement is not efficient, but I have not found any concise documentation as to how to optimize the syntax.
-
Couple ideas, not being sure what you're trying to do.
If you're doing the statement in a loop, it's probably bad, as the xml.nodes will parse the entire XML set each time to get just the 1 value you're looking for. Can you just extract it into a temp table and work from that?
Alternatively, you can probably do an xml.count() type thing to find the number of nodes, then do a while loop over it and extract each node individually without using the .nodes function.
Sketchy, I know, but it depends on what the rest of the code is trying to do with the values in the XML.
I know the .nodes() can sometimes be expensive to call, so reducing the number of calls to it may solve your problem.
Just my $0.02.
Greg Walker
DBA, ExpenseWatch.com -
Thank you for the feedback.
Yes, the issue is that I am in a loop. I have an XML string that contains the X,Y coordinates of a polygon. You mention extracting each node separately without using the .nodes function. Do you have a code snippet that shows how to address a specific node without the use of the .nodes function?
-
The syntax for these is a painfully awkward, but it works.
You may need to change this slightly based on the actual layout of your XML (I was just making some guesses based on your supplied code).
SELECT @xValue = @passXMLstring.query('/shapeDef/verticeDef[ident=sql:variable("@passValue")]').value('(/verticeDef/xCoord)[1]', 'int')
What it does:
The .query() returns the verticeDef node and all its children for any verticeDef node that has a ident child node with the give value.
The .value() then does what you'd expect.
Should be more efficient that parsing the entire XML into nodes for each trip through the loop.
Inserting the .nodes() output into a working table and killing the loop altogether might be even better, but that depends on what you're doing with the values once you extract them.
Hope this works for you. I know the syntax is brutal (we do this kind of thing a lot, and it gets worse when you start doing .replace() things to modify the XML).
Greg Walker
DBA, ExpenseWatch.com -
Thank you for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply