Slow XML shredding in SQL

obenoben

New Member
I have a stored procedure that takes a collection of "Survey" objects and shreds them into their appropriate tables.The "Survey" object has two child objects, "Erosions" and "Spans".Here is an example of the XML I might use:\[code\]<Surveys> <Survey> <SurveyID>35</SurveyID> <CulvertID>5</CulvertID> <PRNo>587180</PRNo> <MP>5.243</MP> <RefMiles>1.500</RefMiles> <PtID>ABCGEFGH</PtID> <FWVersion>10</FWVersion> <SurveyDate>2012-08-27</SurveyDate> <FutureComments>7c36fe43-78cb-436e-81cf-f416aa63c8fc</FutureComments> <Erosions> <Erosion> <ID>160</ID> <SurveyID>35</SurveyID> <Location>Test Erosion - 8/27/2012 - 1:19:04 PM</Location> </Erosion> <Erosion> <ID>161</ID> <SurveyID>35</SurveyID> <Location>Test Erosion - 8/27/2012 - 1:19:04 PM</Location> </Erosion> </Erosions> <Spans> <Span> <ID>88</ID> <SurveyID>35</SurveyID> <Material>Test Span - 8/27/2012 - 1:19:04 PM</Material> </Span> <Span> <ID>89</ID> <SurveyID>35</SurveyID> <Material>Test Span - 8/27/2012 - 1:19:04 PM</Material> </Span> </Spans> </Survey></Surveys>\[/code\]I've uploaded the stored procedure definition to the following location (too big to paste here). Download SQLWhen I send in the XML sample provided above, the procedure takes about 10 seconds to complete, and that is with only a very small XML string. It can take minutes to update/insert a record that includes values in any of the "VARBINARY(MAX)" columns.I am truly an Xquery newbie... not sure what I've done wrong, but the performance is absolutely terrible. Any suggestions?
 
Back
Top