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

    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
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I like your ideas, but I'd tacke them in reverse order (3, 2, 1).

    -PatP

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

    http://msdn.microsoft.com/library/de...asp?frame=true

    Once you have the data imported into the database you can use DTS to do everything.
    Davide Mauri
    http://www.davidemauri.it

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    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
    Posts
    492
    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
  •