I have to produce a lot of graphs using a lot of historical data (1000+ data points for each graph), and depending on some pre-defined options, I need to display that specific data in a graph.
Let’s say I have 4 possible combinations of options X and Y, (X,Y),(X,X),(Y,X),(Y,Y)
If each of these combinations takes 20 seconds for the queries to execute, is it acceptable to store summary data in the form of a hash in the database schema?
Now, let’s say there are 1,000,000+ records, this will mean computing these summary hash’s which will save 20s per request, but it will also mean that my database will grow immensly and will host data that is not neccessarily needed and will only be needed if a user makes that particular request.
So to summarise what I’m thinking
- Much faster loading
Increase space used in database
Add data into the database that may never even get used
I have thought about caching the data as the user is making the request so I only store data for the queries which actually get executed, however this still means a 20s waiting period for that first user.
I have also considered that my queries may be innefficient and slow, however after numerous days trying to optimise them and alter my code design to save time, I think 20s is the fastest I can achieve with the hardware available.
Any thoughts on this?