Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Leveraging N+1 queries

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

92d08794b535e41a4082c57ea547546e?s=128

Sebastian Sogamoso

October 27, 2016
Tweet

Transcript

  1. Leverage N+1 queries

  2. Things like N+1 is a feature, which usually is seen

    as a bug “ g” DHH
  3. None
  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)
  11. None
  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!