Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Update pipeline performance improvements in 7.0 #26797

Closed
8 tasks done
ajcvickers opened this issue Nov 23, 2021 · 4 comments
Closed
8 tasks done

Update pipeline performance improvements in 7.0 #26797

ajcvickers opened this issue Nov 23, 2021 · 4 comments
Assignees
@roji
Labels
area-change-tracking area-perf area-save-changes closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-enhancement
Milestone
7.0.0

Comments

@ajcvickers
Copy link
Member

ajcvickers commented Nov 23, 2021 •
edited by roji
Loading

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 ajcvickers added this to the 7.0.0 milestone Nov 23, 2021
@AndriySvyryd AndriySvyryd mentioned this issue Jan 14, 2022
Closed
@roji roji mentioned this issue Apr 27, 2022
Open
@ajcvickers ajcvickers removed their assignment Jul 7, 2022
@roji roji changed the title Improve change tracking and update performance Update pipeline performance improvements in 7.0 Jul 19, 2022
@roji roji mentioned this issue Jul 19, 2022
Open
13 tasks
@roji roji closed this as completed Jul 19, 2022
@ajcvickers ajcvickers modified the milestones: 7.0.0, 7.0.0-rc1 Jul 22, 2022
@ajcvickers
Copy link
Member Author

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
Copy link

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

@ajcvickers: What is HiLo?

@ajcvickers
Copy link
Member Author

@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
Copy link
Member

roji commented Aug 10, 2022

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

@ajcvickers ajcvickers added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Aug 14, 2022
@ajcvickers ajcvickers modified the milestones: 7.0.0-rc1, 7.0.0 Nov 5, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@roji roji

Labels
area-change-tracking area-perf area-save-changes closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-enhancement
Projects
None yet
Milestone
7.0.0
Development

No branches or pull requests
3 participants
@Saibamen @ajcvickers @roji