hunterhawley
1 month ago

Query Efficiency

Posted 1 month ago by hunterhawley

I am calculating basketball stats and have the models Stat, User (which the basketball players are held within), Team, Stat_Meta, Game, Season, Substitution.

I have a view called statTable that can be added to any other view on the app. statTable basically just iterates through each player on the team and retrieves the calculation for each stat type (found in Stat_Meta model). Within those calculation, there are queries run for the Stat, Game, Season, etc. tables. By the time it iterates through every player and all their stats, we are looking at like 500 queries PER game (often we are going through like ~30 queries/view, so you do the math, it's bad).

My question: With the Laravel debug bar installed, I can see that in my test environment, I've got 3,116 queries running when loading the front page, and 2,432 of them are duplicates. It takes forever to load as well. So, how can I re-work this system of queries to reduce the number of them?

Full disclosure, I'm not a CS person, so this isn't something I'm trained in. Right now, I'm super happy this even works, but now it is going to cost me an arm and a leg to do all these queries at scale (not to mention horrible UX).

I've thought about passing a collection of all Stats that is used all around each part of the calculations, but when I try to do that, I get an error, because I utilize a method on the Stat model during these calculations. If anyone knows of a way to just pass this collection around, but still allow it to use the Stat model methods, that would probably increase the efficiency enough to make this thing work correctly.

Please sign in or create an account to participate in this conversation.