Fastest universal way to insert data using standard ADO.NET constructs Now that the slow stuff is out of the way, lets talk about some hardcore bulk loading. Aside from SqlBulkCopy and specialized constructs involving ISAM or custom bulk insert classes from other providers, there is simply no beating the raw power of ExecuteNonQuery() on a parameterized INSERT statement.
internal static void FastInsertMany(DbConnection cnn) { using (DbTransaction dbTrans = cnn.BeginTransaction()) { using (DbCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)"; DbParameter Field1 = cmd.CreateParameter(); cmd.Parameters.Add(Field1); for (int n = 0; n < 100000; n++) { Field1.Value = n + 100000; cmd.ExecuteNonQuery(); } } dbTrans.Commit(); } } |
Simple, elegant, clean. 100,000 inserts on my machine in 1.4 seconds. Yes, that’s 10 times more inserts than the DataAdapter/DbCommandBuilder/DataTable method, and at very nearly the same speed as example 1!
Do not, under any circumstances, bulk insert data by building your own command text over and over and over again. That is the surest way to drag performance into the dirt. Build your CommandText one time, build your parameters one time, and set their values over and over and over instead. SQLite runs much faster, SQL Server runs much faster, and every database known to mankind will run much faster with a prepared, parameterized insert statement.
source: Fastest Bulk Inserts
Recent Comments