More efficient way to insert a dataset in .NET 2.0?

[Sahil Malik [MVP]]

Thanks Mythran .. well .. your comments weren't necessarily incorrect - that
is a way to do it in 1.1. Anyway, that guy didn't reply back with any sane
arguments, so I guess topic closed

_ SM

 

[Guest]

Thanks for your insight, Sahil, I'll definitely check your book out. Doing
individual inserts is killing the execution time of my batch at present.

 

[Mythran]

Thanks for your insight, Sahil, I'll definitely check your book out.
Doing
individual inserts is killing the execution time of my batch at present.

I'm sure this has already been said, but saying against just to emphasis the
speed improvements...

If you have a large set of data you need to import into a SQL Server
database, you should use the SQL Server BULK INSERT command. Either by
Transact SQL or by using the bulk insert utility....look it up in the sql
server documentation on how to use

Put it this way, I had a dataset containing 300k rows that needed to be
inserted...with a DataSet and the adapter's Update method, this took awhile
(5-10 min). The whole application utility to do the import that read from
disk, wrote to the database, and updated afterwards took ~22 minutes. The
bulk of the time it took was due to the insert and update. I changed the
way it inserted, so that it used the bulk insert transact sql statement,
using a format file to define the format of the data. I wrote the data out
to a file on the hard drive, then called the bulk insert. From the time it
started inserting to the time it finished was 15 seconds...the entire app
runs in under 2 minutes now. So, bulk insert is good for fast
importing...although there are some considerations you should read up on (in
the documentation)...

Mythran

 

[Guest]

Thanks for the suggestion! I actually just discovered this one digging into
the new features of ADO.NET 2.0 and it looks like the way to go.