Hi all. Does anybody has experience with plotting huge datasets in CakePHP in combination with Chart.JS?
I have a dataset with about 220000 rows and I need to fetch all data somewhere in controller and send it to template to process them further by the Chart.JS.
Any idea? I tried it to do it but memory gets always exhausted …
Thank you in advance.
This is not something I’ve tried in CakePHP, or really any framework.
From my perspective, it’s best to keep datasets you want to plot to <10K rows, as that amount of data already far exceeds the resolution of your chart on a screen, let alone the memory required for a browser to render it in a any charting library (especially one based on SVG, though Canvas could handle more), as well as the MB of bandwidth needed to transfer it to a browser.
If users need to be able to zoom in to see the full resolution dataset, it’s better to use an API to up/down sample the data accordingly,
On the framework end, it also depends on how your data is stored. If it’s jjust one table, that would be an easy request/response. But of course, returning a full db table is going to eat up a lot of memory if it’s a big dataset. No way around that. And if your query requires several joins, well, doing that and returning the full 220K rows obviously adds exponential overhead.
If your dataset is really that big, you might want to think about providing alternative resolutions quickly (e.g. daily or monthly averages). In which case, it’s probably best to pre-generate those, either on a cron or saved as an alternate dataset. Thus, as users zoom in/out you can just switch to the other resolutions.
The trick is to figure out what your graph really needs to show (i.e. what questions you users need to answer), and then design from there to balance the user needs with the technical overhead. Brute force sometimes works (and is a good way to start prototyping), but once things get complicated, I think you’re going to have to make tradeoffs.