Why does EF5 produce these bursts in SaveChanges () times?

Question

I constantly get the results shown in the graph below. I am wondering if there is anything I can do to increase predictability. I do not use SqlBulkCopy , since I need to use the EFv5 validation features.

It would be great if someone could verify / refute my findings. My goal is to get rid of both types of spikes. I provide the source code below to make it quick for you. All you need is a class library project in VS, with links to EFv5 and NUnit, both available through NuGet. Just paste this code into Class1, change the connection string and run it. You can use the sql script below to recreate the table.

I am using .Net 4.5, EF 5, NUnit 2.6.1, running the code in Release mode, without a debugger. Db - SqlServer 2008 R2. I run a test using NUnit.exe in 64-bit mode, which shows "Net 4.0" as the version of the framework.

The X axis is the batch number (a total of 1000 parties), and the Y axis is milliseconds. You can see that the first batch takes about 30 seconds, which is expected since dbContext is "cold". Each batch saves 100 objects.

Note that this question is looking for some little information that is missing from this answer , as this jitter in EF saves.

Here is the code I'm using:

Table:

CREATE TABLE [dbo].[Entity1]( [Id] [int] IDENTITY(1,1) NOT NULL, [IntField] [int] NOT NULL, [StrField] [nvarchar](50) NOT NULL, [DateField] [datetimeoffset](7) NOT NULL, CONSTRAINT [PK_Entity1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Classes:

 using System; using System.Collections.Generic; using System.Data.Entity; using System.Diagnostics; using NUnit.Framework; namespace ClassLibrary1 { public class Entity1 { public int Id { get; protected set; } public int IntField { get; set; } public string StrField { get; set; } public DateTimeOffset DateField { get; set; } } public class MyContext : DbContext { public MyContext(string connStr) : base(connStr) { } public virtual DbSet<Entity1> Entities { get { return base.Set<Entity1>(); } } } [TestFixture] public class Class1 { [Test] public void EfPerf() { var entities = buildEntities(100000); int batchSize = 100; var batchTimes = new List<Stopwatch>(); for (int i = 0; i < entities.Length; i += batchSize) { var sw = Stopwatch.StartNew(); using (var ctx = buildCtx()) { for (int j = i; j < i + batchSize; j++) ctx.Entities.Add(entities[j]); ctx.SaveChanges(); } sw.Stop(); batchTimes.Add(sw); } batchTimes.ForEach(sw => Console.Out.WriteLine("Elapsed ms: " + sw.ElapsedMilliseconds)); } private MyContext buildCtx() { var cs = "Data Source=your db server;" + "Initial Catalog=your db;" + "Persist Security Info=True;" + "User ID=your user;" + "Password=your pwd"; var ctx = new MyContext(cs); //ctx.Configuration.ProxyCreationEnabled = false; return ctx; } private Entity1[] buildEntities(int count) { var entities = new Entity1[count]; for (int i = 0; i < count; i++) entities[i] = new Entity1 { IntField = i, StrField = "str" + i, DateField = DateTimeOffset.UtcNow }; return entities; } } } 

Answer 1

I have a feeling that you are faced with a lock problem in your database. EF places all calls to saveChanges () in a transaction. By default, transactions in SQL Server operate as Read Committed, which are very restrictive. Perhaps you can try changing the isolation level like this:

  using (var scope = new TransactionScope(TransactionScopeOption.Required, new 2: TransactionOptions { IsolationLevel= IsolationLevel.Snapshot })) 3: { 4: // do something with EF here 5: scope.Complete(); 6: }