search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

Update pipeline performance improvements in 7.0 #26797

Closed ajcvickers closed 2 years ago

ajcvickers commented 2 years ago

Over the past few releases, we have focused on improving EF Core performance on non-tracking queries. For EF7, we plan to focus on performance related to database inserts and updates. This includes performance of change-tracking queries, performance of DetectChanges, and performance of the insert and update commands sent to the database.

The work done here is described in this blog post, and discussed in this community standup.

ajcvickers commented 2 years ago

In doing some testing for other features I noticed these changes kicking in for inserting a graph of entities. /cc @roji @JeremyLikness

EF Core 6.0 does six round trips:

info: 8/10/2022 12:21:38.321 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (18ms) [Parameters=[@p0='cf7e7f72-42e5-4b72-375a-08da7ac27df8', @p1='FarmFood' (Nullable = false) (Size = 4000), @p2='Hay' (Size = 4000), @p3='8dd245bd-d4b5-4b51-3758-08da7ac27df8', @p4='670' (Nullable = true), @p5='HumanFood' (Nullable = false) (Size = 4000), @p6='Sushi' (Size = 4000),
 @p7='1f1c8912-5e70-4750-3757-08da7ac27df8', @p8='Canagan' (Size = 4000), @p9='PetFood' (Nullable = false) (Size = 4000), @p10='1' (Nullable = true), @p11='e13a0b04-ac1a-4671-3759-08da7ac27df8', @p12='Lily's Kitchen' (Size = 4000), @p13='PetFood' (Nullable = false) (Size = 4000), @p14='1' (Nullable = true)], Co
mmandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Foods] ([Id], [Discriminator], [Name])
      VALUES (@p0, @p1, @p2);
      INSERT INTO [Foods] ([Id], [Calories], [Discriminator], [Name])
      VALUES (@p3, @p4, @p5, @p6);
      INSERT INTO [Foods] ([Id], [Brand], [Discriminator], [LifeStage])
      VALUES (@p7, @p8, @p9, @p10),
      (@p11, @p12, @p13, @p14);
info: 8/10/2022 12:21:38.347 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (13ms) [Parameters=[@p15='Cat' (Nullable = false) (Size = 4000), @p16='MBA' (Size = 4000), @p17='e13a0b04-ac1a-4671-3759-08da7ac27df8' (Nullable = true), @p18='Alice' (Nullable = false) (Size = 4000), @p19='Cat' (Nullable = false) (Size = 4000), @p20='Preschool' (Size = 4000), @p21='e13
a0b04-ac1a-4671-3759-08da7ac27df8' (Nullable = true), @p22='Mac' (Nullable = false) (Size = 4000), @p23='Dog' (Nullable = false) (Size = 4000), @p24='Mr. Squirrel' (Size = 4000), @p25='1f1c8912-5e70-4750-3757-08da7ac27df8' (Nullable = true), @p26='Toast' (Nullable = false) (Size = 4000), @p27='FarmAnimal' (Null
able = false) (Size = 4000), @p28='cf7e7f72-42e5-4b72-375a-08da7ac27df8' (Nullable = true), @p29='Clyde' (Nullable = false) (Size = 4000), @p30='Equus africanus asinus' (Size = 4000), @p31='100.0' (Nullable = true) (Precision = 18) (Scale = 2)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
      MERGE [Animals] USING (
      VALUES (@p15, @p16, @p17, @p18, 0),
      (@p19, @p20, @p21, @p22, 1)) AS i ([Discriminator], [EducationLevel], [FoodId], [Name], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Discriminator], [EducationLevel], [FoodId], [Name])
      VALUES (i.[Discriminator], i.[EducationLevel], i.[FoodId], i.[Name])
      OUTPUT INSERTED.[Id], i._Position
      INTO @inserted0;

      SELECT [i].[Id] FROM @inserted0 i
      ORDER BY [i].[_Position];

      INSERT INTO [Animals] ([Discriminator], [FavoriteToy], [FoodId], [Name])
      VALUES (@p23, @p24, @p25, @p26);
      SELECT [Id]
      FROM [Animals]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

      INSERT INTO [Animals] ([Discriminator], [FoodId], [Name], [Species], [Value])
      VALUES (@p27, @p28, @p29, @p30, @p31);
      SELECT [Id]
      FROM [Animals]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 8/10/2022 12:21:38.372 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[@p0='Human' (Nullable = false) (Size = 4000), @p1='3' (Nullable = true), @p2=NULL (DbType = Guid), @p3='Wendy' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Animals] ([Discriminator], [FavoriteAnimalId], [FoodId], [Name])
      VALUES (@p0, @p1, @p2, @p3);
      SELECT [Id]
      FROM [Animals]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 8/10/2022 12:21:38.378 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p0='Human' (Nullable = false) (Size = 4000), @p1='1' (Nullable = true), @p2='8dd245bd-d4b5-4b51-3758-08da7ac27df8' (Nullable = true), @p3='Arthur' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Animals] ([Discriminator], [FavoriteAnimalId], [FoodId], [Name])
      VALUES (@p0, @p1, @p2, @p3);
      SELECT [Id]
      FROM [Animals]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 8/10/2022 12:21:38.379 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0='Human' (Nullable = false) (Size = 4000), @p1='4' (Nullable = true), @p2=NULL (DbType = Guid), @p3='Christi' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Animals] ([Discriminator], [FavoriteAnimalId], [FoodId], [Name])
      VALUES (@p0, @p1, @p2, @p3);
      SELECT [Id]
      FROM [Animals]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 8/10/2022 12:21:38.384 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[@p4='5', @p5='1', @p6='5', @p7='2', @p8='5', @p9='3', @p10='6', @p11='1', @p12='6', @p13='2', @p14='6', @p15='3'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Dictionary<object, string>] ([HumansId], [PetsId])
      VALUES (@p4, @p5),
      (@p6, @p7),
      (@p8, @p9),
      (@p10, @p11),
      (@p12, @p13),
      (@p14, @p15);

EF7 with Identity does three round trips:

info: 8/10/2022 12:19:13.006 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (24ms) [Parameters=[@p0='7093f657-a3b8-4624-4d58-08da7ac22757', @p1='FarmFood' (Nullable = false) (Size = 4000), @p2='Hay' (Size = 4000), @p3='df0a116c-f55d-42f2-4d56-08da7ac22757', @p4='670' (Nullable = true), @p5='HumanFood' (Nullable = false) (Size = 4000), @p6='Sushi' (Size = 4000),
 @p7='1ecefb3b-6489-4a09-4d57-08da7ac22757', @p8='Lily's Kitchen' (Size = 4000), @p9='PetFood' (Nullable = false) (Size = 4000), @p10='1' (Nullable = true), @p11='d4925a5b-601f-4ea4-4d55-08da7ac22757', @p12='Canagan' (Size = 4000), @p13='PetFood' (Nullable = false) (Size = 4000), @p14='1' (Nullable = true), @p1
5='Cat' (Nullable = false) (Size = 4000), @p16='MBA' (Size = 4000), @p17='1ecefb3b-6489-4a09-4d57-08da7ac22757' (Nullable = true), @p18='Alice' (Nullable = false) (Size = 4000), @p19='Cat' (Nullable = false) (Size = 4000), @p20='Preschool' (Size = 4000), @p21='1ecefb3b-6489-4a09-4d57-08da7ac22757' (Nullable = t
rue), @p22='Mac' (Nullable = false) (Size = 4000), @p23='Dog' (Nullable = false) (Size = 4000), @p24='Mr. Squirrel' (Size = 4000), @p25='d4925a5b-601f-4ea4-4d55-08da7ac22757' (Nullable = true), @p26='Toast' (Nullable = false) (Size = 4000), @p27='FarmAnimal' (Nullable = false) (Size = 4000), @p28='7093f657-a3b8
-4624-4d58-08da7ac22757' (Nullable = true), @p29='Clyde' (Nullable = false) (Size = 4000), @p30='Equus africanus asinus' (Size = 4000), @p31='100.0' (Nullable = true) (Precision = 18) (Scale = 2)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Foods] ([Id], [Discriminator], [Name])
      VALUES (@p0, @p1, @p2);
      INSERT INTO [Foods] ([Id], [Calories], [Discriminator], [Name])
      VALUES (@p3, @p4, @p5, @p6);
      INSERT INTO [Foods] ([Id], [Brand], [Discriminator], [LifeStage])
      VALUES (@p7, @p8, @p9, @p10),
      (@p11, @p12, @p13, @p14);
      MERGE [Animals] USING (
      VALUES (@p15, @p16, @p17, @p18, 0),
      (@p19, @p20, @p21, @p22, 1)) AS i ([Discriminator], [EducationLevel], [FoodId], [Name], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Discriminator], [EducationLevel], [FoodId], [Name])
      VALUES (i.[Discriminator], i.[EducationLevel], i.[FoodId], i.[Name])
      OUTPUT INSERTED.[Id], i._Position;
      INSERT INTO [Animals] ([Discriminator], [FavoriteToy], [FoodId], [Name])
      OUTPUT INSERTED.[Id]
      VALUES (@p23, @p24, @p25, @p26);
      INSERT INTO [Animals] ([Discriminator], [FoodId], [Name], [Species], [Value])
      OUTPUT INSERTED.[Id]
      VALUES (@p27, @p28, @p29, @p30, @p31);
info: 8/10/2022 12:19:13.040 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (7ms) [Parameters=[@p32='Human' (Nullable = false) (Size = 4000), @p33='3' (Nullable = true), @p34=NULL (DbType = Guid), @p35='Wendy' (Nullable = false) (Size = 4000), @p36='Human' (Nullable = false) (Size = 4000), @p37='1' (Nullable = true), @p38='df0a116c-f55d-42f2-4d56-08da7ac22757'
(Nullable = true), @p39='Arthur' (Nullable = false) (Size = 4000), @p40='Human' (Nullable = false) (Size = 4000), @p41='4' (Nullable = true), @p42=NULL (DbType = Guid), @p43='Christi' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      MERGE [Animals] USING (
      VALUES (@p32, @p33, @p34, @p35, 0),
      (@p36, @p37, @p38, @p39, 1),
      (@p40, @p41, @p42, @p43, 2)) AS i ([Discriminator], [FavoriteAnimalId], [FoodId], [Name], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Discriminator], [FavoriteAnimalId], [FoodId], [Name])
      VALUES (i.[Discriminator], i.[FavoriteAnimalId], i.[FoodId], i.[Name])
      OUTPUT INSERTED.[Id], i._Position;
info: 8/10/2022 12:19:13.045 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[@p44='5', @p45='3', @p46='5', @p47='1', @p48='5', @p49='2', @p50='6', @p51='3', @p52='6', @p53='1', @p54='6', @p55='2'], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [Dictionary<object, string>] ([HumansId], [PetsId])
      VALUES (@p44, @p45),
      (@p46, @p47),
      (@p48, @p49),
      (@p50, @p51),
      (@p52, @p53),
      (@p54, @p55);

EF7 with HiLo does it all in a single database call!

info: 8/10/2022 12:27:56.971 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (17ms) [Parameters=[@p0='6012a865-624b-4a17-402a-08da7ac35fa7', @p1='FarmFood' (Nullable = false) (Size = 4000), @p2='Hay' (Size = 4000), @p3='e1008f20-b713-4a3c-4028-08da7ac35fa7', @p4='670' (Nullable = true), @p5='HumanFood' (Nullable = false) (Size = 4000), @p6='Sushi' (Size = 4000),
 @p7='33732a70-f101-4d62-4029-08da7ac35fa7', @p8='Lily's Kitchen' (Size = 4000), @p9='PetFood' (Nullable = false) (Size = 4000), @p10='1' (Nullable = true), @p11='8d0a7091-d013-44a9-4027-08da7ac35fa7', @p12='Canagan' (Size = 4000), @p13='PetFood' (Nullable = false) (Size = 4000), @p14='1' (Nullable = true), @p1
5='4', @p16='Cat' (Nullable = false) (Size = 4000), @p17='MBA' (Size = 4000), @p18='33732a70-f101-4d62-4029-08da7ac35fa7' (Nullable = true), @p19='Alice' (Nullable = false) (Size = 4000), @p20='7', @p21='Cat' (Nullable = false) (Size = 4000), @p22='Preschool' (Size = 4000), @p23='33732a70-f101-4d62-4029-08da7ac
35fa7' (Nullable = true), @p24='Mac' (Nullable = false) (Size = 4000), @p25='2', @p26='Dog' (Nullable = false) (Size = 4000), @p27='Mr. Squirrel' (Size = 4000), @p28='8d0a7091-d013-44a9-4027-08da7ac35fa7' (Nullable = true), @p29='Toast' (Nullable = false) (Size = 4000), @p30='6', @p31='FarmAnimal' (Nullable = f
alse) (Size = 4000), @p32='6012a865-624b-4a17-402a-08da7ac35fa7' (Nullable = true), @p33='Clyde' (Nullable = false) (Size = 4000), @p34='Equus africanus asinus' (Size = 4000), @p35='100.0' (Nullable = true) (Precision = 18) (Scale = 2), @p36='1', @p37='Human' (Nullable = false) (Size = 4000), @p38='2' (Nullable
 = true), @p39=NULL (DbType = Guid), @p40='Wendy' (Nullable = false) (Size = 4000), @p41='3', @p42='Human' (Nullable = false) (Size = 4000), @p43='4' (Nullable = true), @p44='e1008f20-b713-4a3c-4028-08da7ac35fa7' (Nullable = true), @p45='Arthur' (Nullable = false) (Size = 4000), @p46='5', @p47='Human' (Nullable
 = false) (Size = 4000), @p48='6' (Nullable = true), @p49=NULL (DbType = Guid), @p50='Christi' (Nullable = false) (Size = 4000), @p51='1', @p52='2', @p53='1', @p54='4', @p55='1', @p56='7', @p57='3', @p58='2', @p59='3', @p60='4', @p61='3', @p62='7'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Foods] ([Id], [Discriminator], [Name])
      VALUES (@p0, @p1, @p2);
      INSERT INTO [Foods] ([Id], [Calories], [Discriminator], [Name])
      VALUES (@p3, @p4, @p5, @p6);
      INSERT INTO [Foods] ([Id], [Brand], [Discriminator], [LifeStage])
      VALUES (@p7, @p8, @p9, @p10),
      (@p11, @p12, @p13, @p14);
      INSERT INTO [Animals] ([Id], [Discriminator], [EducationLevel], [FoodId], [Name])
      VALUES (@p15, @p16, @p17, @p18, @p19),
      (@p20, @p21, @p22, @p23, @p24);
      INSERT INTO [Animals] ([Id], [Discriminator], [FavoriteToy], [FoodId], [Name])
      VALUES (@p25, @p26, @p27, @p28, @p29);
      INSERT INTO [Animals] ([Id], [Discriminator], [FoodId], [Name], [Species], [Value])
      VALUES (@p30, @p31, @p32, @p33, @p34, @p35);
      INSERT INTO [Animals] ([Id], [Discriminator], [FavoriteAnimalId], [FoodId], [Name])
      VALUES (@p36, @p37, @p38, @p39, @p40),
      (@p41, @p42, @p43, @p44, @p45),
      (@p46, @p47, @p48, @p49, @p50);
      INSERT INTO [Dictionary<object, string>] ([HumansId], [PetsId])
      VALUES (@p51, @p52),
      (@p53, @p54),
      (@p55, @p56),
      (@p57, @p58),
      (@p59, @p60),
      (@p61, @p62);
Saibamen commented 2 years ago

EF7 with HiLo does it all in a single database call!

@ajcvickers: What is HiLo?

ajcvickers commented 2 years ago

@Saibamen It's a value generation strategy that reserves a range of key values in the database and then uses those values as needed, as opposed to requesting key values at the time of insert.

roji commented 2 years ago

@Saibamen take a look at this blog post, which summarizes optimizations done under this epic; it contains a brief explanation of HiLo.