search

valdisiljuconoks / localization-provider-opti

Database driven localization provider for Optimizely (ex. Episerver) websites
Apache License 2.0
11 stars 17 forks source link

High database CPU usage for version 8.0.3 with .NET 8 #219

Closed WilliamknE closed 5 months ago

WilliamknE commented 6 months ago

Using version 8.0.3 with .NET 8, we have experienced a huge increase of queries from LocalizationProvider to our database. The queries sent to the DB looks like this:

(@key nvarchar(58))SELECT r.Id, r.Author, r.FromCode, r.IsHidden, r.IsModified, r.ModificationDate, r.Notes, t.Id as TranslationId, t.Value as Translation, t.Language, t.ModificationDate as TranslationModificationDate FROM [dbo].[LocalizationResources] r LEFT JOIN [dbo].[LocalizationResourceTranslations] t ON r.Id = t.ResourceId WHERE ResourceKey = @key

I have been in contact with Optimizely, where they mentioned searching for nvarchar on the varchar columns results in not beeing able to take advantage of the indexes.

We had to revert to an earlier version of LocalizationProvider to make sure the indexes in the DB is being used properly.

valdisiljuconoks commented 6 months ago

Interesting, as 8.0.3 specifically should be targeting cache issues. Do you see the same query being executed over and over (same resource key)?

valdisiljuconoks commented 6 months ago

If you are on Community Slack workspace, find me there please. Let's take this PM.

valdisiljuconoks commented 5 months ago

should be normalized in v8.1.0 (ref: https://github.com/valdisiljuconoks/localization-provider-opti/issues/220)

erkmenesen commented 1 month ago

Hi @valdisiljuconoks , Sorry to reopen this issue; we have the same problem on our Opti Commerce project. We're using v7.5.1, and from time to time, this query bombards our EpiCms database, causing the whole shop to go down. I know you already fixed it on v8.1.0, but it's only usable with DotNet 8. Our project runs on DotNet 6, and upgrading is not possible at the moment.

Your advice is crucial for us, Valdis. Do you have any suggestions to resolve this issue without upgrading to dotnet 8? Thank you so much <3

valdisiljuconoks commented 1 month ago

I guess backport to previous major runtime is required..