/api/users/all performance degraded when using external auth

[benPearce1]

Severity

No blocked customers

Version

2024.2.5035

Latest Version

I could reproduce the problem in the latest build

What happened?

Performance of the /api/users/all endpoint is slower when using external authentication with a large number of claims per user.
This can be observed on the User list, Subscriptions, test permissions, events and other pages.

Reproduction

Call the /api/users/all or navigate to one of the pages listed. The api call will be slower than previously, in some cases going from ~1 second to more than 20 seconds.

Error and Stacktrace

No response

More Information

The generated sql query behind this endpoint is causing an exponentially higher amount of data to be read

SELECT [u].[Id], [u].[AccessTokensNotBefore], [u].[Created], [u].[DataVersion], [u].[DisplayName], [u].[EmailAddress], [u].[IdentificationToken], [u].[IsActive], [u].[IsService], [u].[LastModified], [u].[PasswordHashed], [u].[RefreshTokensNotBefore], [u].[Username], [t].[Id], [t].[Provider], [t].[UserId], [t].[Id0], [t].[IsIdentifyingClaim], [t].[IsServerSideOnly], [t].[Key], [t].[UserIdentityId], [t].[Value], [t0].[Id], [t0].[Provider], [t0].[UserId], [t0].[Id0], [t0].[GroupId], [t0].[UserExternalProviderId]
FROM [User] AS [u]
LEFT JOIN (
    SELECT [u0].[Id], [u0].[Provider], [u0].[UserId], [u1].[Id] AS [Id0], [u1].[IsIdentifyingClaim], [u1].[IsServerSideOnly], [u1].[Key], [u1].[UserIdentityId], [u1].[Value]
    FROM [UserIdentity] AS [u0]
    LEFT JOIN [UserIdentityClaim] AS [u1] ON [u0].[Id] = [u1].[UserIdentityId]
) AS [t] ON [u].[Id] = [t].[UserId]
LEFT JOIN (
    SELECT [u2].[Id], [u2].[Provider], [u2].[UserId], [u3].[Id] AS [Id0], [u3].[GroupId], [u3].[UserExternalProviderId]
    FROM [UserExternalProvider] AS [u2]
    LEFT JOIN [UserExternalProviderSecurityGroup] AS [u3] ON [u2].[Id] = [u3].[UserExternalProviderId]
) AS [t0] ON [u].[Id] = [t0].[UserId]
ORDER BY [u].[Username], [u].[Id], [t].[Id], [t].[Id0], [t0].[Id]

Workaround

None known