fastest way to insert data (no bulk insert, no dts)

[Hakan Eren]

Hi all,

What is the fastest way to insert data using ADO.Net?

I have to insert more than 500.000 records and calling
xCommand.ExecuteNonQuery is no good.

I also tried filling a data table first and then updating
it.

SQL Server's Bulk insert operation is even more than good
bu it reads from a text file only and also we plan to use
both sqlsrv and oracle, so I can't use dts too.

I have to find a portable programmatic way.

thanx

 

[William Ryan]

Run it in its own thread. Inserting 500k records the SQL Statements is
never going to be really fast. Bulk Insert Rocks, you could write your data
out to a text file and fire it from there.

Filling a DataTable and then updating it isn't going to be any quicker than
executenonquery, you're still doing the same thing.

DTS btw can write to Oracle, SQL, Excel and just about everything else, you
don't even need SQL Server as a DataSource to use it...SQL Loader that
Oracle uses can do a lot for you too.

 

[David Browne]

Hi all,

What is the fastest way to insert data using ADO.Net?

I have to insert more than 500.000 records and calling
xCommand.ExecuteNonQuery is no good.

I also tried filling a data table first and then updating
it.

SQL Server's Bulk insert operation is even more than good
bu it reads from a text file only and also we plan to use
both sqlsrv and oracle, so I can't use dts too.

As William suggested your fastest, portable mechanism is going to be flat
file loading. Both SQL Server and Oracle do it extremely quickly.

From SQL Server you can use the BULK INSERT statement to avoid spawing BCP.
And for Oracle 9i you can use external tables to avoid spawning SQL*Loader.

For both of these methods you need to get the file to the server.

So you either have to push the files to a staging directory on the db
server, and tell the server to pick them up from there. Or you have to
spawn the BCP for SQL and SQL*Loader for oracle. But you can do that from
your "client" machine if you have the appropriate tools installed.

For Oracle only, ODP.NET support array-bound inserts, and so
..ExecuteNonQuery can insert rows as quickly as SQL*Loader (using
conventional path inserts).

For SQLServer the best you can do with ExecuteNonQuery is to group your
insert statements into big batches and send them over to the server
together.

David



David

 

[bruce barker]

for sqlserver, insert more than one record per batch (query). try about
100-500 per batch. for oracle just do an array insert.


-- bruce (sqlwork.com)