RolandGuijt
commented
1 month ago The code you're referring to was written to accommodate searches from the UI. ApplyFilter is called from QuerySessionsAsync which is ultimately called from the UI. We used Contains to support partial matches as users type and we chose convenience over performance in this case.
Good that you found a more suitable method for your needs.
And thanks for reporting this: we're now discussing internally if this behavior should change. If we decide to do that I will link to the issue here.
Which version of Duende IdentityServer are you using? 7.0.6
Which version of .NET are you using? .NET 8
Describe the bug
We had an issue recently whereby we were experiencing SQL timeouts when closing accounts in Identity Server. As part of our closure function, we use the session management service to query for all of the server side session related to the identity, which we then iterate through and remove using SessionManagementService.RemoveSessionsAsync method.
Upon investigating the SQL trace, we noticed that when querying user sessions, the resulting SQL is running a LIKE filter, something like
WHERE SubjectId LIKE @param0 AND SessionId LIKE @param1Because of the LIKE operator, the query is unable to leverage the indexes on the table as it forced to perform a full table scan.
This is coming from the following library code, which is using a "contains" filter. I can't see why it would need to do this for subjectId or sessionId as they are both GUID fields, so a partial match wouldn't be useful functionality.
I was able to work around the issue by instead calling ServerSideTicketStore.GetSessionsAsync directly, which uses an equality check as expected.
https://github.com/DuendeSoftware/IdentityServer/blob/e9860c6488f90e8fbc11a4452b9dd111dbfae933/src/EntityFramework.Storage/Stores/ServerSideSessionStore.cs#L248
To Reproduce Set up a SQL trace Call the SessionManagementService.QuerySessionsAsync method with a subjectID or sessionID filter set Inspect the resulting SQL query
Expected behavior The resulting query uses an equality filter on the subjectID and sessionID GUID fields.
Log output/exception with stacktrace
Additional context N/A