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!I like your ideas, but I'd tacke them in reverse order (3, 2, 1).

-PatP|||Why don't you try to bulk insert XML using SQLXML?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp?frame=true

Once you have the data imported into the database you can use DTS to do everything.|||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|||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?sql