Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005

    Unanswered: Xml performance issue

    I have a major showstopper in transforming xml data into a table structure. The system is real-time and receiving hundreds of packed xml messages per second. I can do everything I need to outside of the server to get the data to clients but getting the data into a table in the database is killing me.

    I'm going to attach a .sql file to demonstrate my problem. I use sqlserver 2005 (and tried on 2008 with no improvement). The whole problem lies in parsing the xml into data that i can use to upsert a table - it takes too long.

    In the same provided I have two approaches - 1 users xquery (as recommended) and the other uses openxml (as definitely not recommended these days).

    I consistently get a performance of 90ms for the first approach and this is just no good because it means i can only really handle 10-odd messages per second.

    VERY unexpectedly in the second (openxml) approach I get far better performance.

    So, 2 questions:

    1. how the heck can i get a better parsing performance using this approach(I will look to throttling in a way that i stop processing every message but wait for a second to pass but i hate to do this and will still have xml parsing to do)

    2. why the heck is the old openxml approach far, far faster (5 times on my server here).

    Any thoughts, corrections, etc. will be gladly accepted and greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    XML parsing is very expensive; you may want to decouple the processes of receiving XML data and parsing it further by inserting incoming documents into an XML column in a staging table and processing it asynchronously.

    Some performance tuning hints can be found here: Performance Optimizations for the XML Data Type in SQL Server 2005
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2005
    Indeed that's exactly what happens. Just put the same together to make it easier for you good folks to see my problems.

    Thanks for responding.

  4. #4
    Join Date
    Feb 2010

    Xml performance issue

    In case XML parsing is expensive, you can use Pull or VTD-XML, I particularlly recommend vtd-xml, it is the latest and most advanced XML processing software API, it claims to be the fatest and most memory efficent, with built-in XPath support

    The Future of XML Processing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts