Faster SQL Bulk Inserts With C# #254
Replies: 7 comments 9 replies
-
I think there is a comparison missing, handling inserts in a table with a Instead creating a DbCommand and it's parameters each time there should be use a single var customerItem = customers.First();
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(cmdText, connection);)
{
command.Parameters.AddWithValue("@Id", customerItem.Id);
command.Parameters.AddWithValue("@FirstName", customerItem.FirstName);
command.Parameters.AddWithValue("@LastName", customerItem.LastName);
command.Parameters.AddWithValue("@Street", customerItem.Street);
command.Parameters.AddWithValue("@City", customerItem.City);
command.Parameters.AddWithValue("@State", customerItem.State);
command.Parameters.AddWithValue("@PhoneNumber", customerItem.PhoneNumber);
command.Parameters.AddWithValue("@EmailAddress", customerItem.EmailAddress);
connection.Open();
foreach (var customer in customers)
{
command.Parameters[0].Value = customer.Id;
command.Parameters[1].Value = customer.FirstName;
command.Parameters[2].Value = customer.LastName;
command.Parameters[3].Value = customer.Street;
command.Parameters[4].Value = customer.City;
command.Parameters[5].Value = customer.State;
command.Parameters[6].Value = customer.PhoneNumber;
command.Parameters[7].Value = customer.EmailAddress;
command.ExecuteNonQuery();
}
} Bit other kind of that: var first = true;
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(cmdText, connection);)
{
var parameters = command.Parameters;
connection.Open();
foreach (var customer in customers)
{
if(first)
{
parameters.AddWithValue("@Id", customer.Id);
parameters.AddWithValue("@FirstName", customer.FirstName);
parameters.AddWithValue("@LastName", customer.LastName);
parameters.AddWithValue("@Street", customer.Street);
parameters.AddWithValue("@City", customer.City);
parameters.AddWithValue("@State", customer.State);
parameters.AddWithValue("@PhoneNumber", customer.PhoneNumber);
parameters.AddWithValue("@EmailAddress", customer.EmailAddress);
first = false;
}
else
{
parameters[0].Value = customer.Id;
parameters[1].Value = customer.FirstName;
parameters[2].Value = customer.LastName;
parameters[3].Value = customer.Street;
parameters[4].Value = customer.City;
parameters[5].Value = customer.State;
parameters[6].Value = customer.PhoneNumber;
parameters[7].Value = customer.EmailAddress;
}
command.ExecuteNonQuery();
}
} The approach above I have more maximized, additionally you can use this in combination with multi threading - |
Beta Was this translation helpful? Give feedback.
-
Just wanted to say thanks for this writeup. I'm scraping 100's of millions of records from an environment and inserting them 1-by-1 was a horrible solution. Now I'm at around 20-30 seconds per half-million because the dataset is wider than your samples. Thanks a bunch! |
Beta Was this translation helpful? Give feedback.
-
Love this. However, looking at the WriteToServer call "ToDataTable" and not seeing this as available option. could be I'm running an older version of .Net? Or perhaps I overlooked creating this method in your code? Cheers |
Beta Was this translation helpful? Give feedback.
-
Hi @tim, Could you share the code for ToDataTable() Function that is being used? |
Beta Was this translation helpful? Give feedback.
-
Hey Tim, |
Beta Was this translation helpful? Give feedback.
-
Hi. Great write up. For Generated-sql downwards. how do you support an issue with the insertion of one record when inserting in builk. I'm contemplating a move away from SQLBulkCopy due to the error reporting it has. When there's an issue with one field in a record, and that record is part of a large dataset. You might get some column information (generally hard to resolve back to the input database), but then you get no information as to which record. Unless you use the SqlRowsCopied callback with a fairly low resolution to find where it got up to. |
Beta Was this translation helpful? Give feedback.
-
Hi Tim, Quoting the doc: By default, Entity Framework performs Detect Changes automatically when the following methods are called: DbSet.Find -- If you need to add or remove many objects from a context, consider using DbSet.AddRange and DbSet.RemoveRange. This methods automatically detect changes only once after the add or remove operations are completed. |
Beta Was this translation helpful? Give feedback.
-
On your page https://github.com/timdeschryver/timdeschryver.dev/blob/main/blog/faster-sql-bulk-inserts-with-csharp/index.md I think there is a comparison missing, handling inserts in a table with a
DbCommand
andDbParameter
.Instead creating a DbCommand and it's parameters each time there should be use a single
DbCommand
instance and it's list of already created parameters. TheExecuteNonQuery()
can be executed unlimited.Bit other kind of that:
Beta Was this translation helpful? Give feedback.
All reactions