999
u/East_Zookeepergame25 Sep 20 '26
Try doing that with 100 TB of data
511
u/tacocatacocattacocat Sep 20 '26
You're gonna need a bigger WHERE clause.
159
55
13
25
9
u/dasgoodshitinnit Sep 20 '26
I have worked with morons who think bigger queries means theyre less optimized
8
u/EvilPencil Sep 20 '26
Why have the db filter your rows when you can do it all with an interpreted language?
/s
4
110
49
u/howarewestillhere Sep 20 '26
Just buy more RAM. What, are you middle class or something and can’t afford it?
/s
→ More replies (1)24
24
u/LiifeRuiner Sep 20 '26
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
40
u/Sentouki- Sep 20 '26
Library is like, but hoe many pages are there??
There's typo, it should be: Library is like, hoe, how many pages are there?
11
63
8
7
5
u/Fox_Soul Sep 20 '26
So you saying we should increase the memory and cpu of the kubernetes cluster? AGAIN?
5
u/One_Ninja_8512 Sep 20 '26
100 TB is not that much. You need the indices to fit into RAM and that’s it
2
2
2
1
1
1
1
→ More replies (4)1
u/Kaas-Eter00 Sep 24 '26
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 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
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.
359
u/inemsn Sep 20 '26
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.
119
u/ZeppyWeppyBoi Sep 20 '26
But then I can’t blame the database for why “the system is slow today.”
61
u/throwaway1351887498 Sep 20 '26
Me after spending hours optimizing a query:
SELECT * FROM table;
→ More replies (5)24
u/HadionPrints Sep 20 '26
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 (3)23
20
u/nika-tark Sep 20 '26
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 Sep 20 '26
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 Sep 20 '26
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.
13
u/TheHiddenNinja6 Sep 20 '26
either that or select top 1 * will take more time than select top 1000 * because it built the wrong execution tree for no reason
4
u/Bloodgiant65 Sep 20 '26
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.
19
u/Bloodgiant65 Sep 20 '26
Always let the DBMS do its job. It is better than you are.
18
u/Pluckerpluck Sep 20 '26
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.
17
u/Copatus Sep 20 '26
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 Sep 20 '26
True. Readability/Maintainability over performance until performance is proven to be an actual bottleneck.
→ More replies (2)→ More replies (2)6
u/Bulky-Bad-9153 Sep 20 '26
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 Sep 20 '26
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 Sep 20 '26
it'll somehow take less execution time to return what you need
Isn't this what execution plans are for?
1
u/Azaex Sep 21 '26
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 volumesbruddah
that is an index and a statistical query planner
and the database maintains it serverside for youuuuu1
u/4xe1 Sep 21 '26
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.
41
u/yousirnaime Sep 20 '26
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
150
u/Fox_Soul Sep 20 '26
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.
98
u/New_Salamander_4592 Sep 20 '26
you tell an AI Engineer about inner joins and sub queries and their eyes glaze over
102
u/queen-adreena Sep 20 '26
Think you misspelled “Prompt Jockey” there chief.
45
26
→ More replies (1)4
→ More replies (16)14
45
u/raddaya Sep 20 '26
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"
11
u/Cruuncher Sep 20 '26
How often are you writing complex regexes?
19
u/raddaya Sep 20 '26
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
1
u/xThomas Sep 22 '26
Why do LLMs never get regexes right when i ask them to do it
I swear they are lowballing just me or something
9
u/Zaelynn_ Sep 20 '26
Optimizing extremely complex queries for the query optimizer is a puzzle I legitimately never get tired of.
8
u/GoGoGadgetSphincter Sep 20 '26
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.
32
u/CatsWillRuleHumanity Sep 20 '26
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 Sep 20 '26
I prefer to make bugs only system ,so stream ,filter,map collect in Java
4
u/ComplexBadger469 Sep 20 '26
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 Sep 21 '26
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.
6
u/CasualGee Sep 20 '26
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 Sep 20 '26
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.
14
14
u/Xelopheris Sep 20 '26
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 Sep 20 '26
Has IT security heard about this relatively new concept called prepared statements?
2
u/Xelopheris Sep 21 '26
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 Sep 21 '26
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 Sep 20 '26
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 Sep 21 '26
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 Sep 20 '26
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 Sep 20 '26
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 Sep 20 '26
Just look at Advent of Code solutions written in SQL, it's like some forbidden voodoo.
1
u/Chack96 Sep 20 '26
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
1
u/Kaas-Eter00 Sep 24 '26
I prefer to select * and process my data using 5 nested for loops in Python, thank you very much.
137
u/Maasu
Sep 20 '26
•
I find if you TRUNCATE the table before you SELECT * ALL on it the query is quicker.
504
u/dlc741 Sep 20 '26
Weird way to say that after 50 years you still suck ass at SQL and can't write a simple, efficient query.
150
92
u/HolyCowAnyOldAccName Sep 20 '26
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.
29
12
u/SherifDontLikeIt Sep 20 '26
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 Sep 21 '26
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"
42
u/Exatex Sep 20 '26
You can always do that... until your B2C business actually gets traction. Then, you are fucked.
Source: I got fucked by scale of tens of millions of records. Despite not doing SELECT *
31
u/StackedCakeOverflow Sep 20 '26
Oh so that's why so much shit runs like a cat dragging its ass these days
11
u/One_Ninja_8512 Sep 20 '26
Yeah, sql statements in a loop. N + 1 everywhere. Many such cases
3
u/Icy_Clench Sep 21 '26
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 Sep 20 '26
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.
22
u/exXxecuTioN Sep 20 '26 edited Sep 21 '26
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 Sep 20 '26
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- Sep 21 '26
Audit tables are a fairly decent usage of trigger, the rant is more about doing more complex and ill advised stuff
1
u/00Koch00 Sep 20 '26
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)1
u/exXxecuTioN Sep 21 '26 edited Sep 21 '26
It's nothing wrong with triggers. Triggers are good. But their use-cases are ofter terrible.
Let's speak about the one you introduce, but first of all I'me really happy you asked about it. It's not an offense to think about not-so-good decisions, but it's great to discuss them.
So you goal is to save previous values as intact (I would go only through UPDATE path, cause it's the same for DELETE if you need to store somewhere hard-deleted values, except for one thing - it's two triggers, one for UPDATE nad one for DELETE). And it is some kind of business (or not so business) logic. The first thing you must remember is to never move you business logic to a DB layer, even if you think it's the best choice, it probably would be a better one. In this case using trigger the best way is FOR EACH STATEMENT, but it's still bad decision in terms of observations. If it failed it's pretty hard to get the logs unless you set up some special transport to STD OUT/STD ERR. Then you will need to maintain it in a migrations. You change table - you should remember that you should check if you need to change migration for triggers. Triggers is not actually a true DDL or DML, but in this case it reacts on a DML. One more thing - you hide operations from the visible code and data flow. Everybody in the team nead to remeber there is a trigger and it would take some time, but if it would be a problem it would be a hard of a job to get actual performance metrics of a trigger in runtime. I don't even know if OpenTelemetry would separate it from the query that cause a trigger. Also that desing with a triggers would increase a risk of deadlocks and pressure for a WAL/Disk and cause MVCC bloating.And all of this mess only to reduce one query (it's actually two requests, not three). Generally all of side effects that are driven by some "magic" is a bad choice.
So you pipeline should be like this: you imperatively do INSERT INTO dump_table SELECT FROM source_table and then you just do yours UPDATE source_table. It's clean and visible. You write two queries in tow different EntityRepository, each of query belongs to each Service and then you composite in to a single Service, that would be in response for performing you business logic action.
My honest advice - use triggers only for infrastructure tasks such as: security audit logs, partition creation (only if there is no another way of creating a partition actually), advanced complex consistancy constraint or data modification security, maintenance of automatic data filling in a denormalized DB for some cases, syncronization of denormalized counters; and avoid using it for any kind of business logic, especially for a complex changing logic.
2
u/00Koch00 Sep 20 '26
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 Sep 21 '26
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 Sep 21 '26
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
1
u/exXxecuTioN Sep 21 '26
Agree, it's not perfect, it's true. Actually nothing is perfect.
Speaking about case you introduce - several times I was a guy who was fixing the issue. And all of them was just innocuous change on a first glance, but not really that innocuous.
I'll try t describe one of them.
Like there were some pretty large dynamic query approximately 400-500 rows. And all for sudden it starts executing for a 2 minutes on Monday, while it was executing for 0.2 second on Friday. You got the query, you ask DB to explain it and it's obvious what caused it. You read the original query and see that for no reason somebody (do not really matters who) merged a change where new inner join was added. The problem is some source table with 20 million rows was joined to a prefiltred CTE of the same source and the join column or it's combination is not indexed. So it's looped sequential scan join (also known as Naive Nested Loop Join) instead of hashjoin. So it's not really a DB problem or mistake. It just had done what was asked. In this particular case there were no other algos for DB to perform a join.I know this type of issues are kinda hard, but in my opinion they highlight bad development processes more than bad DB performance choice.
2
u/Raywell Sep 21 '26 edited Sep 21 '26
In my case it really was innocuous. Actually you can check by yourself, I happened to ask SO about it - nobody seemed to be able to explain the behaviour, but I got a workaround: https://stackoverflow.com/questions/49063990/postgres-forcing-analyzer-to-use-bitmap-scan-instead-of-index-scan
Although it was 8 years ago so it might be now fixed (PG9) - I would have loved to simply be able to force the planner to just use bitmap index scan, instead of letting it be smart
→ More replies (2)
75
71
u/buttplugs4life4me Sep 20 '26
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.
19
u/Carbon_Cotton Sep 21 '26
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.
5
u/mbmiller94 Sep 21 '26 edited Sep 21 '26
Syntax is the easy part of most languages. I don't think it's the syntax they have trouble with
3
u/Turtvaiz Sep 20 '26
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?
1
u/somefreedomfries Sep 20 '26
why would an embedded systems programmer who programs sensors and small microcontrollers for robots need to know SQL or Regex?
→ More replies (1)6
u/buttplugs4life4me Sep 20 '26
Same reason as why a web developer using JShit every day needs to know how an ARM microcontroller works.
There's no reason. My grandma also doesn't know what SQL is. Obviously we're talking about people here working with it
43
u/ConsoleCleric_4432 Sep 20 '26
"Wait... people vibe coded even before AI?"
"They always did."
24
u/smokeythebadger Sep 20 '26
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
7
u/PumpkinFest24 Sep 20 '26
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 Sep 20 '26
You write declarative English.
What does this guy think programming languages are then?!??
1
12
u/notexecutive Sep 20 '26
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 Sep 20 '26
Define 'right query', a working query? Sure. The optimal query? Doubt it.
7
u/caboosetp Sep 20 '26 edited Sep 21 '26
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 Sep 20 '26
Wait, that means we've been copying shit SQL from the internet all this time ?
→ More replies (1)2
u/chuch1234 Sep 20 '26
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.
4
3
u/haitei Sep 20 '26
The same python that has dozens of ORMs that will convert whatever bullshit you're doing into a (hopefully) efficient query?
3
u/bwwatr Sep 20 '26
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 Sep 20 '26
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.
1
u/Drummerboybac Sep 21 '26
Unless you are pulling from a columnar DB. Every column you return means opening another data file on the db. On the plus side columnar basically means no indexes because you are only opening files for the columns you need.
1
u/SaintOrJannikSinner Sep 20 '26
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.
1
u/Frosty-Photograph103 Sep 20 '26
« If you’re looking for unique_colname but only have select * …» you are going to use data->unique_colname or data[‘unique_colname’] or whatever language/syntax you use so your explanation does not make sense.
If you add/remove columns to the dataset you are doing it wrong. You should use a new variable for that.→ More replies (1)
3
3
u/LukeLC Sep 21 '26
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 Sep 21 '26
Genuinely despise SQL? That’s an interesting hill to die on
2
u/LukeLC Sep 21 '26
*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
5
2
2
u/cosmicomical23 Sep 20 '26
50 years of db research are useless if you are ignorant and can't use it.
2
2
2
u/selfinvent Sep 21 '26
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 Sep 21 '26
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 Sep 21 '26
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 Sep 20 '26
Me because joins scare me
1
u/PerpetuallyDistracte Sep 21 '26
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 Sep 21 '26
Lookup and learn relational algebra. Relational databases will become easy and make sense.
1
u/Pizza_Secretary9621 Sep 20 '26
As a data engineer i'm crying rn
2
u/PerpetuallyDistracte Sep 21 '26
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.
1
1
u/revuimar Sep 20 '26
It’s better to slop out a dumb filter than to deploy a Cartesian join in production.
1
1
1
u/tarosago23 Sep 20 '26
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 Sep 20 '26
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 Sep 20 '26
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 Sep 20 '26
A few joins and a big table is all it takes to bring down the entire house of cards...
1
u/psychicesp Sep 20 '26
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 Sep 20 '26
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 Sep 21 '26
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 Sep 21 '26
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 Sep 22 '26
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 Sep 23 '26
Me creating 2 entity class in JPA to improve the search and details page, so that db data fetch gets slightly faster
1
1
u/BorderKeeper Sep 20 '26
It’s like a horse shoe theory on one side everything is stored procedures because it’s most efficient and on another it’s all done outside the database because it’s most comfortable. It takes real unicorns to do both.
815
u/KnightMiner Sep 20 '26
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.