Database heavy query caused by QueryableNotificationUsersImpl

Fixed in

EPiServer.CMS.Core 11.20.3

Created

Nov 26, 2020

Updated

Jan 15, 2021

Area

CMS Core

State

Closed, Fixed and tested


Description

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


this is a DB running on 80 Gen5 vCores!