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

Importing data from Btrieve to SQL - Migrating data to new SQL ser

I have an old Btrieve database written by some programmer in the distant
past. It's currently running on an old 3.11 novell server. That applictation
being updated every day.
I need to get that data out of the old Btrieve DB and into a new SQL
database.
What an easy way (tool) to migrate this data over into one or more SQL tables?
thanks
Jim
You will need the DDF files if you do not already have them to use
ODBC. If you need to build these you can take a look at BtSearch32 at
www.nssdd.com. It helps you analyze the structure and build the DDF
files. It will also export to dbase or ascii formats.
Gil

Importing data from Btrieve to SQL - Migrating data to new SQL ser

I have an old Btrieve database written by some programmer in the distant
past. It's currently running on an old 3.11 novell server. That applictation
being updated every day.
I need to get that data out of the old Btrieve DB and into a new SQL
database.
What an easy way (tool) to migrate this data over into one or more SQL tables?
thanks
JimHi Jim,
Jim wrote:
> I have an old Btrieve database written by some programmer in the
> distant past. It's currently running on an old 3.11 novell server.
> That applictation being updated every day. I need to get that data
> out of the old Btrieve DB and into a new SQL database. What an easy
> way (tool) to migrate this data over into one or more SQL tables?
You should be able to use the Pervasive Btrieve ODBC driver included
with the product to migrate your data from Btrieve to your Microsoft SQL
Server. There's a good tutorial that covers this online at:
http://tinyurl.com/45m5t/
Hope this helps.|||Thanks, I'll take a look.
Good night.
Jim
"William R. Lorenz" wrote:
> Hi Jim,
> Jim wrote:
> > I have an old Btrieve database written by some programmer in the
> > distant past. It's currently running on an old 3.11 novell server.
> > That applictation being updated every day. I need to get that data
> > out of the old Btrieve DB and into a new SQL database. What an easy
> > way (tool) to migrate this data over into one or more SQL tables?
> You should be able to use the Pervasive Btrieve ODBC driver included
> with the product to migrate your data from Btrieve to your Microsoft SQL
> Server. There's a good tutorial that covers this online at:
> http://tinyurl.com/45m5t/
> Hope this helps.
>|||You will need the DDF files if you do not already have them to use
ODBC. If you need to build these you can take a look at BtSearch32 at
www.nssdd.com. It helps you analyze the structure and build the DDF
files. It will also export to dbase or ascii formats.
Gil