Why is Identity Server making what seems like a duplicate set of DB calls

Question

I'm working on fixing some major latency issues with our login process, and in doing a lot of logging to Identtiy Server 4, I found what looks like a duplicate set of queries being run on every login.

Does anyone with some experience with IS4 have any idea why this is happening or what can be done about it? The calls appear to be coming from the middleware of IS4, and not any of our custom code.

EDIT: I added some tracking in my database for queries, and found the following:

These sets of queries run about 7 times during the full login process. Other queries are run as well, but these in particular run seven times.

I'm new to using the query store this way, but my understanding of how the stored execution plans and data works, if a different parameter had been passed in, it would appear as a new line in one of the tables (either the query store or the query_store_plan). Since there is only one row per query in the query store, and one plan per row, my conclusion is that it is only looking for one particular client ID. We'll call that client 'AdminTool'

It seems highly inefficient to run the same query seven times during the login process. I'm sure some of that has to do with token generation and other things that the data can't be persisted across, but seven still seems excessive.

One sample query from the execution plan is:

 SELECT [x.IdentityProviderRestrictions].[Id], [x.IdentityProviderRestrictions].[ClientId],
 [x.IdentityProviderRestrictions].[Provider] 
 FROM [ClientIdPRestrictions] AS [x.IdentityProviderRestrictions]
 INNER JOIN (SELECT TOP(1) [x6].[Id] FROM [Clients] AS [x6] 
            WHERE [x6].[ClientId] = @__clientId_0
            ORDER BY [x6].[Id]) 
            AS [t5] 
 ON [x.IdentityProviderRestrictions].[ClientId] = [t5].[Id]
 ORDER BY [t5].[Id]

With the execution plan's parameter list including:

 <ColumnReference Column="@__clientId_0" ParameterDataType="nvarchar(200)"
     ParameterCompiledValue="N'AdminTool'" />

Are you **sure** they are duplicates? What are the `clientId` being passed for each entry? — mjwills, Jul 26 '18 at 21:31
If you can, please copy/paste the logs in to the question (make sure there isn't any sensitive data in them though) — phuzi, Jul 27 '18 at 13:58
Have you configured it to *use* any [`Caching`](http://docs.identityserver.io/en/release/topics/startup.html#caching)? — Damien_The_Unbeliever, Jul 27 '18 at 14:03
@Damien_The_Unbeliever we have not, which may be part of the issue. I will check into that — Marshall Tigerus, Jul 27 '18 at 14:25

score 2 · Accepted Answer · answered Jul 27 '18 at 14:28

2

There are simple caching components that come with IdentityServer, but by default they're not configured to be used.

This means that, if you're using a database backed store (for Clients, Resources, etc) then every access to that store will generate database work. And those stores will get hit several times during any common usage of IdentityServer.

The Caching section of the documentation discusses how to install the simple components which are based around an in-memory cache. There are also more advanced topics if you wish to implement caching yourself, and I believe there's a package (not aware of its official status) to use Redis for caching.

answered Jul 27 '18 at 14:28

Damien_The_Unbeliever

234,701
27
340
448

implemented IS4's caching and it did not have any impact on the number of queries hitting the db or the speed of login. I'm looking into the implementation of it. – Marshall Tigerus Jul 27 '18 at 14:53
As a note, once caching was implemented per the recommendations on another question https://stackoverflow.com/questions/51560620/caching-does-not-appear-to-work-in-identity-server-4 it all worked – Marshall Tigerus Aug 09 '18 at 13:37

Why is Identity Server making what seems like a duplicate set of DB calls

1 Answers1