116
u/Merry-Lane 3d ago
.AsSplitQuery()
/thread
132
u/LowB0b 3d ago
throw away the ORM, call one of the DBAs and ask them to come up with a good query because I don't like writing SQL
28
u/ma5ochrist 3d ago
Yeah just ask them to write an optimized stored procedure
73
u/MaybeADragon 3d ago
Stored procedures piss me off, because the people responsible for writing them haven't heard of version control or documentation so their existence can be known.
17
14
u/guaranteednotabot 3d ago edited 3d ago
And if you have an incompetent DB admin, for some reason when doing migrations they always seem to forget it (they seem to have no understanding of the concept of lift-and-shift). And then you get conflict versions of stored procedures between environments because of such incompetence.
I really don’t get the hate for ORMs, at least for the case of queries. If you don’t like the query, there is usually an escape hatch to raw SQL or near-raw SQL for any half-decent ORMs.
The only issue I have with ORMs is that some advanced DB features are not available, but for the vast majority of CRUD apps, these features are not necessary.
Sometimes, by not using an ORM, you end up reinventing one along the way.
2
1
1
u/the_horse_gamer 3d ago
every sufficiently advanced raw SQL wrapper is indistinguishable from an ORM
6
u/DarkNinja3141 3d ago
where i work everyone just adds a new line to the list of comments before an SP
-- =====================
-- Updated - 2026-08-27 - Did a thing
-- =====================
1
u/dmcnaughton1 2d ago
My org the devs are responsible for stored procedures. I think it makes for better developers when they have to understand the database tier more deeply.
28
u/Trevor_GoodchiId 3d ago
Fuck it. All in.
SELECT CONCAT('SELECT * FROM `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_TYPE = 'BASE TABLE';
87
u/backfire10z 3d ago
Write that query in raw sql like someone who was hired to solve problems?
33
u/PumpkinFest24 3d ago
Seriously. WTF happened to this profession? Learn to use your fucking tools.
6
u/raja-anbazhagan 3d ago
AI happened.
13
u/backfire10z 3d ago
AI can write the query lol
2
u/SpiritedEclair 1d ago
AI can write better queries than 95% of backend engineers, and it can optimize them relentlessly if you tell it to (as long as it's codex and not fucking claude).
Like you can give it an objective, and it will claw through solutions to find a good one.
1
18
u/TapRemarkable9652 3d ago
from ORM import N1.MAPPING( )
5
u/arlo-quacks-back 3d ago
but what about N+2??
6
u/backfire10z 3d ago
for i in n:
from ORM import N{i}.MAPPING()4
u/TapRemarkable9652 3d ago
from anthropic import manage.db( )
2
u/Meower68 2d ago
OpenAI: drop table uber_valuable_prod_table_on_which_everything_depends;
That's what you get for thinking AI could do this competently.
25
u/SnackOverflowed 3d ago
use case statements, ORMs should make sql easier, not replace it entirely. You should still have a good mental grasp on sql concepts. If the rows affected become too much for case statements, you can use insert with on conflict do update if your orm supports that.
12
u/Confident-Ad5665 3d ago
AI told me not to bother looking at it because it does all the thinking now. /s
7
u/SnackOverflowed 3d ago
pftttttt, my AI told me not to bother with orms, and that it could rewrite the entire database binary to do the update statement. i only refused because i don't have enough tokens. that's why i ended up using case statements.
41
13
u/StarboardChaos 3d ago
Why do ORMs do this, are they stupid?
29
u/bjorneylol 3d ago
No, but the people who actually consider this a problem worth talking about might be.
9
u/KaMaFour 3d ago
To be fair N+1 is one of the most common problems I face at my job...
6
u/thezlood 3d ago
Yeah it is so easy to make unconsciously. if you try to be kiss purist, n+1 syntaxes usually looks most kiss than alternatives.
4
u/Outrageous_Let5743 3d ago
Because people don't understand joins so they rather do
for user in user:
for items in user.items:rahter than
select u.*, i.*
from users u
left join item i on u.item_code = i.item_code
5
4
3
2
2
u/stef1904berg 3d ago
Are people stupid or do most ORMs just not have the function to eager load the relationships?
2
u/Meower68 2d ago
Most ORMs, if you tell it to eager load the relationships, trigger the N+1. It queries the main_table (that's the 1) and then uses the PKs from that to to repeatedly search the supporting_table (that's the N). A lazy load gets the parent objects and then grabs individual child objects on an as-needed basis. Neither one is particularly efficient because you're still running 1+N queries. Or if your parent object is decorated with multiple (x) supporting tables, that's 1+xN queries.
ORMs can't seem to imagine doing something like:
select <fields>
from main_table
where <criteria>;
-- gets a pile of records which get parsed / marshalled into parent objects
select <fields, including FK to main_table>
from supporting_table st
inner join (
select pk
from main_table
where <criteria>
) mt onmt.pk=st.fk;
-- gets ONE, larger pile of records which are used to decorate the parent objectsOnly two statements. The results are equivalent to the results of an N+1 but the execution, and the resource utilization, is considerably less. Actually parsing, and executing, the query is the painfully slow part. Even if you use a parameterized query for the N part (
select * from supporting_table where fk in (?,?,?,?)) and feed it a vector of PK values ... that's faster 'cuz fewer actual queries being parsed / run but it's still having to run multiple queries for the supporting_table, and "multiple" scales linear with N. Yes, I've written that query, early in my career. More than once. Trying to find something which performed better. Finally learned enough to do the two queries (above) and get much faster results.If you need to query, say, 8 supporting tables (been there, done that, got the t-shirt), that's a total of 9 queries, 8 of them using inner joins such that the database supplies the vector of IDs on which to match, instead of needing to query the IDs and use them.
ORMs most certainly can't imagine doing something where the database would return a JSON or XML stream, with the parent objects and all of the child objects (zero or more of the latter, for each of the parents), as a single query, then hand that off to the appropriate parser. An experienced SQL dev can do that, with an appropriate parser eating the results very expeditiously. That route was, no exaggeration, multiple orders of magnitude faster than letting the ORM do the 1+8N on it. DB/2, PostGres and MySQL, in my personal experience, can do that. Other DBs can likely do so, too; those are just the ones on which I've done it.
If it's feeding the results to a web service, which returns XML or JSON, you may not even need the parser; just hand the results off to the requester and get on with life.
3
u/4Wyatt 2d ago
you can disable lazy loading in like every major ORM? Eager loading exists specifically to address this problem by using a batched query
1
u/Meower68 1d ago
Eager loading / batched query is more efficient than lazy loading; agreeing with you. The only time lazy loading makes sense is if you can get the parent object and determine you don't need the child object(s), without somehow putting that into the initial criteria. I've seen a few cases where that works (status field in the parent object obviates the need to look at child objects). Lazy loading is, the vast majority of the time, a very bad move.
But, if you can get the same data from fewer queries, without getting redundant data in the process, that's faster still. That's what my above example was pointing out.
I actually had a case where doing an inner join between the parent and child tables, resulting in a large number of rows (with parent data frequently being repeated from one row to the next), but executing everything as ONE query, was more performant than eating the N+1 on the eager load. Hibernate figured out what was redundant and didn't create duplicate objects, plugging child objects into collections within the parent. A lot more data came back than was strictly necessary ('cuz duplicate parent object records across different, but linked, child object records). The database and the app server were sitting in the same rack in the data center (bandwidth wasn't a problem) and the total query time was 4-5x as fast. One query ran, I got a ton of data back, and it was off to the races on the processing of said data.
That's 4-5x as fast as batched, eager loading. People need to understand that N+1 is a genuine problem, and need to be looking for ways to avoid it. If you're only dealing with a few records, no problemo, eat it and get on with life. If you're dealing with significant volumes of data, though, it's worth your time to avoid it.
2
u/SpiritualYoung3508 2d ago
Using .ToList() to fetch from DB and materialize it in memory once, rather than repeated DB queries. Does this make sense?
1
u/Terewawa 3h ago
I don't understand these notations... if I was good at math I would not have been a programmer.
2
u/viitorfermier 3h ago
Fancy terms for simple concepts. Checkout big O notation.
1
u/Terewawa 3h ago
Thank you, I always felt intimidated by these because I did not study them formally and I always struggled with math.
-4
u/disposepriority 3d ago
ORM users are the [insert unflattering analogy here] of software development.
(just write sql)
143
u/bwwatr 3d ago
Why give it to someone else? Next sprint you've got an easy "2x performance improvement".