Table design for user sessions: highly-frequent selecting/updating of rows, that are subsequently not often touched
0
votes
0
answers
29
views
I have a use-case where we are required to keep track of certain metrics within user sessions. Now, these metrics/stats update very often with each action/event that occurs (hundreds of times in several minutes per active user).
My current setup is simple: SQL Server table with the metric fields and an expiry datetime field. With every update done to the row, this expiry date is also updated to 5 minutes in the future. As longs as the expiry date is in the future, the current session is updated, otherwise a new one is created.
I have an index on the userId, some other related id column and on the expiry date column.
But as soon as load on the application increases, some queries (both selects and updates) on this table are really slow (10 seconds) and slow everything down to the point the application crashes.
I am assuming this occurs due to the large number of selects and updates on an ever-increasing field (expiry datetime) that is indexed? I still have to confirm this hypothesis.
Is anything fundamentally wrong with my table architecture? How would you approach something like this? Could in an intermediary table fix the problem?
Thanks a lot in advance.
Asked by Senne
(1 rep)
Nov 12, 2022, 05:00 PM