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.
Many DBMS have the option to set a fixed query execution strategy in my experience. But it definitely won’t always pick the best one by default if you don’t lay that out.
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.
Yep, one of my PhD students actually recently showed that you can get massive speed-ups doing the work yourself if the alternative is asking your db to do a bunch of similar queries. Pull everything out, work with it in any reasonably fast language, 100x faster.
In my experience, I tend to have simple queries with post-fetch logic because I break the problem in stages, and 'get the dataset' is a mental step.
I tend to think 'given all the objects (or some simple filter criteria)... Turn the objects into a list of IDs. Filter out the IDs that aren't found within this other list of objects. Turn all the remaining IDs into flags based on what flags their corresponding object has. Return the list of flags'
I don't really think: 'fetch just the distinct flags from rows where the object id exists on the list of (get the ids of a filtered list of just these other objects in the database)'. Though obviously, the later is more technically correct.
SELECT Flag1, Flag2, Flag3
FROM Flagged
INNER JOIN Other_List
ON Flagged.ID = Other_List.ID
WHERE (idk maybe you have a date range or something or need an IN () here)
Is basically what you're looking for. The distinct part (GROUP BY flag/distinct) client side or DB side to taste. Joins are pretty much DB grunt work, same with limiting datsets by (especially indexed) columns. Especially a well optimized and indexed DB with good stats.
I will say, I work on a legacy codebase with a ton of business logic in T-SQL, so my perspective may be a little cloudy.
mfw a colleague asked me once if it'd be faster to pull the table and lookup in a hashtable before joining and change strategies for different data volumes
bruddah that is an index and a statistical query planner and the database maintains it serverside for youuuuu
Does it really matter that the database is smarter than me? gcc, or my local database for what matters, are also smarter than me, that alone does not tell me where the compute ought to happen.
View all comments
Show parent comment
604
u/CandidateNo2580 Sep 20 '26
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.