10 ms·

Understanding the N + 1 queries problem

111 points by ananthakumaran 4y ago

keltex 4y ago
I don't know Rails Active Record. But if the ORM is anything like others I am more familiar with (Django / Python or Linq / C#) can't you do a join and just have a single query? Or use raw SQL if performance is an issue?
- deleted 4y ago
  [deleted]
- n_e 4y ago
  I don't know ActiveRecord either, but it appears so https://guides.rubyonrails.org/active_record_querying.html#joining-a-single-association https://guides.rubyonrails.org/active_record_querying.html#j...
- acjohnson55 4y ago
  The problem tends to come up when you pass models around and subsequent logic traverses relationships. It's fundamentally an issue of treating Active Record-style models as though they were in-memory objects.
  - jbverschoor 4y ago
    No it's not. If one forehand you do not know which "object graph" you need, you'll run in this problem no matter what the underlying tech is. At least some with ORMs you can specify afterwards (when passing the activerecords) that you want to have prefetched (inner or outer joins, caching). Sometimes it's done automatically, because you can simply detect when you're in an N+1 query loop.
samwillis 4y ago
Something interesting to consider with N+1 queries, these warnings only really apply to remote database servers, as in not on the same machine. If you are using SQLite, or another in process database, N+1 isn't an issue at all. So with the increased use of SQLite as an "edge" database it's something to consider. "Many Small Queries Are Efficient In SQLite": https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
- n_e 4y ago
  Although the problem will be a lot less severe than with remote servers, this is still sub-optimal: - the data passed from one query to the next still needs to move from the database process to the service process and back - the queries will always be executed in the order they are in the code, denying the optimizer the opportunity to execute the full query in the best order
  - jerf 4y ago
    There is still also non-zero overhead associated with making queries in general, in both the querying and query-answering process. The ceiling of the range where you can get away with this without user-visible performance impact will be much higher, and the relative performance difference may be smaller, but in general fewer queries for the same data will still be better in general. Even with an in-process DB, you're still essentially making a sort of context switch.
    - eatonphil 4y ago
      Piling on about overhead (and SQLite), many high-level languages take some hit for using an FFI. So you're still incentivized to avoid tons of SQLite calls. https://github.com/dyu/ffi-overhead https://github.com/dyu/ffi-overhead
  - Arch-TK 4y ago
    With SQLite there is no "database process" unless you are explicitly only using a dedicated process to make the queries through which isn't necessary with SQLite anyway. That being said, the problem here is not whether N+1 is a problem or not, but rather if, given the immense amount of unnecessary complexity that using an ORM brings, it is appropriate to use an ORM.
  - davnicwil 4y ago
    Sub optimal in one regard, but if segmenting queries makes for simpler, easier to read and easier to debug code, then you're optimising dev time. Often this is the right tradeoff to make.
adamzapasnik 4y ago
This is what I struggle a lot with in Rails. No good, community backed serialisation gem. AMS is a mess, other ones are not maintained. And I'm not a fan of JSON api spec's serialisation either. But also AR doesn't have any easy tools to construct complex queries/multi queries. It works for basic and medium stuff, but even this very common count problem is a disaster to deal with. Sure. you can use Arel and some other gems, but these aren't good solutions for someone that wants to get things done. Makes me wonder how others deal with these problems tbh.
- bhaak 4y ago
  IME if ActiveRecord is not sufficient, you go directly to SQL. find_by_sql gives you enough freedom to get everything out of the db into a ActiveModel object.
- jbverschoor 4y ago
  bs, there are a few very good serialization gems. ActiveRecord and Hibernate/JPA work a lot better than concatenating your own SQL strings. If there's something that really doesn't benefit from your model (reports), then you'd fallback to either SQL, or still use ActiveModel + the aggregates
  - adamzapasnik 4y ago
    Please list the good serialisation gems, as I can't find any ;) What if you have a complex/dynamic query, how do you build it? You said yourself that AR works better than concatenating SQL strings, but AR doesn't even support CTEs atm and building complex queries is not trivial and sometimes even possible without just SQL strings...
    - rurabe 4y ago
      Big fan of raw sql, but practically speaking (as it relates to developing with rails) CTEs can be rewritten as subqueries, the advantage being that they are linear instead of nested in SQL. With AR queries you can do the same and make it linear in ruby (and then the computer doesn't really care if your sql is nested) last_three_posts = Post.limit(3).order(created_at: :desc) @posts = Comment.where(post_id: last_three_posts)
simonw 4y ago
There's another option with many databases these days: you can often use aggregation functions to return the related data as part of a single query, even across many-to-many tables. I wrote up how to do that using JSON aggregation functions in both SQLite and PostgreSQL for example: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...
- panzerboiler 4y ago
  How would you add also the count of the votes of each comment in the aggregation, as per the example in the article?
  - deleted 4y ago
    [deleted]
  - simonw 4y ago
    Lots of ways to do that, one way would be using a CTE like this one: https://lite.datasette.io/?install=datasette-pretty-json&sql=https://gist.githubusercontent.com/simonw/3d6cbcd55beda108a88265a80f042726/raw/06ac070945874bdfe78442973ae76240cd8de370/posts_comments_votes.sql#/data?sql=with+comment_vote_counts+as+%28%0A++select%0A++++comment_id%2C%0A++++count%28*%29+as+vote_count%0A++from%0A++++votes%0A++group+by%0A++++comment_id%0A%29%2C%0Acomments_with_vote_counts+as+%28%0A++select%0A++++id%2C%0A++++post_id%2C%0A++++content%2C%0A++++coalesce%28vote_count%2C+0%29+as+votes%0A++from%0A++++comments%0A++++left+join+comment_vote_counts+on+comments.id+%3D+comment_vote_counts.comment_id%0A%29%0Aselect%0A++posts.id%2C%0A++posts.title%2C%0A++posts.content%2C%0A++json_group_array%28%0A++++json_object%28%0A++++++%27id%27%2C%0A++++++comments_with_vote_counts.id%2C%0A++++++%27content%27%2C%0A++++++comments_with_vote_counts.content%2C%0A++++++%27votes%27%2C%0A++++++comments_with_vote_counts.votes%0A++++%29%0A++%29+as+comments%0Afrom%0A++posts%0A++join+comments_with_vote_counts+on+comments_with_vote_counts.post_id+%3D+posts.id%0A++group+by+posts.id https://lite.datasette.io/?install=datasette-pretty-json&sql... with comment_vote_counts as ( select comment_id, count(*) as vote_count from votes group by comment_id ), comments_with_vote_counts as ( select id, post_id, content, coalesce(vote_count, 0) as votes from comments left join comment_vote_counts on comments.id = comment_vote_counts.comment_id ) select posts.id, posts.title, posts.content, json_group_array( json_object( 'id', comments_with_vote_counts.id, 'content', comments_with_vote_counts.content, 'votes', comments_with_vote_counts.votes ) ) as comments from posts join comments_with_vote_counts on comments_with_vote_counts.post_id = posts.id group by posts.id
acjohnson55 4y ago
I can't help but feel this problem is indicative of incidental complexity in how we develop web applications. Not saying the PHP glory days were better, but there's something to be said for removing the layers of abstraction between the data and the presentation. Make the database query using SQL directly, and then inject the results into the HTML template to be delivered to the browser. Obviously, there were many issues here, like how easy it was to leave applications open to SQL injection attacks. But it has been interesting to see the tide turn back towards server-side rendering, relying on partial DOM replacement for client-side updates. For web apps that don't have massive numbers of UI states (like a document editor), it seems like people are rethinking the wisdom thick client-side JavaScript applications, which seem to be one of the main motivators for REST API layers, and the need to efficiently fulfill N+1 queries. Although, I do remember dealing with the N+1 problem when doing Django server-side apps more than a decade ago, before the dominance of client-side apps. I guess it was more the rise of MVC architecture and the active record pattern (https://en.wikipedia.org/wiki/Active_record_pattern https://en.wikipedia.org/wiki/Active_record_pattern) that brought the N+1 problem, more so than client-side apps.
- Arch-TK 4y ago
  This is specifically just a problem with all ORMs. They attempt to solve the object-relational mismatch and fail because these two concepts (sets of tuples and graphs) are completely orthogonal.
  - jbverschoor 4y ago
    No it's not.. you can and will easily have the same problem with SQL if you're not fetching beforehand.. I bet wordpress (or many plugins) is/are so slow exactly because of this. ORMs will even make it easier to write more efficient queries is many cases.
    - hinkley 4y ago
      There are too many questions to make any absolute statements. You hint at a couple here. Specialization of requests reduces data transport, but increases testing surface area and drastically reduces caching options - including within the database. Years ago we discovered that our bottleneck in Oracle was being caused by query cache evictions (worse, in 9i only queries currently in the cache could run, so new queries were blocking in the query planner. I'm not sure if this was ever fixed.) One of the important uses of etags within your infrastructure is to allow you to incorporate data manipulation into your caches. If your functions are deterministic you can store the results of a calculation, instead of the source data for that calculation. Cache hits then don't just save bandwidth, they also save computation, which reduces the sequential parts of the response, fighting Amdahl's Law.
henning 4y ago
[flagged]
- Nextgrid 4y ago
  You're dismissing ORMs because of one (well-understood and easy to mitigate) downside without considering the upsides. ORM do make development much easier and are fine in the majority of cases to begin with. For the edge-cases, some can be addressed by giving the ORM extra hints about your intentions (such as for the N+1 queries problem) and those that can't can be rewritten as raw SQL. Why dismiss them entirely in favour of Raw SQL if you can get the best of both worlds by picking the appropriate tool for the job?
  - Arch-TK 4y ago
    If you simply don't attempt to shoehorn your data into a graph structure then you don't need an ORM in the first place. ORMs only make development easier insofar as you insist on turning your relational data into graphs. In all instances I've found that raw SQL or a good query builder (not an ORM, e.g. SQLAlchemy core) and an approach to handling the data as it is (relational) and not as you might want to pretend it to be (graph) is sufficient to ensure the resulting software is easier to develop and maintain. In situations where I have needed graph data I have found graph databases to work well there too.
  - dakiol 4y ago
    How is the example given in the post an "edge" case? All except the simplest domain models include these kind of relationships (and more complex ones). The Post->Comment->Vote pattern is practically everywhere as soon as you are modeling real-world scenarios.
    - isbvhodnvemrwvn 4y ago
      Solving those issues takes you SIGNIFICANTLY less time than mapping SQL results in any moderately complex relation graph.
- kstrauser 4y ago
bfung 4y ago
> breadth-first loading. The ideal solution requires us to load the data in a breadth-first approach, but unfortunately, this is harder to write because it does not compose well. The author finds the simplest and efficient solution, but continues to over engineer for blog content :P “Composing” is overrated in this case.
- ananthakumaran 4y ago
  This is a contrived example, probably not a real-world use case. The kind of issues I am dealing at work is much more complicated, usually involves more than 3 or 4 tables, serializers are referred by multiple other serializers etc. There is usually business logic involved as well in the query construction. I understand things can be improved, but it's not as simple as writing few queries by hand. ActiveRecord/ActiveModelSerializer provides good composability, but fails to handle N + 1 queries optimally, which is what I am trying to explain.
ydnaclementine 4y ago
Would this not be solved with adding `votes` to the `includes`? Something like: ``` Post.includes(comments: :votes) ``` Similar stackoverflow: https://stackoverflow.com/a/24397716 https://stackoverflow.com/a/24397716
- ramchip 4y ago
  Exactly this. Combine with Bullet[1] to detect problems early. [1] https://bhserna.com/tools-to-help-you-detect-n-1-queries.html#bullet https://bhserna.com/tools-to-help-you-detect-n-1-queries.htm...
- rurabe 4y ago
  The problem here is that you are loading all the votes as AR instances which is fine at small scale, but as your app gets larger, loading and instantiating thousands of Vote instances just to then break them down into an integer will start to drag on your controller. If you can count in the database itself it's a big win. Although no doubt your solution is cleaner code.
kstrauser 4y ago
I really wish this been originally called the “1+N problem”, not “N+1”. That naming makes it much clearer to me.
- pharmakom 4y ago
  Or even the “1 then N problem” since we determine the N from the 1.
- Izkata 4y ago
  I swear that's what it was called when I was first introduced to it a decade ago. When I first saw one of the posts focused on it on here I didn't initially recognize it as referring to the same thing.
  - kstrauser 4y ago
    I don't remember how I heard it originally, but I wouldn't have recognized it as the same, either. To me, "N+1" implies you're already doing N queries and now you're running 1 more. That's a different class of problem than "you were running 1 query, and now you're running more than 1."
TexanFeller 4y ago
Understand N+1 before you try GraphQL.
saila 4y ago
You could probably get this down to two queries, one for posts and one for comments, if you aggregate the vote count when retrieving the comments. I think this is pretty easy to do with most ORMs. You could also get it down to 1 query using SQL. This is one way to do it based on the schema in the article [postgres, not well tested]: with latest_posts as ( select * from post limit 3 ), latest_comments as ( select c.*, count(v.id) as votes from comment c left join vote v on v.comment_id = c.id where c.post_id in (select id from latest_posts) group by c.id, c.content ) select p.*, json_agg(c) from latest_posts p left join latest_comments c on c.post_id = p.id group by p.id, p.title, p.content # NOTE: fixed SQL bug noted by @rurabe Off the top of my head, I'm not sure how you would (or if you could) do this with ActiveRecord, SQLAlchemy, or the Django ORM, but it's probably more complicated than just writing the SQL. To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL.
- gnuvince 4y ago
  > To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. When I did web development, I saw it as a "hack" and a "failure to write clean code" whenever I reached for raw SQL. This is of course not true at all, but it was a powerful psychological blocker and I'd spend too much time trying to figure how to get the ORM to do what I wanted instead of writing the SQL myself and moving on to the next problem.
- joshuahedlund 4y ago
  > but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. I agree. I often feel like I benefited by starting my web career pre-ORM and only learning to use them a few years in, so I can appreciate and use both. I sometimes wonder if it’s harder for new devs to acquire the same kind of experience.
- pphysch 4y ago
  It's pretty straightforward in Django. The key is being comfortable with writing custom Manager/QuerySet methods. You could do something like `Post.objects.latest().annotate_comments()` which would resolve almost exactly to the query you wrote above.
pmg102 4y ago
We solved the N+1 queries problem where I work by raising the level of abstraction from "queries plus serialisation" to "what shape data is required". We open sourced the solution at https://www.django-readers.org/ https://www.django-readers.org/.
eloisius 4y ago
I wish I'd had an opportunity to use Phoenix in production before I got out of web dev, because the way the Ecto ORM obviated this entire class of error was beautiful. Instead of lazy loading, there's a neat grammar for preloading the entire graph of related records that you want.
pharmakom 4y ago
This comes up in GraphQL, not just ORMs. A beautify solution is Facebook’s Haxl. Less beautiful is data-loader.
- eezing 4y ago
  Correct. While data-loader facilitates data loading, type resolvers in GraphQL is where the solution starts.
funnyfoobar 4y ago
Somewhat deviating, but relavent. If we use counter cache that is to keep vote_count on comments table, the include(:comments) solution would work fine. https://scoutapm.com/blog/how-to-start-using-counter-caches-in-rails https://scoutapm.com/blog/how-to-start-using-counter-caches-...

I don't know Rails Active Record. But if the ORM is anything like others I am more familiar with (Django / Python or Linq / C#) can't you do a join and just have a single query? Or use raw SQL if performance is an issue?

deleted 4y ago
[deleted]
n_e 4y ago
I don't know ActiveRecord either, but it appears so https://guides.rubyonrails.org/active_record_querying.html#joining-a-single-association https://guides.rubyonrails.org/active_record_querying.html#j...
acjohnson55 4y ago
The problem tends to come up when you pass models around and subsequent logic traverses relationships. It's fundamentally an issue of treating Active Record-style models as though they were in-memory objects.
- jbverschoor 4y ago
  No it's not. If one forehand you do not know which "object graph" you need, you'll run in this problem no matter what the underlying tech is. At least some with ORMs you can specify afterwards (when passing the activerecords) that you want to have prefetched (inner or outer joins, caching). Sometimes it's done automatically, because you can simply detect when you're in an N+1 query loop.

samwillis 4y ago

Something interesting to consider with N+1 queries, these warnings only really apply to remote database servers, as in not on the same machine. If you are using SQLite, or another in process database, N+1 isn't an issue at all. So with the increased use of SQLite as an "edge" database it's something to consider. "Many Small Queries Are Efficient In SQLite": https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html

n_e 4y ago
Although the problem will be a lot less severe than with remote servers, this is still sub-optimal: - the data passed from one query to the next still needs to move from the database process to the service process and back - the queries will always be executed in the order they are in the code, denying the optimizer the opportunity to execute the full query in the best order
- jerf 4y ago
  There is still also non-zero overhead associated with making queries in general, in both the querying and query-answering process. The ceiling of the range where you can get away with this without user-visible performance impact will be much higher, and the relative performance difference may be smaller, but in general fewer queries for the same data will still be better in general. Even with an in-process DB, you're still essentially making a sort of context switch.
  - eatonphil 4y ago
    Piling on about overhead (and SQLite), many high-level languages take some hit for using an FFI. So you're still incentivized to avoid tons of SQLite calls. https://github.com/dyu/ffi-overhead https://github.com/dyu/ffi-overhead
- Arch-TK 4y ago
  With SQLite there is no "database process" unless you are explicitly only using a dedicated process to make the queries through which isn't necessary with SQLite anyway. That being said, the problem here is not whether N+1 is a problem or not, but rather if, given the immense amount of unnecessary complexity that using an ORM brings, it is appropriate to use an ORM.
- davnicwil 4y ago
  Sub optimal in one regard, but if segmenting queries makes for simpler, easier to read and easier to debug code, then you're optimising dev time. Often this is the right tradeoff to make.

adamzapasnik 4y ago

This is what I struggle a lot with in Rails. No good, community backed serialisation gem. AMS is a mess, other ones are not maintained. And I'm not a fan of JSON api spec's serialisation either. But also AR doesn't have any easy tools to construct complex queries/multi queries. It works for basic and medium stuff, but even this very common count problem is a disaster to deal with. Sure. you can use Arel and some other gems, but these aren't good solutions for someone that wants to get things done. Makes me wonder how others deal with these problems tbh.

bhaak 4y ago
IME if ActiveRecord is not sufficient, you go directly to SQL. find_by_sql gives you enough freedom to get everything out of the db into a ActiveModel object.
jbverschoor 4y ago
bs, there are a few very good serialization gems. ActiveRecord and Hibernate/JPA work a lot better than concatenating your own SQL strings. If there's something that really doesn't benefit from your model (reports), then you'd fallback to either SQL, or still use ActiveModel + the aggregates
- adamzapasnik 4y ago
  Please list the good serialisation gems, as I can't find any ;) What if you have a complex/dynamic query, how do you build it? You said yourself that AR works better than concatenating SQL strings, but AR doesn't even support CTEs atm and building complex queries is not trivial and sometimes even possible without just SQL strings...
  - rurabe 4y ago
    Big fan of raw sql, but practically speaking (as it relates to developing with rails) CTEs can be rewritten as subqueries, the advantage being that they are linear instead of nested in SQL. With AR queries you can do the same and make it linear in ruby (and then the computer doesn't really care if your sql is nested) last_three_posts = Post.limit(3).order(created_at: :desc) @posts = Comment.where(post_id: last_three_posts)

simonw 4y ago

There's another option with many databases these days: you can often use aggregation functions to return the related data as part of a single query, even across many-to-many tables. I wrote up how to do that using JSON aggregation functions in both SQLite and PostgreSQL for example: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...

panzerboiler 4y ago
How would you add also the count of the votes of each comment in the aggregation, as per the example in the article?
- deleted 4y ago
  [deleted]
- simonw 4y ago
  Lots of ways to do that, one way would be using a CTE like this one: https://lite.datasette.io/?install=datasette-pretty-json&sql=https://gist.githubusercontent.com/simonw/3d6cbcd55beda108a88265a80f042726/raw/06ac070945874bdfe78442973ae76240cd8de370/posts_comments_votes.sql#/data?sql=with+comment_vote_counts+as+%28%0A++select%0A++++comment_id%2C%0A++++count%28*%29+as+vote_count%0A++from%0A++++votes%0A++group+by%0A++++comment_id%0A%29%2C%0Acomments_with_vote_counts+as+%28%0A++select%0A++++id%2C%0A++++post_id%2C%0A++++content%2C%0A++++coalesce%28vote_count%2C+0%29+as+votes%0A++from%0A++++comments%0A++++left+join+comment_vote_counts+on+comments.id+%3D+comment_vote_counts.comment_id%0A%29%0Aselect%0A++posts.id%2C%0A++posts.title%2C%0A++posts.content%2C%0A++json_group_array%28%0A++++json_object%28%0A++++++%27id%27%2C%0A++++++comments_with_vote_counts.id%2C%0A++++++%27content%27%2C%0A++++++comments_with_vote_counts.content%2C%0A++++++%27votes%27%2C%0A++++++comments_with_vote_counts.votes%0A++++%29%0A++%29+as+comments%0Afrom%0A++posts%0A++join+comments_with_vote_counts+on+comments_with_vote_counts.post_id+%3D+posts.id%0A++group+by+posts.id https://lite.datasette.io/?install=datasette-pretty-json&sql... with comment_vote_counts as ( select comment_id, count(*) as vote_count from votes group by comment_id ), comments_with_vote_counts as ( select id, post_id, content, coalesce(vote_count, 0) as votes from comments left join comment_vote_counts on comments.id = comment_vote_counts.comment_id ) select posts.id, posts.title, posts.content, json_group_array( json_object( 'id', comments_with_vote_counts.id, 'content', comments_with_vote_counts.content, 'votes', comments_with_vote_counts.votes ) ) as comments from posts join comments_with_vote_counts on comments_with_vote_counts.post_id = posts.id group by posts.id

acjohnson55 4y ago

I can't help but feel this problem is indicative of incidental complexity in how we develop web applications. Not saying the PHP glory days were better, but there's something to be said for removing the layers of abstraction between the data and the presentation. Make the database query using SQL directly, and then inject the results into the HTML template to be delivered to the browser. Obviously, there were many issues here, like how easy it was to leave applications open to SQL injection attacks. But it has been interesting to see the tide turn back towards server-side rendering, relying on partial DOM replacement for client-side updates. For web apps that don't have massive numbers of UI states (like a document editor), it seems like people are rethinking the wisdom thick client-side JavaScript applications, which seem to be one of the main motivators for REST API layers, and the need to efficiently fulfill N+1 queries. Although, I do remember dealing with the N+1 problem when doing Django server-side apps more than a decade ago, before the dominance of client-side apps. I guess it was more the rise of MVC architecture and the active record pattern (https://en.wikipedia.org/wiki/Active_record_pattern https://en.wikipedia.org/wiki/Active_record_pattern) that brought the N+1 problem, more so than client-side apps.

Arch-TK 4y ago
This is specifically just a problem with all ORMs. They attempt to solve the object-relational mismatch and fail because these two concepts (sets of tuples and graphs) are completely orthogonal.
- jbverschoor 4y ago
  No it's not.. you can and will easily have the same problem with SQL if you're not fetching beforehand.. I bet wordpress (or many plugins) is/are so slow exactly because of this. ORMs will even make it easier to write more efficient queries is many cases.
  - hinkley 4y ago
    There are too many questions to make any absolute statements. You hint at a couple here. Specialization of requests reduces data transport, but increases testing surface area and drastically reduces caching options - including within the database. Years ago we discovered that our bottleneck in Oracle was being caused by query cache evictions (worse, in 9i only queries currently in the cache could run, so new queries were blocking in the query planner. I'm not sure if this was ever fixed.) One of the important uses of etags within your infrastructure is to allow you to incorporate data manipulation into your caches. If your functions are deterministic you can store the results of a calculation, instead of the source data for that calculation. Cache hits then don't just save bandwidth, they also save computation, which reduces the sequential parts of the response, fighting Amdahl's Law.

henning 4y ago

[flagged]

Nextgrid 4y ago
You're dismissing ORMs because of one (well-understood and easy to mitigate) downside without considering the upsides. ORM do make development much easier and are fine in the majority of cases to begin with. For the edge-cases, some can be addressed by giving the ORM extra hints about your intentions (such as for the N+1 queries problem) and those that can't can be rewritten as raw SQL. Why dismiss them entirely in favour of Raw SQL if you can get the best of both worlds by picking the appropriate tool for the job?
- Arch-TK 4y ago
  If you simply don't attempt to shoehorn your data into a graph structure then you don't need an ORM in the first place. ORMs only make development easier insofar as you insist on turning your relational data into graphs. In all instances I've found that raw SQL or a good query builder (not an ORM, e.g. SQLAlchemy core) and an approach to handling the data as it is (relational) and not as you might want to pretend it to be (graph) is sufficient to ensure the resulting software is easier to develop and maintain. In situations where I have needed graph data I have found graph databases to work well there too.
- dakiol 4y ago
  How is the example given in the post an "edge" case? All except the simplest domain models include these kind of relationships (and more complex ones). The Post->Comment->Vote pattern is practically everywhere as soon as you are modeling real-world scenarios.
  - isbvhodnvemrwvn 4y ago
    Solving those issues takes you SIGNIFICANTLY less time than mapping SQL results in any moderately complex relation graph.
kstrauser 4y ago

bfung 4y ago

> breadth-first loading. The ideal solution requires us to load the data in a breadth-first approach, but unfortunately, this is harder to write because it does not compose well. The author finds the simplest and efficient solution, but continues to over engineer for blog content :P “Composing” is overrated in this case.

ananthakumaran 4y ago
This is a contrived example, probably not a real-world use case. The kind of issues I am dealing at work is much more complicated, usually involves more than 3 or 4 tables, serializers are referred by multiple other serializers etc. There is usually business logic involved as well in the query construction. I understand things can be improved, but it's not as simple as writing few queries by hand. ActiveRecord/ActiveModelSerializer provides good composability, but fails to handle N + 1 queries optimally, which is what I am trying to explain.

ydnaclementine 4y ago

Would this not be solved with adding `votes` to the `includes`? Something like: ``` Post.includes(comments: :votes) ``` Similar stackoverflow: https://stackoverflow.com/a/24397716 https://stackoverflow.com/a/24397716

ramchip 4y ago
Exactly this. Combine with Bullet[1] to detect problems early. [1] https://bhserna.com/tools-to-help-you-detect-n-1-queries.html#bullet https://bhserna.com/tools-to-help-you-detect-n-1-queries.htm...
rurabe 4y ago
The problem here is that you are loading all the votes as AR instances which is fine at small scale, but as your app gets larger, loading and instantiating thousands of Vote instances just to then break them down into an integer will start to drag on your controller. If you can count in the database itself it's a big win. Although no doubt your solution is cleaner code.

kstrauser 4y ago

I really wish this been originally called the “1+N problem”, not “N+1”. That naming makes it much clearer to me.

pharmakom 4y ago
Or even the “1 then N problem” since we determine the N from the 1.
Izkata 4y ago
I swear that's what it was called when I was first introduced to it a decade ago. When I first saw one of the posts focused on it on here I didn't initially recognize it as referring to the same thing.
- kstrauser 4y ago
  I don't remember how I heard it originally, but I wouldn't have recognized it as the same, either. To me, "N+1" implies you're already doing N queries and now you're running 1 more. That's a different class of problem than "you were running 1 query, and now you're running more than 1."

TexanFeller 4y ago

Understand N+1 before you try GraphQL.

saila 4y ago

You could probably get this down to two queries, one for posts and one for comments, if you aggregate the vote count when retrieving the comments. I think this is pretty easy to do with most ORMs. You could also get it down to 1 query using SQL. This is one way to do it based on the schema in the article [postgres, not well tested]: with latest_posts as ( select * from post limit 3 ), latest_comments as ( select c.*, count(v.id) as votes from comment c left join vote v on v.comment_id = c.id where c.post_id in (select id from latest_posts) group by c.id, c.content ) select p.*, json_agg(c) from latest_posts p left join latest_comments c on c.post_id = p.id group by p.id, p.title, p.content # NOTE: fixed SQL bug noted by @rurabe Off the top of my head, I'm not sure how you would (or if you could) do this with ActiveRecord, SQLAlchemy, or the Django ORM, but it's probably more complicated than just writing the SQL. To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL.

gnuvince 4y ago
> To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. When I did web development, I saw it as a "hack" and a "failure to write clean code" whenever I reached for raw SQL. This is of course not true at all, but it was a powerful psychological blocker and I'd spend too much time trying to figure how to get the ORM to do what I wanted instead of writing the SQL myself and moving on to the next problem.
joshuahedlund 4y ago
> but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. I agree. I often feel like I benefited by starting my web career pre-ORM and only learning to use them a few years in, so I can appreciate and use both. I sometimes wonder if it’s harder for new devs to acquire the same kind of experience.
pphysch 4y ago
It's pretty straightforward in Django. The key is being comfortable with writing custom Manager/QuerySet methods. You could do something like `Post.objects.latest().annotate_comments()` which would resolve almost exactly to the query you wrote above.

pmg102 4y ago

We solved the N+1 queries problem where I work by raising the level of abstraction from "queries plus serialisation" to "what shape data is required". We open sourced the solution at https://www.django-readers.org/ https://www.django-readers.org/.

eloisius 4y ago

I wish I'd had an opportunity to use Phoenix in production before I got out of web dev, because the way the Ecto ORM obviated this entire class of error was beautiful. Instead of lazy loading, there's a neat grammar for preloading the entire graph of related records that you want.

pharmakom 4y ago

This comes up in GraphQL, not just ORMs. A beautify solution is Facebook’s Haxl. Less beautiful is data-loader.

eezing 4y ago
Correct. While data-loader facilitates data loading, type resolvers in GraphQL is where the solution starts.

funnyfoobar 4y ago

Somewhat deviating, but relavent. If we use counter cache that is to keep vote_count on comments table, the include(:comments) solution would work fine. https://scoutapm.com/blog/how-to-start-using-counter-caches-in-rails https://scoutapm.com/blog/how-to-start-using-counter-caches-...