Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004

    Unanswered: Importing From XML

    Hello everyone and thanks in advance.

    Ok, here's the deal. I am 1 of 2 developers at my organization, the other is a COBOL programmer. Long story short, every night he sends me a 22MB XML file containing our company's inventory. This contains about 40,000 items. In C#, I'm basically looping through all the XML nodes and on each node I load up an object with the particular properties (manufacturer, SKU, serial number, etc.) and call a Stored Procedure to to save the item.

    In saving the item, I'm checking to see if it was already in my inventory and I either perform an update or an insert on the table as approriate. The query does a couple of other things like retreiving the Purchase Order Number, Cubic Feet, etc. from other tables. All in all, the query takes about 1-2 seconds to complete. I've optimized the query as much as I know how (including the addition of indexes), but this still takes at least 6 hours to complete the 40,000 items. Is this the best approach? Any other suggestions?

    So far the only things I've thought about changing is:
    1. Break it out into multiple queries to avoid poor compilation of execution plans

    2. Multithread the C# app so that I'm running like 10 queries at once.

    3. Get the COBOL programmer to give me a delimited file and do a BULK INSERT on it into an empty HEAP, then run a single INSERT and a single UPDATE (using CASE statements)

    Again, Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I like your ideas, but I'd tacke them in reverse order (3, 2, 1).


  3. #3
    Join Date
    Feb 2003
    Milano, Italy
    Why don't you try to bulk insert XML using SQLXML?

    Once you have the data imported into the database you can use DTS to do everything.
    Davide Mauri

  4. #4
    Join Date
    Nov 2002
    What does XML and the relational model have in common?

    Absolutley nuthin

    say it again

    XML, what is it good for.....

    EDIT: That deserves a BIG MOO

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Not sure if I see this correctly, but your processing the nodes one by one? Could it help if you'd compile the xml into an appropriate object structure, see what's new and then have a go at saving it?

Posting Permissions

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