Authorize request protocol validation slow SQL query in v.6

[Toshik]

Discussed in https://github.com/DuendeSoftware/IdentityServer/discussions/667

^{Originally posted by Toshik January 21, 2022}
Hello.

I use IS with PostgreSQL (Npgsql EF 6.0.2 driver used)

After upgrading .Net to v.6 and Duende to version 6.0.1 I've met a performance issue. During Login page rendering Identity Server issues some extremely slow SQL queries:

[11:48:07 DBG] client configuration validation for client CLIENT-NAME succeeded.
[11:48:07 DBG] AuthenticationScheme: Identity.Application was not authenticated.
[11:48:07 DBG] Start authorize request protocol validation
[11:48:09 INF] Executed DbCommand (1,720ms) [Parameters=[@__clientId_0='?'], CommandType='Text', CommandTimeout='30']
SELECT c."Id", c."AbsoluteRefreshTokenLifetime", c."AccessTokenLifetime", c."AccessTokenType", c."AllowAccessTokensViaBrowser", c."AllowOfflineAccess", c."AllowPlainTextPkce", c."AllowRememberConsent", c."AllowedIdentityTokenSigningAlgorithms", c."AlwaysIncludeUserClaimsInIdToken", c."AlwaysSendClientClaims", c."AuthorizationCodeLifetime", c."BackChannelLogoutSessionRequired", c."BackChannelLogoutUri", c."CibaLifetime", c."ClientClaimsPrefix", c."ClientId", c."ClientName", c."ClientUri", c."ConsentLifetime", c."Created", c."Description", c."DeviceCodeLifetime", c."EnableLocalLogin", c."Enabled", c."FrontChannelLogoutSessionRequired", c."FrontChannelLogoutUri", c."IdentityTokenLifetime", c."IncludeJwtId", c."LastAccessed", c."LogoUri", c."NonEditable", c."PairWiseSubjectSalt", c."PollingInterval", c."ProtocolType", c."RefreshTokenExpiration", c."RefreshTokenUsage", c."RequireClientSecret", c."RequireConsent", c."RequirePkce", c."RequireRequestObject", c."SlidingRefreshTokenLifetime", c."UpdateAccessTokenClaimsOnRefresh", c."Updated", c."UserCodeType", c."UserSsoLifetime", c0."Id", c0."ClientId", c0."Origin", c1."Id", c1."ClientId", c1."GrantType", c2."Id", c2."ClientId", c2."Scope", c3."Id", c3."ClientId", c3."Type", c3."Value", c4."Id", c4."ClientId", c4."Created", c4."Description", c4."Expiration", c4."Type", c4."Value", c5."Id", c5."ClientId", c5."Provider", c6."Id", c6."ClientId", c6."PostLogoutRedirectUri", c7."Id", c7."ClientId", c7."Key", c7."Value", c8."Id", c8."ClientId", c8."RedirectUri"
FROM "Clients" AS c
LEFT JOIN "ClientCorsOrigins" AS c0 ON c."Id" = c0."ClientId"
LEFT JOIN "ClientGrantTypes" AS c1 ON c."Id" = c1."ClientId"
LEFT JOIN "ClientScopes" AS c2 ON c."Id" = c2."ClientId"
LEFT JOIN "ClientClaims" AS c3 ON c."Id" = c3."ClientId"
LEFT JOIN "ClientSecrets" AS c4 ON c."Id" = c4."ClientId"
LEFT JOIN "ClientIdPRestrictions" AS c5 ON c."Id" = c5."ClientId"
LEFT JOIN "ClientPostLogoutRedirectUris" AS c6 ON c."Id" = c6."ClientId"
LEFT JOIN "ClientProperties" AS c7 ON c."Id" = c7."ClientId"
LEFT JOIN "ClientRedirectUris" AS c8 ON c."Id" = c8."ClientId"
WHERE c."ClientId" = @__clientId_0
ORDER BY c."Id", c0."Id", c1."Id", c2."Id", c3."Id", c4."Id", c5."Id", c6."Id", c7."Id"

Given my DB has just a few records in it, query result contains over 57K records due to JOIN statements multiplication. As a result each query gets executed in 4-6 seconds. With IdentityServer 5.2.3 it works absolutely faster with the same DB data.

Taking above in account, I assume with production data amount this query is going to be a show stopper.

[Toshik]

ClientStore.FindClientByIdAsync:

        var query = Context.Clients
            .Where(x => x.ClientId == clientId)
            .Include(x => x.AllowedCorsOrigins)
            .Include(x => x.AllowedGrantTypes)
            .Include(x => x.AllowedScopes)
            .Include(x => x.Claims)
            .Include(x => x.ClientSecrets)
            .Include(x => x.IdentityProviderRestrictions)
            .Include(x => x.PostLogoutRedirectUris)
            .Include(x => x.Properties)
            .Include(x => x.RedirectUris)
            .AsNoTracking();
            
        var client = (await query.ToArrayAsync(CancellationTokenProvider.CancellationToken))
            .SingleOrDefault(x => x.ClientId == clientId);

I guess this makes no sense

(await query.ToArrayAsync(CancellationTokenProvider.CancellationToken))
            .SingleOrDefault(x => x.ClientId == clientId);

as we already have clients filtered out by clientId, and we should not load whole dataset from DB to take single record from it.
So ToArrayAsync and Where could be just replaced with SingleOrDefault(x => x.ClientId == clientId).

Replacing it with some projection instead of bunch of Include could even make query execution much faster

[maulik-modi]

@Toshik , can you elaborate, how replacing by projection would help here?

@brockallen , Is there any interface for IClientStore cache?

[maulik-modi]

I guess single query is enough as client id is already checked in where

 var query = Context.Clients
            **.Where(x => x.ClientId == clientId)**
            .Include(x => x.AllowedCorsOrigins)
            .Include(x => x.AllowedGrantTypes)
            .Include(x => x.AllowedScopes)
            .Include(x => x.Claims)
            .Include(x => x.ClientSecrets)
            .Include(x => x.IdentityProviderRestrictions)
            .Include(x => x.PostLogoutRedirectUris)
            .Include(x => x.Properties)
            .Include(x => x.RedirectUris)
            .AsNoTracking()
            .FirstOrDefault();
            

[Toshik]

@maulik-modi doing Include normally produces LEFT JOIN, which reduces performance in case there is a bunch of joins.
Using projection we may produce subqueries, which supposed to be faster in this particular scenario.

Also, using projection allows you to filter out unneeded data to reduce dataset loaded from DB, e.g. exclude expired ClientSecrets

[brockallen]

Have you looked at our caching docs?

[Toshik]

@brockallen caching actually helps, thank you.
But, still wondering how long it will take to execute that query to update cache in production.
Executing that query is also going to aggressively consume RAM

[brockallen]

After upgrading .Net to v.6 and Duende to version 6.0.1 I've met a performance issue.

The change is what was recommended to us by the EF team given their changes in 6.0. I'm happy to re-review this, and there is a issue (somewhere -- I'll have to look for it/them) on the history behind these changes.

Also, is it possible this is something in the Postgres provider that doesn't work the same as other providers? I don't know Postgres at such a low level, and that's also been the cause of other issues in the past. I'd trust you have more insight on this.

I guess this makes no sense

That's due to customers often misconfiguring collation in the DB, so it's an app level mechanism to avoid dups. We've seen it before with various ways people post-configure their DB. This is not your issue, tho, from what I can tell.

[brockallen]

Update to this: #694 (comment)

[brockallen]

Closing this one to track there. Thanks.