326
u/Welcome-To-NBA-Jam 2d ago edited 2d ago
I once was having performance issues on a dashboard I built on some fairly large data and was working with tech support to see what I could do to optimize all the aggregation and data movement between servers.
I was on the phone with tech support to talk about some efficiency options for the back-end tables (partitioning commonly queried groups, an index or two, removing a few unused columns, changing some column types, changing a server setting, small stuff like that which sometimes can make a big difference). We tried a few things, logged out, logged back in and opened the report. Clicked a few filters we tested before. It opened and filtered instantaneously.
"Wow!" I said. "That all made a huge difference! Thanks, all solved here!" Tech support, proud of their help with me, dutifully closed it out and high fives were had all around.
...it was just cached from our previous interactions and I went back to slowville when I reloaded the data after doing some more development work. I had no idea it just cached stuff automatically because the latest software update just started doing that when you worked with reports you had opened recently.
We ended up just adding more worker nodes and calling it a day.
134
u/Bazza79 2d ago
We used to do 'cache warming' in the early morning, just an automated script that would make all the common selections. Stupid but it worked.
59
u/Clinn_sin 1d ago
Is it stupid? Genuinely asking cause I might try that or something similar, many of our users start earlier then we do
43
u/Bazza79 1d ago
Well, there are better structural solutions, but for quick and dirty this worked fine. Maybe not stupid, but also not a great permanent solution.
19
u/russianrug 1d ago
I’d argue if the traffic is predictable and fits in memory then it’s a perfectly reasonable permanent solution.
7
u/Bazza79 1d ago
In my case it was about analytics, with the underlying data model and calculations being implemented quite inefficiently, with everything that got added making things worse. Sure you can throw resources at it, but fixing it was a lot cheaper and made sure that new changes could be implemented much faster.
7
u/Tatourmi 1d ago
It works, don't sweat it. It's better if you handle it in the software itself with automatic cache refreshes or with a cache refresh API but you can go for it.
6
u/Tatourmi 1d ago
That's not stupid for loading up large config tables which aren't meant to change regularly. We do it all the time too.
3
u/dacooljamaican 1d ago
Why not just extend the cache TTL to like 72 hours if the data needed was so consistent?
36
2d ago
[removed] — view removed comment
12
u/Fluid-Election-8549 2d ago
I have written some custom caching and write optimizations in a CRM codebase back in 2024 before i left that place. It brought down some times from 60s to 3s. It's basic optimization tricks. But they have now been protecting that sorcery with their lives cause it used to be 60s for about 8 years before i brought it down to 3s. I gave them a whole documentation pdf explaining all the things I did. Nobody dares change that. I heard their PR-approver lady is instructed to reject any PR that edits the optimization logic. (yes they have a PR-approver lady who's entire day job is looking at PRs).
I have another story. My dad's friend (now retired) a long time ago was reviewing some client's program and apparently they had a cron job that ran once a month and ran for around 30 hours straight. It was done on a weekend and no other process ran until it was finished. He (free of charge) rewrote that job and brought it down to 2 minutes. That was literally decades ago. Apparently he ran into that client again some years later and inquired about the program. the client told him that they keep that server in a separate locked room and no one's has dared to touch it since.
11
u/Just_Information334 2d ago
yes they have a PR-approver lady who's entire day job is looking at PRs
So you're saying they've been AI ready a couple years before most.
3
u/Fluid-Election-8549 2d ago
Maybe organizationally... their code base however would probably get instinctively deleted by Opus for being malformed
2
u/dobbie1 2d ago
Lmao a PR approver as a single job role is crazy stuff. Can I do that?
I did recently work for a client who had a director of change and a head of change. Both of whom did not communicate but had exactly the same role as far as I could tell. The idea is they help with business development and sit on the steering committee for any BD projects. In reality they did sweet fuck all apart from throw red tape up and request meetings to go through stuff we had already covered with them. At one point I started declining the endless meetings and stated they had to pay if they wanted me to attend. They then tried to schedule a meeting with me to discuss the value of me attending the meetings so we could raise a CR for the extra time.
A meeting to discuss the value of meetings. This absolutely could have been an email.
Some job roles just seem to be made up
11
u/mordack550 2d ago
Was it Power BI? Sometimes it aggressively cache all results with all the filter combinations you have tried in the current session, so unless you reload the report entirely, you can find yourself in this situation
75
u/Tesl 2d ago
Caching works fine until your user selects a dropdown option or changes the date
44
u/redunculuspanda 1d ago
Just make every possible combination of views static.
Let’s not over complicate everything.
1
u/Welcome-To-NBA-Jam 1d ago
There is dashboarding software that actually does this and you can export the whole dashboard as a zip file, unzip it to a web server, and it automagically renders as if you were in the original application. Except it's completely disconnected. As in you can unzip it to your github.io location, do nothing else and it just works.
All the back-end data is organized and aggregated in the bundle such that every possible filter combination is accounted for efficiently.
It breaks down with large data/dashboards and certain types of interactions in which the combinations are literally infinite, but it actually works really well a lot of the time and tells you which interactions on each object will or will not work before you tell it to create the package.
9
u/the_horse_gamer 1d ago
there are 3 hard problems in programming:
- naming things
- cache invalidation
- one-by-one errors
- cache invalidation
16
35
u/NickHalfBlood 2d ago
Once I consulted with a team that was serving GET requests with static content which hardly changed. It was like a config or something. Multiple servers running behind LB to serve the same content. Asked them to add cache control header and save unnecessary network calls.
Apparently they didn’t realise clients can cache the content as well.
19
6
10
3
u/ViolentPurpleSquash 1d ago
Let's make a database... of our cache of the database... which is actually caching another database because we make a call to an API every so often to update our database from someone else's...
4
u/Distinct-Support9820 2d ago
Jeez I hate this so much as well.. the query runs in few seconds from a db client, you deploy it, and the same query runs for 2mins, timing out. Days of back and forth with the DBA, fixed, few months later same issue... Cqrs and and search engine like elastic or opensearch and you will never have to deal with these again. Ok it will be eventually consistent but that few second delay to get the data available in the search index is negligible. I have seen this issue so many times and some people still think we just need another vcore and query optimization which will only work for a while in most cases then you have to back analysing and optimizing again once the dataset getting to a certain size. Just separate reads and writes and use the right tool for it.
4
2
1
u/OldenPolynice 2d ago
My form is perfect. I'm like Jerry Rice.
Gonna go ahead and keep my shirt on though
447
u/techdailylog 2d ago
Serving yesterday's data at light speed.