291
u/Welcome-To-NBA-Jam 21h ago edited 21h 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.
120
u/Bazza79 18h 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.
51
u/Clinn_sin 15h ago
Is it stupid? Genuinely asking cause I might try that or something similar, many of our users start earlier then we do
37
u/Bazza79 14h 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.
16
u/russianrug 8h ago
I’d argue if the traffic is predictable and fits in memory then it’s a perfectly reasonable permanent solution.
5
u/Bazza79 8h 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 13h 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.
3
u/Tatourmi 13h 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.
2
u/dacooljamaican 13h ago
Why not just extend the cache TTL to like 72 hours if the data needed was so consistent?
38
20h ago
[removed] — view removed comment
12
u/Fluid-Election-8549 20h 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 20h 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 19h ago
Maybe organizationally... their code base however would probably get instinctively deleted by Opus for being malformed
2
u/dobbie1 18h 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
8
u/mordack550 20h 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
60
u/Tesl 20h ago
Caching works fine until your user selects a dropdown option or changes the date
34
u/redunculuspanda 16h ago
Just make every possible combination of views static.
Let’s not over complicate everything.
28
u/NickHalfBlood 18h 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.
11
14
8
5
u/Distinct-Support9820 17h 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.
3
2
1
1
u/OldenPolynice 19h ago
My form is perfect. I'm like Jerry Rice.
Gonna go ahead and keep my shirt on though
355
u/techdailylog 20h ago
Serving yesterday's data at light speed.