Get User Query slows under load

[krotkiske]

Which version of Duende IdentityServer are you using?

Version 7

Which version of .NET are you using?

Dotnet 8

Describe the bug

We have a query on account login that is hitting our SQLServer which is expensive (above screenshot). We were looking to add an index on ClaimValue and ClaimType, these columns are nvarchar(max) as you can see in the below sql. What is your recommendation in this scenario?

SELECT [u0].[Id], [u0] .[AccessFailedCount], [u0] .[ConcurrencyStamp], [u0] .[Email], [u0] .[EmailConfirmed], [u0] .[LockoutEnabled], [u0] .[LockoutEnd], [u0] .[NormalizedEmail], [u0] .[NormalizedUserName], [u0] .[PasswordHash], [u0] .[PhoneNumber], [u0] .[PhoneNumberConfirmed], [u0] .[SecurityStamp], [u0] .[TwoFactorEnabled], [u0] .[UserName]
FROM [UserClaims] AS [u]
INNER JOIN [Users] AS [u0]
ON [u].[UserId] = [u0].[Id]
WHERE [u].[ClaimValue] = @claim_Value_0
AND [u].[ClaimType] = @claim_Type_1

To Reproduce

N/A

Expected behavior

Should we modify the database to restrict the columns to something other then nvarchar(max) to help increase the efficiency of the query? We are looking to reduce the excessive time to complete the query.

Log output/exception with stacktrace

N/A

Additional context

[RolandGuijt]

IdentityServer doesn't include a user store (except for a very simple in memory one for demo purposes). Since this query is on the table UserClaims I suspect you are using ASP.NET Core Identity as a user store. The issue tracker for that product would be a better place to ask your question. In general I would say creating an index should help. I would recommend trying that with a test database.

[RolandGuijt]

I'm closing the issue for now but feel free to reopen and add to it if there are more questions about our products.

[AndersAbel]

I'm revisiting this as it is a query that should not appear during the standard flows from IdentityServer. As far as I can find out the query is executed when the UserManager.GetUsersForClaimAsync() method is called. This method is not used anywhere in the Duende.IdentityServer packages for Asp.Net Identity, nor from our template UI code.

It looks like this query is not covered by any index. If it is a method that you need to call frequently I think that you should add an index for those columns (you probably need to change the datatype too as nvarchar(max) columns cannot be included in an index.