EPiServer.CMS.Core 11.20.3
Nov 26, 2020
Jan 15, 2021
CMS Core
Closed, Fixed and tested
QueryableNotificationUsersImpl.FindAsync is used to find users based on their username, not by exact match, but by pattern match. It will finally reach database with this query
(@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000))SELECT [Project1].[Id] AS [Id], [Project1].[Email] AS [Email], [Project1].[EmailConfirmed] AS [EmailConfirmed], [Project1].[PasswordHash] AS [PasswordHash], [Project1].[SecurityStamp] AS [SecurityStamp], [Project1].[PhoneNumber] AS [PhoneNumber], [Project1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], [Project1].[TwoFactorEnabled] AS [TwoFactorEnabled], [Project1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], [Project1].[LockoutEnabled] AS [LockoutEnabled], [Project1].[AccessFailedCount] AS [AccessFailedCount], [Project1].[UserName] AS [UserName] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Email] AS [Email], [Extent1].[EmailConfirmed] AS [EmailConfirmed], [Extent1].[PasswordHash] AS [PasswordHash], [Extent1].[SecurityStamp] AS [SecurityStamp], [Extent1].[PhoneNumber] AS [PhoneNumber], [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], [Extent1].[LockoutEnabled] AS [LockoutEnabled], [Extent1].[AccessFailedCount] AS [AccessFailedCount], [Extent1].[UserName] AS [UserName] FROM [dbo].[AspNetUsers] AS [Extent1] WHERE (@p__linq__0 IS NULL) OR (( CAST(LEN(@p__linq__0) AS int)) = 0) OR (( CAST(CHARINDEX(UPPER(@p__linq__1), UPPER([Extent1].[UserName])) AS int)) > 0) ) AS [Project1] ORDER BY row_number() OVER (ORDER BY [Project1].[UserName] ASC) OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY
which will basically do a full scan on AspNetUsers table. If customer has many users registered, that is a major bottleneck.
Stacktrace