3 ms·

Well the fact that the "n+1 query problem" is a fatal mistake ORMs force you to worry about, whereas you never had to worry about it with plain SQL, is pretty d

by kyllo 10y ago

Well the fact that the "n+1 query problem" is a fatal mistake ORMs force you to worry about, whereas you never had to worry about it with plain SQL, is pretty damning in and of itself.

zeta0134 10y ago
This is the first time I've heard of the N+1 query problem. I went and looked into my code and found 2 places where I had applied it, and have now measurably improved the performance of those pages. (Alas, if only I had enough traffic for it to make a real difference.) You really learn something new every day if you're observant. Thanks!
Lazare 10y ago
That's not entirely true; I actually ran into a problem today where we've got a (thankfully minor) performance issue due to a classic n+1 query problem involving an old non-ORM persistence layer with hand written SQL queries. There's nothing about plain SQL that means you "don't need" to worry about the n+1 queries, although I suppose the author of our authentication class apparently thought that way, because while we have a way of getting all objects from the DB, and we have a way of checking the DB to see if the user has permission to view an object, we don't have a way of getting all the objects a user can view. But at least all our queries use delightfully artisanal hand-crafted SQL! :) I think the better lesson is that if you're writing code that touches the DB, you need to be aware of what your code (and whatever libraries or frameworks you're using) are actually doing to the DB, with a vague idea of how DBs work. The real crime of ORMs is they hint (or sometimes outright promise) that the DB is being abstracted away and you no longer need to worry about indexes or joins or constraints, or even whether you're data is being persisted to a RDBMS or a NoSQL datastore. Literally: I ran across one ORM recently that used as a major selling point that you could use MySQL or MongoDB interchangeably with no changes in your app code and I just shuddered. So yeah, I'd say that the issue with ORMs isn't that they force you to worry about the n+1 query problem; it's that they encourage you not to worry about it. Everyone should be worried about it! :)
mdpopescu 10y ago
What if I was to tell you that the "n+1 query problem" only happens when you misuse ORMs? I know, "no true scotsman" and all that :) Ok... what if I was to tell you that you can get SQL injection with stored procedures? I worked for a company where we had that problem. I hadn't written them, but I knew that was impossible so I bet my boss that's not the reason. And then I looked at them and discovered they were doing something like s = "SELECT * FROM table WHERE id =" + @id and then exec_sql s (I don't remember the exact syntax, but it was something like that.) Misuse of what a stored procedure is supposed to do? Yeah, so's the "n+1 query problem" with ORMs... Though I will grant you that the second one is much easier to get wrong, the guy who wrote the SP had to work to make it vulnerable to injection...
vidarh 10y ago
The problem there is not understanding the abstraction. That's a general problem of most abstraction layers, and the solution is not avoiding abstraction but using a good one and learning how it works. Of course I realise lots of people will happily continue to use horrible ORMs and not learn how they work, but that's not a ORM specific problem.

by kyllo 10y ago

Well the fact that the "n+1 query problem" is a fatal mistake ORMs force you to worry about, whereas you never had to worry about it with plain SQL, is pretty damning in and of itself.

zeta0134 10y ago

This is the first time I've heard of the N+1 query problem. I went and looked into my code and found 2 places where I had applied it, and have now measurably improved the performance of those pages. (Alas, if only I had enough traffic for it to make a real difference.) You really learn something new every day if you're observant. Thanks!

Lazare 10y ago

That's not entirely true; I actually ran into a problem today where we've got a (thankfully minor) performance issue due to a classic n+1 query problem involving an old non-ORM persistence layer with hand written SQL queries. There's nothing about plain SQL that means you "don't need" to worry about the n+1 queries, although I suppose the author of our authentication class apparently thought that way, because while we have a way of getting all objects from the DB, and we have a way of checking the DB to see if the user has permission to view an object, we don't have a way of getting all the objects a user can view. But at least all our queries use delightfully artisanal hand-crafted SQL! :) I think the better lesson is that if you're writing code that touches the DB, you need to be aware of what your code (and whatever libraries or frameworks you're using) are actually doing to the DB, with a vague idea of how DBs work. The real crime of ORMs is they hint (or sometimes outright promise) that the DB is being abstracted away and you no longer need to worry about indexes or joins or constraints, or even whether you're data is being persisted to a RDBMS or a NoSQL datastore. Literally: I ran across one ORM recently that used as a major selling point that you could use MySQL or MongoDB interchangeably with no changes in your app code and I just shuddered. So yeah, I'd say that the issue with ORMs isn't that they force you to worry about the n+1 query problem; it's that they encourage you not to worry about it. Everyone should be worried about it! :)

mdpopescu 10y ago

What if I was to tell you that the "n+1 query problem" only happens when you misuse ORMs? I know, "no true scotsman" and all that :) Ok... what if I was to tell you that you can get SQL injection with stored procedures? I worked for a company where we had that problem. I hadn't written them, but I knew that was impossible so I bet my boss that's not the reason. And then I looked at them and discovered they were doing something like s = "SELECT * FROM table WHERE id =" + @id and then exec_sql s (I don't remember the exact syntax, but it was something like that.) Misuse of what a stored procedure is supposed to do? Yeah, so's the "n+1 query problem" with ORMs... Though I will grant you that the second one is much easier to get wrong, the guy who wrote the SP had to work to make it vulnerable to injection...

vidarh 10y ago

The problem there is not understanding the abstraction. That's a general problem of most abstraction layers, and the solution is not avoiding abstraction but using a good one and learning how it works. Of course I realise lots of people will happily continue to use horrible ORMs and not learn how they work, but that's not a ORM specific problem.