Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a 400k XML document trying to insert into an 86-column SQL Server 2008 table. It takes about 35 minutes to get that XML into a temp table using this code:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
    Select 
     xItem.value('declare namespace xkj="urn:xpaykj-report-xml-1.0"; (xkj:prop[@idx=1])   [1]   ','Date')  as [InputDate]                                                  
    -- and then 85 other columns --
Into #loadtemp
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

I need to optimize... Something. But I am not sure what. Can anyone help me with ideas?

Thanks.

share|improve this question

migrated from stackoverflow.com Dec 21 '11 at 21:14

This question came from our site for professional and enthusiast programmers.

1  
Could you provide some sample XML that we can use to test on? –  Mikael Eriksson Dec 21 '11 at 17:19
    
Could you also include the query plan? Hard to tell what's wrong without seeing what SQL Server is trying to do. –  Anon246 Dec 21 '11 at 21:35

3 Answers 3

Can you stage it twice? First, shred the data into rows of XML, and then read the columns from the rows. Something like:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj) 
    Select  
     xItem.query('.')  as RowXML
Into #loadtemp 
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem) 

should give you rows of XML; then apply your value statements to shred the table into columns.

share|improve this answer

Despite the answer depending on what SQL is doing, I've always used this whitepaper to tune my XML XPath in SQL Server with very good results. Some of the techniques have yielded 100's - 1000's of times speed improvements using some of the techniques. I highly recommend it for tuning cases like these:

http://msdn.microsoft.com/library/ms345118

Thanks, Eric

share|improve this answer

Is this a regular import? Try Integration Services. Its a bit different than T-SQL but, There XML Source should be quick.

  1. A new SSIS project in SSDT
  2. add a "file Connection manager" to represent the xml doc
  3. add a "OLE Connection manager" to represent the destination database
  4. Add a dataflow in the task pane
    1. XML Source component to bring the data into the pipeline
    2. Connect the correct source output to a OLE database destination

other additional steps that might be needed:

  1. variable source file name
  2. use an XML Task to run an XSLT to simplify the document, to filter the doc down to.

    <root>
      <properties ... />
    ...
    </root>
    

If You are uncomfortable with SSIS I would definitely tune, the article linked by Anon246 above, brought me massive improvements to we saw improvements there.

share|improve this answer
1  
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Colin 't Hart Apr 23 at 18:38
    
The how to answer page dose include "or a viable alternative". Should I have stated the alternative more clearly? SSIS is a posible option but, only worth it if the import happens often. Should I have left off the my concurring opinion in general? Or, sperated to comment on the prior answer? I would like to answer an more useful way. –  Matt Apr 23 at 19:25
    
@Matt a more thorough answer would include a synopsis of how SSIS could be used for this, and if you refer to another user's answer being specific (i.e. "The section on optimizing for XML indexes was extremely useful") would improve the quality of the answer. –  JNK Apr 23 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.