4 ms·

"Many of the performance problems solved during my first few years at GitLab were N+1 query problems." "Other frameworks have learned from this over the years

by zzzeek 3y ago

"Many of the performance problems solved during my first few years at GitLab were N+1 query problems." "Other frameworks have learned from this over the years and provide better alternatives. The usual approach is that instead of being able to arbitrarily query associated data, you have to pass in the data ahead of time. The benefit here is that if you were to forget passing the data in, you'd run into some sort of error rather than the code querying the data for you on a per-row basis, introducing performance problems along the way." can someone...translate this for me into human? If I want to query for some data I have to ....first pass the data in? Where did I get the data? I can't parse this paragraph at all. Rubyists....

YorickPeterse 3y ago
In Rails, you can write something like this: Project.limit(10).each do |project| project.members.count end Here `project.members` returns some sort of query object that produces a query along the lines of this: [...] FROM members WHERE members.project_id = X [...] In other words, you can just query associated data on a per-project basis, without needing to pass any additional arguments. The problem this results in is that in the above code you'd run a COUNT query for _each_ project. Instead what you'd want is a single COUNT that groups data per project, such that you can then pass that data along with the `each` call. This setup would only require 2 queries, instead of 20. To eager load data in Rails you have to explicitly opt-in, resulting in something like this: Project.includes(:members).limit(10).each do |project| project.members.count end The problem here is that an opt-in mechanism is too easy to forget (as is evident by how common these N+1 query problems are), and even if you include it there are certain cases where you still end up running extra queries for each row. The solution here is to separate querying from the row instance types, e.g. a "Project" type can't query data itself and instead requires it to be passed in. This makes it much more difficult to create N+1 query problems.
- zzzeek 3y ago
  oh OK , then I can translate: 1. have eager loading, but then also 2. prohibit lazy loading, have it raise rather than implicitly load voila haha, downmods
d_k_f 3y ago
They're likely taking about being able to query for additional data from the view layer. Rails makes this very easy since you often simply pass along query results to the view, which are "database connected" ActiveRecord instances. This way you can easily build the infamous "iterate over Posts and show their Users" N+1 example. According to them, other frameworks require you to fetch all required data before passing it to the view layer, thus preventing this issue from coming up on the first place.

by zzzeek 3y ago

YorickPeterse 3y ago

In Rails, you can write something like this: Project.limit(10).each do |project| project.members.count end Here `project.members` returns some sort of query object that produces a query along the lines of this: [...] FROM members WHERE members.project_id = X [...] In other words, you can just query associated data on a per-project basis, without needing to pass any additional arguments. The problem this results in is that in the above code you'd run a COUNT query for _each_ project. Instead what you'd want is a single COUNT that groups data per project, such that you can then pass that data along with the `each` call. This setup would only require 2 queries, instead of 20. To eager load data in Rails you have to explicitly opt-in, resulting in something like this: Project.includes(:members).limit(10).each do |project| project.members.count end The problem here is that an opt-in mechanism is too easy to forget (as is evident by how common these N+1 query problems are), and even if you include it there are certain cases where you still end up running extra queries for each row. The solution here is to separate querying from the row instance types, e.g. a "Project" type can't query data itself and instead requires it to be passed in. This makes it much more difficult to create N+1 query problems.

zzzeek 3y ago
oh OK , then I can translate: 1. have eager loading, but then also 2. prohibit lazy loading, have it raise rather than implicitly load voila haha, downmods

d_k_f 3y ago

They're likely taking about being able to query for additional data from the view layer. Rails makes this very easy since you often simply pass along query results to the view, which are "database connected" ActiveRecord instances. This way you can easily build the infamous "iterate over Posts and show their Users" N+1 example. According to them, other frameworks require you to fetch all required data before passing it to the view layer, thus preventing this issue from coming up on the first place.