4

The technologies at hand:

  • C# .NET 4.0
  • SQL Server 2014
  • Entity Framework 4.3.1
  • Code First
  • ANTS Performance Profiler 7
  • SQL Server 2014 Profiler 2
  • Google Search

The problem:
I'm doing performance work on some software. There's a particular issue that causes a serious slow down. With an EF DataContext with around 43 ADDED entities, the DataContext.SaveChanges() method takes eats up a whole load of time.

Using SQL Profiler I can see the inserts taking place with a duration of (about) 0ms. This is as expected.

Using ANTS Profiler I can see DataContext.SaveChanges() taking (about) 1,500ms. Drilling down into this, 99.9% of this time is spent inside SNINativeMethodWrapper.SNIReadSyncOverAsync.

Using Google, there are very few useful results (well none, hence this question). For the first time in a long time, I found myself looking into page 2 and beyond of the Google results (uncharted waters!).

There are a couple of questions on SO that reference this method but from different contexts:

I'm looking for a solution that does not require any of:

  • Upgrade EF to V6+ (or any other version for that matter)
  • Move away from CodeFirst
  • Not use DataContext.SaveChanges()
  • Re-architect the software

I should add that I have disabled the following EF settings. This has a positive effect overall (as expected) but has no effect on the problem domain.

  • Context.Configuration.ValidateOnSaveEnabled = false;
  • Context.Configuration.AutoDetectChangesEnabled = false;

The Question:
Can anyone suggest a code change that can resolve or avoid this issue?

Community
  • 1
  • 1
Paul Fleming
  • 24,238
  • 8
  • 76
  • 113
  • read this http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework and then use `SqlBulkCopy` for mass insertion of records https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx – user1666620 Dec 04 '15 at 15:23
  • You should make different context based on the Bounded Context pattern. You should try to precompile the dbmodels and store them ( they are the major slow down of the big context ) and then use the precompiled dbModels per request in the process of creating DbContext. Try looking at [this online course](https://app.pluralsight.com/library/courses/efarchitecture/table-of-contents), because it explains everything you encounter. – Ognyan Dimitrov Dec 05 '15 at 15:00
  • @OgnyanDimitrov That's for your comment. I will have a watch of those courses (Plural Sight says I've already watched two but I forget when!). I am looking for a solution that doesn't require significant rearchitecture. That may not be avoidable, but it's my primary aim. I'm not sure how your suggestions solve or help to solve my problem. A bounded context with 43 added entities may still exhibit the same issue. I suspect the same if I compile the model and queries. – Paul Fleming Dec 07 '15 at 09:06
  • Sorry, but my comment was actually for another performance issue. – Ognyan Dimitrov Dec 07 '15 at 10:40
  • Why do you hesitate to move to 6.1? – Ognyan Dimitrov Dec 07 '15 at 10:41
  • actually, for this few entries in the dbcontext (suspecting not-ambient DbContext), I don't think the issue at hand is actually the inserting/manifesting changes to the database, but actually the database call itself (like creating a connection, authenticating) that is the main issue for this performance loss. I actually could imagine connection pooling would increase performance quite a bit. – DevilSuichiro Dec 07 '15 at 11:49
  • @OgnyanDimitrov. Upgrading to 6.1 doesn't guarantee a fix to the problem and is a significant piece of work. – Paul Fleming Dec 07 '15 at 13:52
  • @DevilSuichiro You may be onto something there. I profiled the DB previously and most of the `INSERT`s came back as 0. I've since profiled it again and this time they come back between 15 and 60ms. Averaging at around 33ms, they add up to about 1,400ms. So it seems that's my problem. Fat DB and human error! Write your comment as an answer and the points are yours... – Paul Fleming Dec 07 '15 at 13:56
  • If you have your abstractions in place you can easily try at least - no check-ins :) Integration test can do the job too. – Ognyan Dimitrov Dec 07 '15 at 15:16

1 Answers1

1

As suggested in the comments:

actually, for this few entries in the dbcontext (suspecting not-ambient DbContext), I don't think the issue at hand is actually the inserting/manifesting changes to the database, but actually the database call itself (like creating a connection, authenticating) that is the main issue for this performance loss. I actually could imagine connection pooling would increase performance quite a bit.

For these interested: With every "actual" Db call (that is, not query preparation, but actually fetching/writing data into the database), EF will first establish a connection if the connection string/database name is given, or use the connection given in the DbContext(Connection, bool contextOwnsConnection=true) overload of the context constructor. This will happen with every query that actually calls the database. With certain databases, this establishment of the connection can take quite a lot of time, while looping through the context entities and issueing a DELETE/UPDATE/INSERT call depending on the state should take (at least for these few entries) not as much time.

DevilSuichiro
  • 1,049
  • 8
  • 21