EF9 Migration Performance Regression

[cmatheso]

I've recently started looking at upgrading our .NET 8 apps from EF8 to EF9 and unfortunately think we are blocked until the regression issues are resolved.

As part of our testing infrastructure we spin up a fresh containerized mssql database and run migrations before proceeding with integration testing. Using EF8, we were able to run through >320 migrations in ~15 seconds. Bumping up to EF9 I've manually aborted the process past 10 minutes - its not finishing in a reasonable amount of time. I've noticed 2 main changes pre/post upgrade, 1st shows via the generated SQL is the lack of GO commands between statements and the single global transaction change vs. per migration transaction changes. The other is parameterization of seeded record inserts, but not confident that's causing the overall degradation.

Trying to debug some recorded dbcommands pre/post upgrade, here's 1 example of the performance degradation I'm seeing:

EF9 generated SQL to insert a single seeded row object during a larger migration:

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N?, N?, N?, N?, N?, N?, N?, N?) AND [object_id] = OBJECT_ID(N?))
    SET IDENTITY_INSERT [MY_DB].[MY_TABLE] ON;
INSERT INTO [MY_DB].[MY_TABLE] ([Id], [CreatedAt], [CreatedBy], [FkColumn1], [FkColumn2], [IsDisplayable], [UpdatedAt], [UpdatedBy])
VALUES (?, ?, N?, ?, ?, CAST(? AS bit), ?, N?),
(?, ?, N?, ?, ?, CAST(? AS bit), ?, N?);
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N?, N?, N?, N?, N?, N?, N?, N?) AND [object_id] = OBJECT_ID(N?))
    SET IDENTITY_INSERT [MY_DB].[MY_TABLE] OFF;

Execution time: 3.47 seconds

EF8 generated SQL to insert a single seeded row object during a larger migration:

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreatedAt', N'CreatedBy', N'FkColumn1', N'FkColumn2', N'UpdatedAt', N'UpdatedBy') AND [object_id] = OBJECT_ID(N'[MY_DB].[MY_TABLE]'))
    SET IDENTITY_INSERT [MY_DB].[MY_TABLE] ON;
INSERT INTO [MY_DB].[MY_TABLE] ([Id], [CreatedAt], [CreatedBy], [FkColumn1], [FkColumn2], [UpdatedAt], [UpdatedBy])
VALUES ('1940df2a-0341-4950-950f-a95449844678', '2025-02-26T21:55:16.0890000Z', N'00000000-0000-0000-0000-000000000000', 'c464c814-cff4-4c44-85fe-48aee620147e', '0870c5d5-04f8-4d73-8cea-c447c8548eaa', '2025-02-26T21:55:16.0890000Z', N'00000000-0000-0000-0000-000000000000');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreatedAt', N'CreatedBy', N'FkColumn1', N'FkColumn2', N'UpdatedAt', N'UpdatedBy') AND [object_id] = OBJECT_ID(N'[MY_DB].[MY_TABLE]'))
    SET IDENTITY_INSERT [MY_DB].[MY_TABLE] OFF;

Execution time: 6.9 msec.

Table definition if curious:

CREATE TABLE [MY_DB].[MY_TABLE]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_Id] DEFAULT (newsequentialid()),
[Adm_TenantId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_Adm_TenantId] DEFAULT ('00000000-0000-0000-0000-000000000000'),
[FkColumn1] [uniqueidentifier] NOT NULL,
[FkColumn2] [uniqueidentifier] NOT NULL,
[Adm_UpdatedBy] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_Adm_UpdatedBy] DEFAULT (suser_sname()),
[Adm_UpdatedAt] [datetime2] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_Adm_UpdatedAt] DEFAULT (getutcdate()),
[CreatedBy] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_CreatedBy] DEFAULT ('00000000-0000-0000-0000-000000000000'),
[CreatedAt] [datetime2] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_CreatedAt] DEFAULT (getutcdate()),
[UpdatedBy] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_UpdatedBy] DEFAULT ('00000000-0000-0000-0000-000000000000'),
[UpdatedAt] [datetime2] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_UpdatedAt] DEFAULT (getutcdate()),
[IsDisplayable] [bit] NOT NULL CONSTRAINT [DF_MY_DB_MY_TABLE_IsDisplayable] DEFAULT (CONVERT([bit],(1)))
);
GO
ALTER TABLE [MY_DB].[MY_TABLE] ADD CONSTRAINT [PK_MY_DB_MY_TABLE_Id] PRIMARY KEY CLUSTERED ([Id])
GO

I'm not able to post internal app code but may be able to spin up a test repo if needed. Couple key info points:

• Using .NET 8.
• Attempting to use EF 9.0.2 (from 8.0.10).
• Windows11, MSSQL via WSL2, local environment.
• 1 Database, 1 DbContext, 11 Tables, >320 EF migrations.
• Most migrations are pure EF generated (ie, creates/alter tables, update indexes, etc). We have ~500 rows of seeded data spread throughout. Very small set of migrations are custom/raw sql.
• Running start -> finish the migration SQL when dumped to a script is around 12000 lines. The lack of GO commands post EF9 unfortunately also makes the generated script unusable out of box.
• Other libs: Microsoft.Data.SqlClient=6.0.1, OpenTelemetry.Instrumentation.SqlClient=1.11.0-beta.1.