IdentityServer / IdentityServer3.EntityFramework

EntityFramework persistence layer for IdentityServer3
Apache License 2.0
68 stars 97 forks source link

Improve perf of token cleanup #103

Open feanz opened 8 years ago

feanz commented 8 years ago

resolves #89

Use entity framework extended to run the token clean up process. This pevent the cleanup code pulling the data back before deleting it.

Add an index to the expiry field to improve performance of selection.

This current solution does not include @tbarcelon proposed plan to move the clustered index to expiry. It just adds an extra non clustered index.

Note: I've included a test that I would remove before merging this code it just so you can run it quickly run it to check it works.

I've profiled the sql that gets executed by entityframework extended.

DELETE [dbo].[Tokens]
FROM [dbo].[Tokens] AS j0 INNER JOIN (
SELECT 
    1 AS [C1], 
    [Extent1].[Key] AS [Key], 
    [Extent1].[TokenType] AS [TokenType]
    FROM [dbo].[Tokens] AS [Extent1]
    WHERE [Extent1].[Expiry] < '2016-05-13 14:19:03.6501655 +00:00'
) AS j1 ON (j0.[Key] = j1.[Key] AND j0.[TokenType] = j1.[TokenType])

Included execution plan image

Less than ideal compared to the alternative but probably good enough.

DELETE FROM [dbo].[Tokens] WHERE [Expiry] < '2016-05-13 14:19:03.6501655 +00:00'

image

dnfclas commented 8 years ago

Hi @feanz, I'm your friendly neighborhood .NET Foundation Pull Request Bot (You can call me DNFBOT). Thanks for your contribution! In order for us to evaluate and accept your PR, we ask that you sign a contribution license agreement. It's all electronic and will take just minutes. I promise there's no faxing. https://cla2.dotnetfoundation.org.

TTYL, DNFBOT;

dnfclas commented 8 years ago

@feanz, Thanks for signing the contribution license agreement so quickly! Actual humans will now validate the agreement and then evaluate the PR.
Thanks, DNFBOT;

brockallen commented 8 years ago

Just got around to this now -- I like the idea and sorry for the delay.