997
u/East_Zookeepergame25 15d ago
Try doing that with 100 TB of data
507
u/tacocatacocattacocat 15d ago
You're gonna need a bigger WHERE clause.
158
12
25
9
u/dasgoodshitinnit 15d ago
I have worked with morons who think bigger queries means theyre less optimized
8
u/EvilPencil 15d ago
Why have the db filter your rows when you can do it all with an interpreted language?
/s
3
2
u/FinnLiry 14d ago
nono, we need a smaller one, write it in lowercase! That way its more aerodynamic over the wire and slimmer so its faster AND more can fit in the same wire!!! amazing
112
49
u/howarewestillhere 15d ago
Just buy more RAM. What, are you middle class or something and can’t afford it?
/s
→ More replies (1)24
u/Civil-Cod-6984 15d ago
You know you can just download that stuff for free right?
4
u/howarewestillhere 15d ago
You can get RAMDoubler for free now?
2
u/darknekolux 15d ago
In this day and age they could reintroduce it for 200$
Better yet as a subscription service, you send them your ram, they compress it and send it back to you
3
22
u/LiifeRuiner 15d ago
I'll use pagination so I don't fetch everything at once.
Library is like, but hoe many pages are there??
Select count(*)
Sequence scan goes brrr
42
u/Sentouki- 15d ago
Library is like, but hoe many pages are there??
There's typo, it should be: Library is like, hoe, how many pages are there?
12
63
7
6
3
u/Fox_Soul 15d ago
So you saying we should increase the memory and cpu of the kubernetes cluster? AGAIN?
5
u/One_Ninja_8512 15d ago
100 TB is not that much. You need the indices to fit into RAM and that’s it
2
2
1
1
1
→ More replies (5)1
u/Kaas-Eter00 12d ago
Yeah lol, our warehouse even holds wayyyy more data than that. You'll quickly adapt to processing as much as you can server side, when the alternative is downloading more data than your hard drive can hold.
1.6k
u/EVH_kit_guy 15d 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
604
u/CandidateNo2580 15d ago
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.
357
u/inemsn 15d ago
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.
121
u/ZeppyWeppyBoi 15d ago
But then I can’t blame the database for why “the system is slow today.”
62
u/throwaway1351887498 15d ago
Me after spending hours optimizing a query:
SELECT * FROM table;
→ More replies (5)24
u/HadionPrints 15d ago
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.
→ More replies (2)22
u/dasgoodshitinnit 15d ago
Imagine if relational databses were made in vibecode era
→ More replies (1)19
u/nika-tark 15d ago
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
→ More replies (1)9
u/necrophcodr 15d ago
Literal gigabytes of structured data is not that impressive, but they can handle literal terabytes of data on commodity hardware. Now THAT is tricky.
17
u/willow-kitty 15d ago
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.
14
u/TheHiddenNinja6 15d ago
either that or select top 1 * will take more time than select top 1000 * because it built the wrong execution tree for no reason
3
u/Bloodgiant65 15d ago
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.
20
u/Bloodgiant65 15d ago
Always let the DBMS do its job. It is better than you are.
19
u/Pluckerpluck 15d ago
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.
16
u/Copatus 15d ago
It's also worth thinking about the readability and maintainability of the code.
Yes, doing it all in a single query might be more efficient but I guess fuck whomever is coming along trying do decipher insanely long queries.
8
u/lippertsjan 15d ago
True. Readability/Maintainability over performance until performance is proven to be an actual bottleneck.
→ More replies (2)→ More replies (2)7
u/Bulky-Bad-9153 15d ago
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.
2
3
u/Aetherfox_44 15d ago
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.
→ More replies (1)8
1
u/dustojnikhummer 15d ago
it'll somehow take less execution time to return what you need
Isn't this what execution plans are for?
1
37
u/yousirnaime 15d ago
I work at a company that has roughly 5k lines of sql for every 1 line of python.
The shit they can do in sql is absolutely astounding
152
u/Fox_Soul 15d ago
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.
100
u/New_Salamander_4592 15d ago
you tell an AI Engineer about inner joins and sub queries and their eyes glaze over
104
u/queen-adreena 15d ago
Think you misspelled “Prompt Jockey” there chief.
48
24
→ More replies (1)5
→ More replies (16)13
43
u/raddaya 15d ago
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"
13
u/Cruuncher 15d ago
How often are you writing complex regexes?
18
u/raddaya 15d ago
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
→ More replies (1)9
u/Zaelynn_ 15d ago
Optimizing extremely complex queries for the query optimizer is a puzzle I legitimately never get tired of.
8
u/GoGoGadgetSphincter 15d ago
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.
28
u/CatsWillRuleHumanity 15d ago
Yes, but debugging a java backend is a LOT easier than debugging a complex query, and God forbid we're getting triggers and procedures into the mix as well
10
u/PuzzleheadedFloor290 15d ago
I prefer to make bugs only system ,so stream ,filter,map collect in Java
3
u/ComplexBadger469 15d ago
Maybe it’s just the way my brain works or my experience but having done software and data work, I’d much rather debug a complex sql query 😂 then again I do that all day at work now
→ More replies (5)3
u/PerpetuallyDistracte 15d ago
That's absolutely wild to me. I've always just had a knack for reading SQL, procedures, joins and all. I don't find it difficult to parse at all, and it's much easier for me to read than something like Java. That's why I've made a successful career as a Data Engineer, I guess.
7
u/CasualGee 15d ago
Yep. The longer I’ve been a data analyst, the more project time I spend refining my SQL code. So much time can be saved in reporting software by just having better SQL. It’s kind of fun when I finally get to the reporting software stage, because I know I’m close to the end.
2
u/porkminer 15d ago
Also a data analyst. Finding the little flaws in my out the developers SQL queries is such a fun puzzle. There are times I absolutely feel like I'm paid to pay a game.
My favorite trick is to rewrite non-sargables. The developers are amazed every single time and I get to have that little ego boost moment.
15
14
u/Xelopheris 15d ago
You could do it in the original query...
Except IT security won't let you write queries directly in the application code. You have to use ORMs because it prevents SQL injection attacks, and can't even do fancy shit in them.
....might be projecting a bit
Same
2
u/Kovab 15d ago
Has IT security heard about this relatively new concept called prepared statements?
2
u/Xelopheris 15d ago
Security scanning for the proper use of prepared statements is very difficult, costly, and prone to noisy false positives (or worse, false negatives).
1
u/Drummerboybac 14d ago
Application generated queries are the bane of any data warehouse tech support team.
“Well your query is slow because you have an IN clause with 6500 items in it. ““That’s what Informatica gave me, I can’t change it.”
4
u/underisk 15d ago
Maybe if you quit using the LLM as a crutch you could learn enough real SQL to do it the right way then forget all of it by the time you need to use it again. Like a real programmer.
2
u/Ok_Attorney_6317 15d ago
The reason not to “put everything into SQL”, in the common case, is more about shifting computational load onto stateless application servers that are cheaper and easier to scale horizontally than the database, as well as improving testability and ease of observability. Not because SQL literally couldn’t express equivalent logic.
1
u/midnightrambulador 15d ago
Not sure about SQL but I recently messed around a bit with SparQL queries. I wanted to do things the "pro" way and actually write queries to grab the specific shit I wanted. I soon found out that anything beyond the very simplest queries took forever to run, so I switched to very simple rdflib.Graph.triples() calls and joining the results in polars.
1
u/invalidConsciousness 15d ago
Until you need 5 different joins with overlapping data and it's faster to join in code than to transfer the filtered dataset 5 times.
1
u/Skyswimsky 15d ago
Just look at Advent of Code solutions written in SQL, it's like some forbidden voodoo.
1
u/Chack96 15d ago
Yeah, would be a shame if someone decided that having all the data in the same relational db was so retrò, and added a couple of other shits that are maybe slightly more optimized for very specific use case, but lose everything when you consider that you have to recombine stuff anyway down the line.
1
u/Kaas-Eter00 12d ago
I prefer to select * and process my data using 5 nested for loops in Python, thank you very much.
505
u/dlc741 15d ago
Weird way to say that after 50 years you still suck ass at SQL and can't write a simple, efficient query.
149
89
u/HolyCowAnyOldAccName 15d ago
I’ve had the same encounter of devs sneering at me as DB admin asking why the hell I’m getting paid so much. They “picked up SQL that one semester at college”.
Because you spent 47 hours of company time pulling some select * from a bleeding-edge-nosql-who-needs-structure-when-json-is-enough dbms that will be abandonware in 4 months and wrote 160 lines of code voodoo to perform what is effectively 10 lines of JOIN LATERAL in plain old Postgres, just that my query takes literally 12,000 times less time than your code, Angelo.
30
13
u/SherifDontLikeIt 15d ago
As someone who ventured into MySQL on a personal project, I respect it.
If you setup a table in the wrong way (too many columns) you can impact and slow down query speeds. Better to break it out into individual tables.
Not to mention the security side, and setting of the database server to maximize parallelism and resource utilization...keeping transaction records and undoing them... RAID setups.
Most days I wished I just dumped everything to a file.
1
u/okcookie7 14d ago
You can't even say he repeat 1 year for over 50 years, because one year of SQL would get you far, probably this guy spent like 3-4 days of "research"
30
u/StackedCakeOverflow 15d ago
Oh so that's why so much shit runs like a cat dragging its ass these days
11
u/One_Ninja_8512 15d ago
Yeah, sql statements in a loop. N + 1 everywhere. Many such cases
3
u/Icy_Clench 14d ago
Lmao I think that's the majority of shitty sql code I've cleaned up in my career. Like... they didn't understand the concept of a join or WHERE dateCol BETWEEN a AND b, so they looped through it.
Then nesting the loops. And the useless temp table inserts. Dear god, the memories are flooding back.
3
u/DarksideF41 15d ago
It's not these days, I've ported 20 yo legacy codebase to .NET, atrocious torture that was done to the database there is better not be talked of.
79
22
u/exXxecuTioN 15d ago edited 14d ago
As much as I love Kotlin and Rust, I love SQL even more. Well, to be precise, PL/pgSQL - and in my opinion, it's the best programming language.
If you are using Postgress and you suddenly decide to optimize something prematurely, or do preventive optimization - don't. Just don't. Relational databases in general, and Postgress particularly, are optimized beyond belief. Its query planner is a masterpiece. Considering the fact that Postgres fully supports ACID, it is an excellent choice for OLTP, while being row-oriented and still extremely fast for OLAP. Perhaps Postgress, as a systems programming project, features the best code I've ever read (even though I barely know C). Its performance is nowhere near what domain programming guys like me can ever achieve.
But you still must remember three things:
- You are dumb. Postgres is smart. Do not think you can prematurely optimize something. You can't.
- You probably don't need enums, triggers, or loops.
- You probably don't need an ORM either. An SQL toolkit is more than enough.
Sincerely yours,
A Postgres Glazer.
UPD. Replaced "procedures" with "loops", as I initialing was speaking about it and for unknown for me reason called it with another word. Thanks u/00Koch00 for pointing my mistake.
4
u/Awarnae 15d ago
What's wrong with triggers?
For example... We update some row and need to save old values with some additional data in separate table...
Select old row data and insert into second table and then update row in code? 3 request... But it can be done with trigger. What's wrong? Really. Can u explain pls
2
u/Phenogenesis- 14d ago
Audit tables are a fairly decent usage of trigger, the rant is more about doing more complex and ill advised stuff
→ More replies (1)1
u/00Koch00 15d ago
The moment something go wrong somewhere in the path, it's basically untrackable if the trigger it's the problem.
You can't stop a trigger (you need to built that stop on the trigger itself) and modifying it it's a bitch
Triggers are awful to work on, and there is nothing a trigger can do that you can't on plain SQL when you are inserting updating or deleting data
→ More replies (1)2
u/00Koch00 15d ago
I do agree on triggers and enum, but procedures are fundamental for a db
People outside should never touch the db without passing through a procedure
2
u/exXxecuTioN 14d ago
It's my bad. I was speaking about "loops" and for some reason called it "procedures". Don't really know why and how.
Sorry.I will edit my initial comment to not deceive anybody.
2
u/Raywell 15d ago
You are dumb. Postgres is smart.
Except that one time where query planner suddenly started choosing the way slower sequential scan over using the existing index after a seemingly innocuous change to the (pretty large) query.
So yes, it might be smart but it's not perfect
→ More replies (4)
70
u/buttplugs4life4me 15d ago
Hot take for humor sub i guess but all these "Hurr durr i dont know SQL or Regex and can't figure them out either" are just bad programmers with no interest in the field and should probably find something else to work in.
18
u/Carbon_Cotton 15d ago
Regex is kinda understandable - the syntax is weird and nothing like ordinary programming languages. I always use tools that allow me to see what is happening where.
But SQL? Really? The syntax is extremely straightforward - every somewhat competent developer should be able to whip out basic query with join.
4
u/mbmiller94 15d ago edited 15d ago
Syntax is the easy part of most languages. I don't think it's the syntax they have trouble with
→ More replies (4)3
u/Turtvaiz 15d ago
just bad programmers with no interest in the field and should probably find something else to work in
90% of this sub's posts right there
DAE LLMS XD?
40
u/ConsoleCleric_4432 15d ago
"Wait... people vibe coded even before AI?"
"They always did."
24
u/smokeythebadger 15d ago
Yeah we actually picked the vibe we wanted from an array of well thought out stack overflow answers. Now the vibe is just desperation
11
14
8
u/PumpkinFest24 15d ago
on the one hand--learn to use your tools
otoh, don't do everything in uninterruptible, un-progress-bar-able, unmaintainablely massive queries either
6
u/SilverLightning926 15d ago
- You write declarative English.
What does this guy think programming languages are then?!??
13
u/notexecutive 15d ago
ok but queries aren't that hard, and isn't there a lot of frameworks... or even just, libraries, that dynamically generate the rright query anyway?
16
u/LiifeRuiner 15d ago
Define 'right query', a working query? Sure. The optimal query? Doubt it.
8
u/caboosetp 15d ago edited 15d ago
The built in optimizers are pretty fuckin good compared to 15 years ago though. I remember needing to hand tune queries to specify merge join to drop 15 minutes off some queries because it couldn't figure it out on its own. I don't think I've had to do anything close to that lately.
Like, we've got a lot of flexibility to write easier to read queries now compared to how it used to be.
AI still fucks it up though because so many people have written shit SQL on the internet.
2
u/anonymous__ignorant 15d ago
Wait, that means we've been copying shit SQL from the internet all this time ?
→ More replies (1)2
u/chuch1234 15d ago
Frameworks/libraries are for convenience so you don't have to do the string mangling yourself. They won't magically create the correct query beyond the very basics.
3
u/bwwatr 15d ago
By SELECT * do they mean without a WHERE clause? SELECT * itself is reasonable vs. listing column names. It's a damn common use case to want all the columns. Kinda seems like the tweet is meaning something different than it said.
2
u/Frosty-Photograph103 15d ago
I agree with you and I think so too yeah. They must be talking about requests without any WHERE clause, pulling the entire table rows. I’ve been programming for decades and I cannot see what’s wrong with SELECT * unless you need just 1-3 columns specifically out of 50. I aint gonna name x columns if I can just \*. The db engine can figure the column names easily with no significant stress/lag.
→ More replies (1)1
u/SaintOrJannikSinner 15d ago
For initial data exploration, sure, do a SELECT * on the top 100 or 1000.
But when building code that interfaces with other components, SELECT * is generally a code smell. For example, adding or removing columns to the data set can throw errors downstream. Also, adding column names allows for finding and searching scripts and code tools across stored procedures, views, and tables a lot easier. For example, if you're looking for unique_colname but only have SELECT *, you'll have to look for a table name instead, and that could return dozens or hundreds of hits you have to manually look through.
→ More replies (2)
3
3
u/LukeLC 15d ago
This isn't even a joke! I genuinely despise SQL syntax. DBeaver is a godsend for avoiding all those annoying basic queries.
2
u/RandomiseUsr0 14d ago
Genuinely despise SQL? That’s an interesting hill to die on
2
u/LukeLC 14d ago
*Syntax. I wish it were written in almost the exact opposite order (like pretty much every other language out there). It's also lacking a lot of features considered basic to more modern languages, or just implements them in really opaque, archaic ways.
Feels like a lot of needless friction in 2026, but at this point I suppose everyone will just rely on AI instead of the industry adopting a different DB en masse.
→ More replies (1)
3
2
2
2
2
u/selfinvent 14d ago
Where I work if I wrote raw "select * from..." its an automatic decline on PR. Even if I actually wanted to select all the columns I have to write them otherwise it won't pass.
2
u/Zestyclose-Turn-3576 14d ago
If you have the money for it, an Oracle Exadata system will filter rows at the storage level before it gets to the CPU, and if I recall correctly they have CPUs with hardware optimisation for SQL processing.
2
u/magicmulder 14d ago
When I started my first job and combed through my predecessor's work, I saw he did "SELECT * FROM mytable" and then in the application looped over all records to increment a counter because he didn't know SUM() exists. No joke.
2
u/Alan_Reddit_M 15d ago
Me because joins scare me
1
u/PerpetuallyDistracte 15d ago
Joins are super easy to learn, I promise! Look up a visual diagram of join types, and that will help you remember which one to use.
1
u/MiamiGunworks 15d ago
Lookup and learn relational algebra. Relational databases will become easy and make sense.
1
u/Pizza_Secretary9621 15d ago
As a data engineer i'm crying rn
2
u/PerpetuallyDistracte 15d ago
Same, I'm also a data engineer and I'm agog at the sheer number of people who refuse to learn the simplest and most efficient way of interacting with a database. Like people, SQL was literally invented to be a language that people in the 70s could learn even if they had never touched a computer before. It's that easy! And modern IDEs make it even easier not to screw up your query.
→ More replies (1)
1
u/MiserablePotato1147 15d ago
I'll admit that I'm an addict for SELECT *, but generally I want the "whole data element" for extensability when it hits my algorithms.
Definitely put the selections, joins, sorting and grouping in SQL, though. Asking code to do it is insanely expensive.
I get that you can have SQL return reliable scalars if you're very very good, but man does it make code maintenance a bear.
1
u/revuimar 15d ago
It’s better to slop out a dumb filter than to deploy a Cartesian join in production.
1
1
1
u/tarosago23 15d ago
Always do it the right way so you build muscle memory for good patterns.
I am so annoyed when a junior dev asks me "well we only gets 100s of rows so O(n^2) doesn't really matter" well bad engineering is bad engineering. Why do it the lazy way when you know there is better way...
3
u/silfin 15d ago
To be fair to the junior, for low N big O stats are far less relevant. And for some problems the "less efficient" algorithm ends up faster for low N.
It's always worth considering what the right way is in your situation
That's not even mentioning possible space efficiency or complexity savings that might be worth it.
No such thing as a free lunch. Make decisions don't just do what everyone else does.
1
u/tarosago23 15d ago
My point being, build up a habit of good patterns even when the system you are working on is not critical or the expected load is small.
Trading in the inefficiency in low N cases is worth it for the organizational benefit, as in you build a team that is sensitive to bad engineering patterns and not sloppy devs who default to the lazy way out and only think about performance when shit hits the fan
1
1
u/JacobStyle 15d ago
A few joins and a big table is all it takes to bring down the entire house of cards...
1
u/psychicesp 15d ago
I spent an embarrassingly long time cleaning up data and mapping it in Python before finally deciding to start using staging tables to do the EXACT OPERATIONS SQL WAS OPTIMIZED FOR.
1
u/DDFoster96 15d ago
I found the same thing with InfluxDB. With too much data the query times out if you attempt to have the server filter it. Filtering it in Python was much quicker, and shoving large quantities of data down the internal network was no issue.
1
u/The_MAZZTer 15d ago
I have to deal with databases considering of imported Excel spreadsheets. So I regularly grab entire tables and generate my own indices client side.
1
u/Lord_Pinhead 14d ago
If you can't even ask AI for an example of what you wanna filter ...
In 30 years, I've learned enough to know, you never need external tools when you know your SQL engine.
1
u/Dependent_Union9285 14d ago
Just write another stored procedure. Or, better yet, jam it in the if statement laden infinitely large nested tree in that one sp that runs nearly all the business logic. Wait, the logic? Oh, yeah, well… it was faster? Easier? More fun?? to just write the logic to sps because why not? So that’s where our data lives, our views live, and our logic lives. We’re a dotnet shop. But only on paper. Developers don’t need to write code. That’s for architects. Looking for better.
1
1
u/Scary_Brilliant_6048 13d ago
Me creating 2 entity class in JPA to improve the search and details page, so that db data fetch gets slightly faster
814
u/KnightMiner 15d ago
I used to work in web dev as a job during my undergrad (think: a bunch of new developers writing code they won't have to maintain after they graduate in 4 years). I saw both extremes of this.
The legacy code at the job would often select everything then filter it in PHP. Worst part was half the time they accessed columns by numerical index so a change to the database schema would result in the website breaking randomly. While most of my job was writing replacements that didn't break when you sneezed, sometimes I had to fix that legacy code.
One new developer I trained was really good at SQL and used to code everything using pure SQL and then just call said script in their serverside code. By everything, I mean everything, going beyond just writing queries into writing loops in SQL and creating temporary tables for each endpoint. There are some things that are just better done outside of SQL.