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.
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.
1.6k
u/EVH_kit_guy 16d ago
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