I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates.
How the system works
We are running a web (.NET) loyalty program, so every time users performs a certain action (that in turn adds or subtracts points from their account), we are logging that in a transaction log table. That table has about 10 columns, but the (most) important ones are:
Operation(points earned, awarded or spent),
ItemDescription(a description of the action),
Every time a user logs in and access their profile, they can see their transaction log history sorted by date, with the aggregated fact that we have a
Partial Total column that needs to be calculated via the SUM of each item on the list. This transaction log can be filtered by date range, and has a paging feature.
They also see a cumulative of all the points they have earned/awarded or spent since the beginning of time.
We only run INSERTs and SELECTs on this table, and we are currently (after about 4 years of the system being running) in the 200 million range, and it is only going to get bigger fast. We have over 300k users registered, and we need the response to be fairly quick if possible (obviously there will be a middle layer of code to try to apply optimizations to the data querying). We are running SQL Server 2012 (11.0.7462.6).
I am looking for suggestions on alternatives on how to redesign this thinking ahead, so we don't have to worry about within the next five years or so.
- Maybe using a NoSQL product?
- What would be the limit to determine that SQL Server is no longer viable?
- Maybe we just need to have our indexes optimized better? (which is also a topic of conversation because we need to keep the site up and running about 98% of time and index rebuild and defrag has an impact on performance)
Right now we are performing the partial calculations directly on SQL Server but we are thinking about moving that calculation over to the client side, however we are still very early on the design process to know if this is feasible.
Basically I wanted to ask the (much smarter) collective their thoughts on this matter, and see if anyone can point me on the right direction.
Thanks a lot for your time!