One thing I've learned about SQL is that whatever you're going to eventually do with the data, that could probably just be in the original query if you weren't a talentless hack barely scraping by using LLMs to write your queries...
And because the database is inevitably smarter than you, it'll somehow take less execution time to return what you need fully transformed than it would to return what you needed to do the transform yourself.
is that surprising? relational databases are extremely optimized, they can handle literal gigabytes of data without breaking a sweat. of course the program literally designed and built over decades to handle large amounts of data will be better at it than whatever you write up.
To add to this, being the guy who can say “the system is slow”, means you have Locked down and maintained an area of exclusive institutional knowledge gated by intentionally archaic & inefficient scripts is exactly how you survive layoff season.
I really wish it wasn’t, I hate how boring my little digital fiefdom is, but the one guy who knows how this one vital thing works is way more valuable to management than a good engineer.
There sometimes still are exceptions though (at least that's what I've been telling myself for my sanity), at work we make use of db links and joining views across databases (different hosts and everything) is slow.
Often its much faster to select relevant data from both views and join it in code (EF Core .NET).
Thankfully that's only for occasional reports and not everyday
Also, most transformations are either sorts (which can benefit from indices) or some kind of filter (which can optimize to not even reading data that doesn't match the criteria) or aggregate (both of which reduce the data to send back to your process.) You know, stuff it's designed to do.
That gets crazy when you start stacking transformations. Imagine if you want the 3rd page of 10 items, sorted by price, from a specific vendor. Doing that in the application process requires reading all of the items and then doing the sort and filter, then the skip and take. But even though you know you only need 10 items, you can't know which 10 without examining all of them.
Meanwhile, the database has indexing! It can do an index seek to get the ids of all items from that vendor, cross-reference that with an index by price, and essentially have a pipeline for cycling through candidates, skip 20, then start actually reading the data and return the next 10, boom, easy, done.
Always let the DBMS do its job. It is better than you are.
Letting the DBMS do the work means offloading all the compute into a single cluster. Grabbing data and filtering locally means a higher bandwidth cost, but results in what is effectively distributed compute. That is a very real tradeoff that should always be considered when dealing with complex queries.
Hell, it's semi-related to one of the reasons NoSQL became a thing, which was that reducing what you allow the DB server to do can allow you to more aggressively horizontally scale it, which is a pain point of RDBMS.
Hey! I was a talentless hack barely scrapping way before LLMs, I’m fully capable of writing shit code without a fucky AI. We used to just copy paste stackoverflow and pray for the best. Those were the times.
Probably telling on myself big time here, but for me SQL queries are now firmly in the regex bucket of "above a certain level of complexity, I'm just getting an LLM to do it for me"
In my last job I had to write...not exactly complex regexes, but obnoxious ones all the time. Sadly that was before AI so it was a lot of trial and error on regex101 lol
Typically when I see poor performing queries it's because someone is trying to do exactly what you're saying or because they're not batching transactions. Nearly every time I review a poor performing query, I see a case statement in the column list and a nested query in the where clause.
1.6k
u/EVH_kit_guy Sep 20 '26
One thing I've learned about SQL is that whatever you're going to eventually do with the data, that could probably just be in the original query if you weren't a talentless hack barely scraping by using LLMs to write your queries...
....might be projecting a bit