Re: Load XML to database performance issues

Have you looked at the SQLXML XML Bulkload object?

Best regards
Michael

<Jenden0@xxxxxxxxx> wrote in message
news:1150391421.075191.156220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ok, I feel a bit silly. About 5 minutes after I posted I figured out
how to get it to automatically pull the parameter values from the
associated dataset. Its faster now, though if anyone has any
suggestions for speeding it up even more they'd be appreciated.

Jenden0@xxxxxxxxx wrote:

I'm trying to load an XML document into a database via C# and am having
some severe performance issues. I've got a fairly large xml file (~5MB
right now, though it should be able to grow to 50MB) and am trying to
load it into an sqlserver database. I've got about 30 different
tables, so I want to try and keep it as generic as possible so I don't
have a huge mess of code to process each table individually. Also, as
the XML is essentially a backup of the database, I want to maintain the
same identity keys (instead of having sqlserver generate new ones).

I can get good performance by loading the entire xml document into a
dataset and then just saving that dataset to the database using
sqlcommandbuilder, but that changes my identity keys since sqlserver
auto-generates new ones. I can prevent the server from auto generating
the keys by setting "IDENTITY_INSERT ON", but then the
sqlcommandbuilder stops working since it doesn't insert the identity
keys (its expecting SQL to do it itself). I haven't been able to
figure out how to modify the sqlcommandbuilder to insert identity keys
for me.

That led me to my current version, which just uses sqlcommand objects I
build by hand. This works, but (as far as I've been able to figure
out) that requires me to insert each parameter into the sql command for
every record. I think this is likely where my slowdown is (iterating
through the table.row.items list for every reccord).

Anyone have any suggestions? I'm fairly new to the whole .net thing so
I may have overlooked something really obvious.



.