Leveraging N+1 queries

Lightning talk based a blog post written by Piao Qihuan at https://sourcediving.com/speed-up-your-performance-with-n-1-dont-kill-it-82fe3444e7d#.fuxyy4epn.

Presented at: RubyConf Portugal 2016

Transcript

1. Leverage N+1 queries
2. Things like N+1 is a feature, which usually is seen as a bug “ g” DHH
4. Our use case
5. class Recipe < ApplicationRecord belongs_to :user end class User < ApplicationRecord has_many :recipes end
6. class RecipesController < ApplicationController def index @recipes = Recipe.page(params[:page]) end end
7. # recipes/index.html.erb <ul> <%= render @recipes %> </ul> # recipes/_recipe.html.erb <li> Title: <%= recipe.title %> <!-- here's the N+1 query --> Author: <%= recipe.user.name % </li>
8. Queries will look like this when visiting recipes#index SELECT `recipes`.* FROM `recipes` WHERE ... SELECT `users`.* FROM `users` WHERE `users`.`id` = 1111 LIMIT 1 SELECT `users`.* FROM `users` WHERE `users`.`id` = 1112 LIMIT 1 SELECT `users`.* FROM `users` WHERE `users`.`id` = 1113 LIMIT 1 SELECT `users`.* FROM `users` WHERE `users`.`id` = 1114 LIMIT 1
9. Agh, this is easy to fix @recipes = Recipe.published.includes(:user)
10. Log will now look like this SELECT `recipes`.* FROM `recipes` WHERE ... SELECT `users`.* FROM `users` WHERE `users`.`id` IN (1111, 1112, 1113, 1114)
12. When N+1 meets the cache
13. # recipes/_recipe.html.erb <% cache recipe do %> <li> Title: <%= recipe.title %> Author: <%= recipe.user.name %> </li> <% end %>
14. On first recipes#index visit Cache digest for app/views/recipes/index.html.erb: f632c9956497563f2a7b2ed0483ab9de Read fragment views/recipes/864277-20160728164012000000000/ f632c9956497563f2a7b2ed0483ab9de (1.7ms) Write fragment views/recipes/864277-20160728164012000000000/ f632c9956497563f2a7b2ed0483ab9de (1.2ms) ...
15. On subsequent visits, it should read from cache Cache digest for app/views/recipes/index.html.erb: f632c9956497563f2a7b2ed0483ab9de Read fragment views/recipes/864277-20160728164012000000000/ f632c9956497563f2a7b2ed0483ab9de (0.3ms) ...
16. Not really
17. Eager loading users will be unnecessary and in fact have a detrimental effect def index - @recipes = Recipe.published.includes(:user) + @recipes = Recipe.published end
18. We will now only fire one query for the page! SELECT `recipes`.* FROM `recipes` WHERE ...
19. When configured properly N+1 with cache means
20. • Simpler controllers • Simpler queries • Less load on the database • Great scalability
21. This doesn’t mean
22. You should keep every N+1 query in your app
23. sourcediving.com by @kinopyo
24. My name is Sebastián Tweet to me @sebasoga
25. Thanks!