I've got an interesting optimization problem that I'd like some thoughts on. I have two tables in question here: Stats and Stat_Metas. We are talking about basketball stats here, in case that helps anyone visualize this.
Stat_Metas is a list of the types of stats I am recording on the system (assists, rebounds, etc.).
Stats are where I record those stats happening. So, the Stats table relates to one Stat_Meta, but also tells you which player did the assist or shot or whatever.
Now, I calculate (aggregate, really) a few stats that are really rather intense and are taking up to 15-20 seconds to load in the view. The stats are calculated on one page for every player a team has, and every game they play. and these numbers are all aggregated into a table where the rows are player's names, and the columns are the types of stats I am calculating.
I fully recognize that part of the reason for this is that I have somewhat inefficient code (my CS-student roommate used his knowledge from a data structures class to tell me that much), but I have a feeling that even after fully optimizing it, I won't be at a good speed to hot-load these calculations.
So, I'm thinking that I'd like to write a queue worker to run every time the data changes that will do these calculations one time, and be available whenever any user wants to see them. Compare that to my current system where as the users requests the page, they have to wait for the stats to be aggregated and calculated.
Here is the question:
How should I do this in the DB?
I am considering using JSON in the DB because I can relate these calculated numbers to each player for an unknown number of players on each team.
I am also considering giving each stat type it's own column in a table, and relating that to the players ID. Problem there is that if I add new stats in the future, it will likely be a headache.
What do you all think? Are either of these the right approach? Is this the right approach at all?