private static long BenchmarkInsert(int count, Action<EntityDataContext> contextAction) { var sw = new Stopwatch(); sw.Start(); for (int i = 0; i < count; i++) { using (var dataContext = new EntityDataContext()) { dataContext.Configuration.AutoDetectChangesEnabled = false; contextAction(dataContext); dataContext.SaveChanges(); } } sw.Stop(); return sw.ElapsedMilliseconds; }
private static void BenchmarkSelects() { int singleRecordSelectWarmUpCount = 100; int singleRecordSelectCount = 10000; int joinSelectWarmUpCount = 1; int joinSelectCount = 100; int joinSelectAllCount = 10; using (var dataContext = new EntityDataContext()) { //Load keys to find then Guid sequentialGuidKey = dataContext.GuidKeySequentialParentEntities .AsNoTracking() .OrderBy(e => e.Id) .Select(e => e.Id) .Skip(1000) .FirstOrDefault(); Guid nonSequentialGuidKey = dataContext.GuidKeyNonSequentialParentEntities .AsNoTracking() .OrderBy(e => e.Id) .Select(e => e.Id) .Skip(1000) .FirstOrDefault(); int autoIncrementKey = dataContext.AutoIncrementIntKeyParentEntities .AsNoTracking() .OrderBy(e => e.Id) .Select(e => e.Id) .Skip(1000) .FirstOrDefault(); //Benchmarking selecting single records (with child collection) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == sequentialGuidKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records by sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == sequentialGuidKey))); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == nonSequentialGuidKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records by non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == nonSequentialGuidKey))); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == autoIncrementKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records by auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .FirstOrDefault(e => e.Id == autoIncrementKey))); //Benchmarking selecting single records (with all child collections) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == sequentialGuidKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records (all children) by sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == sequentialGuidKey))); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == nonSequentialGuidKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records (all children) by non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == nonSequentialGuidKey))); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, singleRecordSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == autoIncrementKey)); Console.WriteLine("Selecting {0} records...", singleRecordSelectCount); Console.WriteLine("Selecting records (all children) by auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, singleRecordSelectCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .FirstOrDefault(e => e.Id == autoIncrementKey))); //Benchmarking joining 1000 records in one select (with child collection) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record sets with sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record sets with non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record sets with auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .OrderBy(e => e.Id) .Take(1000) .ToList())); //Benchmarking joining 1000 records in one select (with all child collections) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record (all children) sets with sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record sets (all children) with non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectCount); Console.WriteLine("Selecting 1000-record sets (all children) with auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .OrderBy(e => e.Id) .Take(1000) .ToList())); //Benchmarking joining all records in one select (with child collection) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records with sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records with non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records with auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .ToList())); //Benchmarking joining all records in one select (with all child collections) Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records (all children) with sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.GuidKeySequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records (all children) with non-sequential GUID primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.GuidKeyNonSequentialParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList())); Console.WriteLine("Warming up..."); BenchmarkSelect(dataContext, joinSelectWarmUpCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList()); Console.WriteLine("Selecting {0} recordsets...", joinSelectAllCount); Console.WriteLine("Selecting all records (all children) with auto-increment primary key took {0} ms", BenchmarkSelect(dataContext, joinSelectAllCount, c => c.AutoIncrementIntKeyParentEntities .AsNoTracking() .Include(e => e.Children1) .Include(e => e.Children2) .Include(e => e.Children3) .Include(e => e.Children4) .Include(e => e.Children5) .ToList())); } }
private static void PopulateTablesForSelects(int count) { Console.WriteLine("Populating tables for select benchmark with {0} records", count); for (int i = 0; i < count; i++) { using (var dc = new EntityDataContext()) { var p1 = new GuidKeySequentialParentEntity { Name = "Entity" + i, Children1 = new List<GuidKeySequentialChildEntity1>(), Children2 = new List<GuidKeySequentialChildEntity2>(), Children3 = new List<GuidKeySequentialChildEntity3>(), Children4 = new List<GuidKeySequentialChildEntity4>(), Children5 = new List<GuidKeySequentialChildEntity5>() }; var p2 = new GuidKeyNonSequentialParentEntity { Name = "Entity" + i, Children1 = new List<GuidKeyNonSequentialChildEntity1>(), Children2 = new List<GuidKeyNonSequentialChildEntity2>(), Children3 = new List<GuidKeyNonSequentialChildEntity3>(), Children4 = new List<GuidKeyNonSequentialChildEntity4>(), Children5 = new List<GuidKeyNonSequentialChildEntity5>() }; var p3 = new AutoIncrementIntKeyParentEntity { Name = "Entity" + i, Children1 = new List<AutoIncrementIntKeyChildEntity1>(), Children2 = new List<AutoIncrementIntKeyChildEntity2>(), Children3 = new List<AutoIncrementIntKeyChildEntity3>(), Children4 = new List<AutoIncrementIntKeyChildEntity4>(), Children5 = new List<AutoIncrementIntKeyChildEntity5>() }; for (int j = 0; j < 10; j++) { var c11 = new GuidKeySequentialChildEntity1 { Name = "Child1" + i + "_" + j }; var c12 = new GuidKeySequentialChildEntity2 { Name = "Child2" + i + "_" + j }; var c13 = new GuidKeySequentialChildEntity3 { Name = "Child3" + i + "_" + j }; var c14 = new GuidKeySequentialChildEntity4 { Name = "Child4" + i + "_" + j }; var c15 = new GuidKeySequentialChildEntity5 { Name = "Child5" + i + "_" + j }; var c21 = new GuidKeyNonSequentialChildEntity1 { Name = "Child1" + i + "_" + j }; var c22 = new GuidKeyNonSequentialChildEntity2 { Name = "Child2" + i + "_" + j }; var c23 = new GuidKeyNonSequentialChildEntity3 { Name = "Child3" + i + "_" + j }; var c24 = new GuidKeyNonSequentialChildEntity4 { Name = "Child4" + i + "_" + j }; var c25 = new GuidKeyNonSequentialChildEntity5 { Name = "Child5" + i + "_" + j }; var c31 = new AutoIncrementIntKeyChildEntity1 { Name = "Child1" + i + "_" + j }; var c32 = new AutoIncrementIntKeyChildEntity2 { Name = "Child2" + i + "_" + j }; var c33 = new AutoIncrementIntKeyChildEntity3 { Name = "Child3" + i + "_" + j }; var c34 = new AutoIncrementIntKeyChildEntity4 { Name = "Child4" + i + "_" + j }; var c35 = new AutoIncrementIntKeyChildEntity5 { Name = "Child5" + i + "_" + j }; p1.Children1.Add(c11); p1.Children2.Add(c12); p1.Children3.Add(c13); p1.Children4.Add(c14); p1.Children5.Add(c15); p2.Children1.Add(c21); p2.Children2.Add(c22); p2.Children3.Add(c23); p2.Children4.Add(c24); p2.Children5.Add(c25); p3.Children1.Add(c31); p3.Children2.Add(c32); p3.Children3.Add(c33); p3.Children4.Add(c34); p3.Children5.Add(c35); } dc.GuidKeySequentialParentEntities.Add(p1); dc.GuidKeyNonSequentialParentEntities.Add(p2); dc.AutoIncrementIntKeyParentEntities.Add(p3); dc.SaveChanges(); } } }
private static long BenchmarkSelect(EntityDataContext dataContext, int count, Action<EntityDataContext> contextAction) { var sw = new Stopwatch(); sw.Start(); for (int i = 0; i < count; i++) { contextAction(dataContext); } sw.Stop(); //An attempt to make garbage collection not impact benchmarking process ) GC.Collect(); Thread.Sleep(2000); return sw.ElapsedMilliseconds; }