SQL Server Performance

Help With XML Shredding Performance

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by nzblue_fish, Dec 5, 2010.

  1. nzblue_fish New Member

    First, the obligatory apology ... I'm pretty new when it comes to complex SQL so sorry if my question has an obvious solution.
    I've stepped in to help on a project in trouble that involves loading some very large XML files into an SQL database. I've got started using the SQL 2008 XML function and have managed pretty well so far, but I'm really struggling with poor performance when it comes to shredding elements of the incoming XML documents that contain a large number of subnodes.
    As a starting point, the XML structure looks something like this:
    <Animal>
    <Animl_Key>1234567</Animl_Key>
    <Lactations>
    <data1>aaaaa</data1>
    <data2>bbbbb</data2>
    </Lactations>
    ... more lactation nodes
    <Lactations>

    <data1>yyyyy</data1>
    <data2>zzzzz</data2>
    </Lactations>
    </Animal>
    ... many animals
    <Animal>

    <Animl_Key>12345678</Animl_Key>
    <Lactations>

    .... individual data elements
    </Lactations>
    ...

    <Lactations>

    .... individual data elements
    </Lactations>
    </Animal>
    and I want to end up with a table that looks like this:
    Animl_Key, data1,data2
    1234567,aaaaa,bbbbb
    1234567,yyyyy,zzzzz
    1234568, ... etc ...
    1234568, ... etc ...
    I've succesfully achieved this, but only with some very expensive queries. I need to find the most efficient way and am desperate for some help.
    I have started by loading the whole XML document in to a XML datatype and have created PRIMARY and SECONDARY XML indexes using VALUE and PROPERTY to see if they help.
    Any suggestions would be very welcome as I'm running out of both ideas and time.
    Cheers, Innes (NZ)
  2. nzblue_fish New Member

    Just adding to my earlier question it seems that the performance bottleneck is in fact no the XML shredding process. With a bit of experimentation I've narrowed down the issue to the INSERT INTO statement that is supposed to append the SELECT output from the nodes() query.
    My SELECT
    anode.value(...etc)
    FROM @xmlDoc.nodes(...)
    seems to perform reasonably well, but as soon as I do the INSERT INTO TABLE ... using the XML shredding as the input, performance takes a nosedive.
    Any suggestions given this updated information?
    Cheers, Innes

Share This Page