2

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),
  • ItemAmount (self explanatory),
  • UserID and
  • Date.

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).

Considerations

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!

  • 3
    Have you looked into columnstore indexes? You would have to migrate to at least SQL Server 2016 to get the best columnstore features. – Randi Vertongen Jan 30 at 15:32
  • I have not yet, but I will definitely review it. It looks like the feature was added in 2012 but I would have to go at least to 2014 because of the update limitation - sqlwithmanoj.com/2015/06/08/… Thanks!! – leandro filipczyk Jan 30 at 15:51
  • 2
    If you have flexibility over picking a version, why would you choose 2014 over 2016 or, better yet, 2017? This is like buying a two-year warranty on a 5-year lease. – Aaron Bertrand Jan 30 at 15:54
  • 1
    Yep, you are right. I do not know how much flexibility we have around migration, need to check with the team. If migration is an option and columnstore indexes are viable in our scenario then most definitely will aim for 2017. Thanks!! – leandro filipczyk Jan 30 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.