search

ikyriak / IdempotentAPI

A .NET library that handles the HTTP write operations (POST and PATCH) that can affect only once for the given request data and idempotency-key by using an ASP.NET Core attribute (filter).
MIT License
283 stars 40 forks source link

Performance Issue when using Distributed Cache in MSSQL DB #51

Closed a7madsayed closed 10 months ago

a7madsayed commented 1 year ago

I am facing a performance issue when applying the IdempotantAPi using Distributed Cache through MSSql DB The ExpireHours is 24

But I Found the DB Query is taking much time in some cases (Around 35 Seconds)

This is the query I have found :

DECLARE @ExpiresAtTime DATETIMEOFFSET; SET @ExpiresAtTime = (CASE WHEN (@? IS NUll) THEN @? ELSE DATEADD(SECOND, Convert(bigint, @?), @?) END);UPDATE [dbo].[?empotentCache] SET ? = @?, ExpiresAtTime = @ExpiresAtTime,? = @?, ? = @? WHERE ? = @? IF (@@ROWCOUNT = ?) BEGIN INSERT INTO [dbo].[?empotentCache] (?, ?, ExpiresAtTime, ?, ?) VALUES (@?, @?, @ExpiresAtTime, @?, @?); END

The question here is how to solve this performance issue, Also want to know why there is an update statement in DB before inserting the Record?

ikyriak commented 1 year ago

Hello @a7madsayed,

Thanks for taking the time to report this issue. However, your issue is related to DistributedLock implementation using Microsoft SQL Server.

So, you could ask directly to them about their implementation (e.g., queries). I suppose that you are using the DistributedLock.SqlServer NuGet package.