0

I'm facing a performance issue, using c# with EF5 and a SQL Server 2012 database (with 4gb RAM), while trying to insert thousands of items.

For example, this code took me 12s to execute in a MVC app, and 5s in a Windows console app, 99% of it consume by context.saveChanges() statement.

//Prepare list
        Random rand = new Random();
        List<MyItem> list = new List<MyItem>();

        for(int i = 0; i < 1000; i++) {
            list.Add(new MyItem {
                Field1 = i,
                Field2 = rand.Next(1000),
                Field3 = rand.Next(1000),
                Field4 = rand.Next(1000),
                Field5 = rand.Next(1000),
                Field6 = rand.Next(1000),
                Field7 = rand.Next(1000),
                Field8 = rand.Next(1000),
                Field9 = rand.Next(1000),
                Field10 = rand.Next(1000)
            });
        }



        Stopwatch watch = new Stopwatch();
        Stopwatch watch2 = new Stopwatch();

        watch.Start();

        using (var context = new MyEntities())
        {
            context.Configuration.AutoDetectChangesEnabled = false;
            context.Configuration.ValidateOnSaveEnabled = false;
            foreach (MyItem item in list)
            {    
                context.MyItem.Attach(item);
                context.Entry(item).State = System.Data.EntityState.Added;           
            }

            watch2.Start();
            context.SaveChanges();
            watch2.Stop();
        }
        watch.Stop();

I tried to turn off AutoDetectChangesEnabled and ValidateOnSaveEnabled but it seems that there is no performance gain. I also tried with a single insert stored procedure, but the performance where similar.

The table MyItem is a simple table with ten integer fields, one clustered primary key.

Any help will be appreciate !

Improve this question
5
  • It should not take that long. Profile the app. Pause the debugger a few times and look at the call stack (using Show External Code). The method names will tell you what EF is doing. Maybe it is waiting on SQL Server. Commented Mar 15, 2014 at 10:36
  • 1
    @usr It should take exactly that long. 1000 calls into sql server, each with one insert do take their time. The slowness on mass inserts in E is well known and well documented. Commented Mar 15, 2014 at 10:42
  • @TomTom 5ms per insert is a little excessive. SQL Server takes <1ms. EF 4ms per insert is excessive. Should be <1ms as well. EF is slow for such things, but not that slow. Commented Mar 15, 2014 at 10:46
  • @usr If you read the comments to my answe r- that performance was falicated for a non ef loop by the poster. Commented Mar 15, 2014 at 10:54
  • Look at this extension to EF: github.com/MikaelEliasson/EntityFramework.Utilities Commented Mar 15, 2014 at 10:56

1 Answer 1

Reset to default
2

Can we just start with "Entity Framework has no bulk insert". It generates stons of insert statements witz zero batching. Zero - one round trip per line, it does not even put multiple lines into a statement. In your example that is 1000 insert statements - but also 1000 separate round trips to the server process.

I have since long written extension methods for DbContext that allow me to use a method with the signature BulkInsert<T>(IEnumerable<T>) or BulkMerge<T>(IEnumerable<T>). About 5 pages of code.

The classes (T) are generally handcrafted to avoid overlap with the EF entities. I have no problems inserting in batches of 64000 in a second or so. I mainly use my own object data reader with a SqlBUlkCopy class to push the data into a temporary table then insert or merge into the final table - the temp table avoids the exclusive lock on the final table during the upload and the not too smart locking meachnisms in SqlBulkCopy.

But using pure EF for mass inserts - you can forget performance here. If you use search here you will find tons of complaints about this. EF is an ORM - it is not an ETL tool.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thnaks for your quick answer. I've read these complains, but also that turning off options increased performance. I don't want to insert millions of lines ; I'm just surprized that I didn't get any gain. But I will be really interested in your work, if I could see it.
It likely did. As in: 1ms per round trip is not that bad. This is about as good as I would excpect. YOu hit a low level overhead. For simplicity, make that insert manually without EF and find out how fast a loop is. Rules: * Transaction, * One connection * Prepare insert statement with parameters * Loop over it for each row. You will be surprised. Here - exactly here - is your problem. And that is how EF works.
Yeah I already tried it (classic connexion, one insert statement at once), and I got same results. Could I see your extension ? (especialy with bulk merge - I will need it because I also will need to make updates)
Not really. Problem is - it is too much code to post it here. Generally: Using an object based data reader is an hour or two or work, start at weblogs.sqlteam.com/jeffs/archive/2007/05/22/…. Creating the temp table is trivial (select from original into temp where 1=0, only the fields you need) and the rest.... I Think it was a day of work including debugging and now adding the merge function. Make sure you do NOT insert into the original table.... sqlbulkcopy sets...
...(a) an exclusive lock and (b) does not maitntain it - it tries to get one. so multiple at the same time do not work nicely. The temp table avoids that totally and keeps the lock time on the final target as small as possible.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.