Eager Loading for ActiveRecord Performance

As soon as a database has more than a few hundreds rows in any given table, it’s time to start thinking about performance in ActiveRecord. But performance is hard, with terms like eager loading, preloading, and N+1 problems. In this talk, we’ll make sense of those terms, learn about performance vs memory tradeoffs, and look at how to make N+1 queries our friends.

Transcript

1. Eager Loading for ActiveRecord Performance May 25, 2018 Balkan Ruby
2. Dinah Shi @dinahshi
6. ActiveRecord
7. Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. —Wikipedia “
8. An active record is an object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data. —Martin Fowler “
9. ActiveRecord
10. N+1 Query
11. Post Comment
12. <% @posts.each do |post| %> #... <h2>Comments</h2> <%= render post.comments %> <% end %>
13. Post Load (0.8ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:16 Comment Load (8.8ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (131.1ms) Comment Load (2.7ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (109.3ms) Comment Load (2.9ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 3]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (105.6ms) ...
14. @posts = Post.all @posts.each { |p| use p.comments }
15. Post Load (1.3ms) SELECT "posts".* FROM "posts" Comment Load (3.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] Comment Load (2.6ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] Comment Load (2.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 3]] ...
16. #includes
17. @posts = Post.includes(:comments) @posts.each { |p| use p.comments }
18. Post Load (0.4ms) SELECT "posts".* FROM "posts" Comment Load (41.2ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3) [["post_id", 1], ["post_id", 2], ["post_id", 3]...
19. Chaining
20. Post.where(...).limit(10)
21. def limit(value) spawn.limit!(value) end
22. Relations execute queries lazily
23. @posts = Post.includes(:comments) @posts.each { |p| use p.comments }
24. def includes(*args) check_if_method_has_arguments!(:includes, args) spawn.includes!(*args) end def includes!(*args) # :nodoc: args.reject!(&:blank?) args.flatten! self.includes_values |= args self end
25. def includes(*args) check_if_method_has_arguments!(:includes, args) spawn.includes!(*args) end def includes!(*args) # :nodoc: args.reject!(&:blank?) args.flatten! self.includes_values |= args self end
26. @posts = Post.includes(:comments) @posts.each { |p| use p.comments }
27. Post Load (0.4ms) SELECT "posts".* FROM "posts" Comment Load (41.2ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3) [["post_id", 1], ["post_id", 2], ["post_id", 3]...
28. SELECT "posts"."id" AS t0_r0, "posts"."name" AS t0_r1, "posts"."title" AS t0_r2, "posts"."body" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."commenter" AS t1_r1, "comments"."body" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
29. @posts = Post .includes(:comments) .where('comments.body = ?', 'helloWorld') .references(:comments) @posts.each { |p| use p.comments }
30. #preload
31. def preload(*args) check_if_method_has_arguments!(:preload, args) spawn.preload!(*args) end def preload!(*args) # :nodoc: self.preload_values += args self end
32. #eager_load
33. def eager_load(*args) check_if_method_has_arguments!(:eager_load, args) spawn.eager_load!(*args) end def eager_load!(*args) # :nodoc: self.eager_load_values += args self end
34. def exec_queries(&block) skip_query_cache_if_necessary do @records = if eager_loading? apply_join_dependency do |relation, join_dependency| if ActiveRecord::NullRelation === relation [] else rows = connection.select_all(relation.arel, "SQL") join_dependency.instantiate(rows, &block) end.freeze end else klass.find_by_sql(arel, &block).freeze end preload_associations(@records) unless skip_preloading_value @records.each(&:readonly!) if readonly_value @loaded = true @records end end
35. def exec_queries(&block) skip_query_cache_if_necessary do @records = if eager_loading? apply_join_dependency do |relation, join_dependency| if ActiveRecord::NullRelation === relation [] else rows = connection.select_all(relation.arel, "SQL") join_dependency.instantiate(rows, &block) end.freeze end else klass.find_by_sql(arel, &block).freeze end preload_associations(@records) unless skip_preloading_value @records.each(&:readonly!) if readonly_value @loaded = true @records end end
36. gem 'bullet'
37. Should we always use #includes?
38. it depends
39. memory
40. <% @posts.each do |post| %> #... <h2>Comments: <%= post.comments.count %></h2> <% end %>
41. Post Load (0.8ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:16 Comment Load (8.8ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (131.1ms) Comment Load (2.7ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (109.3ms) Comment Load (2.9ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 3]] ↳ app/views/posts/index.html.erb:46 Rendered collection of comments/_comment.html.erb [1000 times] (105.6ms) ...
42. Post Load (0.4ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 (0.9ms) SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:21 (0.8ms) SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/views/posts/index.html.erb:21 (0.5ms) SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 3]] ↳ app/views/posts/index.html.erb:21 ...
43. ✅ memory bloat ❌ N+1
44. class Comment < ApplicationRecord belongs_to :post, counter_cache: true end
45. class AddCommentsCountToPost < ActiveRecord::Migration[5.2] def change add_column :posts, :comments_count, :integer, default: 0, null: false Post.find_each { |p| Post.reset_counters(p.id, :comments) } end end
46. (0.2ms) BEGIN Comment Create (35.3ms) INSERT INTO "comments" ("post_id", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id" [["post_id", 20], ["created_at", "2018-05-19 21:56:30.094214"], ["updated_at", "2018-05-19 21:56:30.094214"]] Post Update All (12.0ms) UPDATE "posts" SET "comments_count" = COALESCE("comments_count", 0) + 1 WHERE "posts"."id" = $1 [["id", 20]] (6.3ms) COMMIT
47. class AddCommentsCountToPost < ActiveRecord::Migration[5.2] def change add_column :posts, :comments_count, :integer, default: 0, null: false Post.find_each { |p| Post.reset_counters(p.id, :comments) } end end
48. Started GET "/posts" for 127.0.0.1 at 2018-05-24 12:38:07 +0300 Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (0.7ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Rendered posts/index.html.erb within layouts/application (3.1ms) Completed 200 OK in 47ms (Views: 43.8ms | ActiveRecord: 0.7ms)
49. Should we always use counter caches?
50. it depends
51. Are N+1 queries always bad?
52. it depends
53. [N+1] sounds like a bug. Well in a Russian doll caching setup, it's not a bug, it's a feature. The beauty of those individual calls is that they're individually cached, on their own timeline, and that they're super simple. —DHH “
55. <% @posts.each do |post| %> <% cache post do %> ... <%= render post.comments %> <% end %> <% end %>
56. on a cold cache
57. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (0.7ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180511205524293984 (0.8ms) Comment Load (4.6ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (112.6ms) Write fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180511205524293984 (2.5ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (0.2ms) Comment Load (2.2ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (123.5ms) Write fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (2.6ms) ...
58. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (0.7ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180511205524293984 (0.8ms) Comment Load (4.6ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (112.6ms) Write fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180511205524293984 (2.5ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (0.2ms) Comment Load (2.2ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (123.5ms) Write fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (2.6ms) ...
59. on a warm cache
60. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (1.5ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/1-20180511205524293984 (1.1ms) Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/2-20180511205526744736 (0.8ms) Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/3-20180511205529193079 (0.6ms) ...
61. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (1.5ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/1-20180511205524293984 (1.1ms) Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/2-20180511205526744736 (0.8ms) Read fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/3-20180511205529193079 (0.6ms) ...
62. Comment.last.touch
63. class Comment < ApplicationRecord belongs_to :post, touch: true end
64. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (0.6ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/10-20180511205546696442 (0.8ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180522214106536042 (0.2ms) Comment Load (10.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (215.4ms) Write fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/1-20180522214106536042 (3.6ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/11-20180518141035700651 (1.5ms) ...
65. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (0.6ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/10-20180511205546696442 (0.8ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/1-20180522214106536042 (0.2ms) Comment Load (10.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 1]] ↳ app/views/posts/index.html.erb:23 Rendered collection of comments/_comment.html.erb [1000 times] (215.4ms) Write fragment views/posts/index: 2893d555d1f12dc920da31b42cc2bcdf/posts/1-20180522214106536042 (3.6ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/11-20180518141035700651 (1.5ms) ...
66. What if we use #includes?
67. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (1.2ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Comment Load (43.4ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20) [["post_id", 2], ["post_id", 3], ["post_id", 4], ["post_id", 5], ["post_id", 6], ["post_id", 7], ["post_id", 8], ["post_id", 9], ["post_id", 10], ["post_id", 1], ["post_id", 11], ["post_id", 12], ["post_id", 13], ["post_id", 14], ["post_id", 15], ["post_id", 16], ["post_id", 17], ["post_id", 18], ["post_id", 19], ["post_id", 20]] ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (2.4ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/3-20180511205529193079 (0.9ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/4-20180511205531711226 (35.0ms) ...
68. Processing by PostsController#index as HTML Rendering posts/index.html.erb within layouts/application Post Load (1.2ms) SELECT "posts".* FROM "posts" ↳ app/views/posts/index.html.erb:5 Comment Load (43.4ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20) [["post_id", 2], ["post_id", 3], ["post_id", 4], ["post_id", 5], ["post_id", 6], ["post_id", 7], ["post_id", 8], ["post_id", 9], ["post_id", 10], ["post_id", 1], ["post_id", 11], ["post_id", 12], ["post_id", 13], ["post_id", 14], ["post_id", 15], ["post_id", 16], ["post_id", 17], ["post_id", 18], ["post_id", 19], ["post_id", 20]] ↳ app/views/posts/index.html.erb:5 Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/2-20180511205526744736 (2.4ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/3-20180511205529193079 (0.9ms) Read fragment views/posts/index:2893d555d1f12dc920da31b42cc2bcdf/ posts/4-20180511205531711226 (35.0ms) ...
69. The whole way you get around doing N+1 queries is by using joins; you do more complicated queries that take longer to compute, and tax the database harder. If you can simplify those queries but there's just more of them, you win if you have a caching strategy to support that. —DHH “
70. the power of lazy
71. Russian doll caching in APIs?
72. ActiveModel::Serializer JBuilder
74. gem 'fast_jsonapi'
75. class PostSerializer include FastJsonapi::ObjectSerializer attributes :name, :title, :body cache_options enabled: true has_many :comments, cached: true end
76. class PostSerializer include FastJsonapi::ObjectSerializer attributes :name, :title, :body cache_options enabled: true has_many :comments, cached: true end
77. on a cold cache
78. Processing by PostsController#index as HTML Post Load (2.1ms) SELECT "posts".* FROM "posts" ↳ app/controllers/posts_controller.rb:8 (1.2ms) SELECT "comments"."id" FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 2]] ↳ app/controllers/posts_controller.rb:8 (1.4ms) SELECT "comments"."id" FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 3]] ↳ app/controllers/posts_controller.rb:8 (4.7ms) SELECT "comments"."id" FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 4]] ↳ app/controllers/posts_controller.rb:8 ...
79. on a warm cache
80. Processing by PostsController#index as HTML Post Load (2.4ms) SELECT "posts".* FROM "posts" ↳ app/controllers/posts_controller.rb:8 Completed 200 OK in 188ms (Views: 0.2ms | ActiveRecord: 2.4ms)
81. Comment.last.touch
82. Processing by PostsController#index as HTML Post Load (6.8ms) SELECT "posts".* FROM "posts" ↳ app/controllers/posts_controller.rb:8 (2.2ms) SELECT "comments"."id" FROM "comments" WHERE "comments"."post_id" = $1 [["post_id", 20]] ↳ app/controllers/posts_controller.rb:8 Completed 200 OK in 126ms (Views: 0.2ms | ActiveRecord: 9.0ms)
83. Should we always use caching?
84. it depends
85. There are only two hard things in Computer Science: cache invalidation and naming things. —Phil Karlton “
86. #includes counter cache Russian doll caching
87. it depends
88. github.com/dinahshi/eager_load_demo
89. More resources • N+1 Queries or Memory Problems: Why not Solve Both? • N+1 is a Rails feature • Nate Berkopec and DHH discuss Rails performance • ActiveRecord source code • bullet gem • fast_jsonapi gem