How to initialize a large Seed efficiently with EF Code First?

Question

We are using EF Code First with SQL Server, and we have a rather large set of master data (~150.000 records) that has to be initialized when the database is created. It would be convenient if this could be done in the Seed method of the db-intializer.

I have generated the SQL script initializing this data. The script consists of lines, where every line contains the INSERT operation for a thousand records. The reason for this is that INSERT commands have a limit of 1000 total records in a single command.

Thus the script is similar to the following:

INSERT INTO MyTable (A, B, C) VALUES (1, 2, 3), (2, 3, 4), ... 1000 records here ... (3, 4, 5);
INSERT INTO MyTable (A, B, C) VALUES (4, 5, 6), (5, 6, 7), ... 1000 records here ... (6, 7, 8);
.... 150 lines

The initialization part of our Seed method looks like this:

foreach (string command in File.ReadAllLines("InsertMasterData.sql"))
{
    context.Database.ExecuteSqlCommand(command);
}

However, this almost takes 2 minutes on my developer machine, using an SQL LocalDB instance (it will probably be faster on a full-blown SQL Server used in production, but it's convenient to use LocalDB for development).

Is there any way to make this faster?

Is this for unit testing so it's done all the time? Otherwise, 2 minutes as a "one time operation" doesn't sound _too_ horrible. — Joachim Isaksson, Oct 06 '14 at 18:28
possible duplicate of [Improving bulk insert performance in Entity framework](http://stackoverflow.com/questions/6107206/improving-bulk-insert-performance-in-entity-framework) — 48klocs, Oct 06 '14 at 18:29
We are early in the development so this has to be done every time when the model changes because we haven't started using migrations yet. Of course if there is no easy way to make it faster, I can always create a smaller version of the master data and use that during development. — Mark Vincze, Oct 06 '14 at 20:09

score 1 · Accepted Answer · answered Oct 07 '14 at 15:11

We have a similar situation where we seed scripts. This is our code. You don't need to use the async method. It seeds about 35,000 records in a few seconds locally.

foreach (string file in files)
            {
                var fileInfo = new FileInfo(file);

                Console.WriteLine("Running script {0}", fileInfo.Name);

                string text = fileInfo.OpenText().ReadToEnd();

                using (var conn = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand(text, conn))
                    {
                        conn.Open();

                        await cmd.ExecuteNonQueryAsync();
                    }
                }
            }

How to initialize a large Seed efficiently with EF Code First?

1 Answers1