I've seen a few questions in here over the years re fast importing of data items, so I thought I'd post our solution. It's SQL Server only, although I'm sure similar features are available on some other platforms that might be adaptable.
We wrote a test to import 10,000 records into a holding table, and using entities it too almost exactly 100 seconds, a rate of 10ms per entity.
Experimenting with transactions and different 'batch' sizes made little difference, so I looked at alternatives.
Looking at the standard System.Data.SQLClient shows an SqlBulkCopy object, and one option takes a DataTable as input. TypedViews inherit from DataTable, so they can be passed in directly, but we need to modify and inject several fields during the data preparation stage, plus we wanted the data from a file, not a database, so we simply use the LLBLGen TypedView as a strongly typed container that matches our destination table.
In summary, the test code is shown below.
This code imports 10,000 items in 3.5 seconds on our test machine. That's a 96% improvement in performance!!
I'm simply sharing this hoping others find this method helpful, and it's a great way of enforcing type safe values during creation of the objects, as the TypedView enforces valid datatype values when creating the object.
The only downer in all of this is that the TypedView's Row object does not allow Nullables, so you have to either check the value for null and not assign a value if it is, of you can modify the templates to implement nullable properly in the underlying row.
For example, a TypedView Row property would need to look something like this:
public System.DateTime? DateOfBirth
{
get { return IsDateOfBirthNull() ? (System.DateTime)TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)) : (System.DateTime)this[_parent.DateOfBirthColumn]; }
set { this[_parent.DateOfBirthColumn] = value.HasValue ? (object)value.Value : DBNull.Value; }
}
Anyway, here's the super-fast code snippet:
using (var db = new DataAccessAdapter())
{
var holdingTable = new PanelMemberHoldingTypedView();
for (int i = 0; i < 10000; i++)
{
var newrow = (PanelMemberHoldingRow) holdingTable.NewRow();
newrow.PanelMemberHoldingId = -1;
newrow.Command = "";
newrow.PanelMemberId = 0;
newrow.PanelId = Guid.Empty;
newrow.InvitationHash = "";
:
: (another 30 fields assigned values here)
:
holdingTable.Rows.Add(newrow);
}
Stopwatch sw = new Stopwatch();
sw.Start();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(db.ConnectionString))
{
bulkCopy.DestinationTableName = "dbo.PanelMember_Holding";
try
{
bulkCopy.WriteToServer(holdingTable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
sw.Stop();
Console.WriteLine($"Process ran in {sw.ElapsedMilliseconds}ms");
Console.WriteLine("Press Enter to exit.");
Console.ReadLine();
}
