TODO N+1

https://code.tutsplus.com/articles/improving-the-performance-of-your-rails-app-with-eager-loading--cms-25018

Introduction

Ruby on Rails ORM has lazy loading enabled.

Example

#  Post Model
class Post < ActiveRecord::Base
    belongs_to :author
end

#  Author Model
class Author < ActiveRecord::Base
    has_many :posts
end

# Controller
class PostsController < ApplicationController
    def index
        @posts = Post.order(created_at: :desc)
    end
end

# View
<% @posts.each do |post| %>
    <td><%= post.title %></td>
    <td><%= post.author.name %></td>
<% end %>

authors = Author.create([{ name: 'John' }, { name: 'Doe' }, { name: 'Manish' }])
Post.create(title: 'I love Tuts+', body: '', author: authors.first)
Post.create(title: 'Tuts+ is Awesome', body: '', author: authors.second)
Post.create(title: 'Long Live Tuts+', body: '', author: authors.last)

Case 1: Default - lazy loading

@posts = Post.order(created_at: :desc)

N+1

Post Load (0.6ms)  SELECT "posts".* FROM "posts"  ORDER BY "posts"."created_at" DESC
Author Load (0.5ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT 1  [["id", 3]]
Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT 1  [["id", 2]]
Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT 1  [["id", 1]]

Case 2.1: preload - eager loading / separated queries

@posts = Post.order(created_at: :desc).preload(:author)

SELECT "posts".* FROM "posts"  ORDER BY "posts"."created_at" DESC
SELECT "authors".* FROM "authors" WHERE "authors"."id" IN (3, 2, 1)

1. No N+1

2. Fail @ order posts by author name

3. Fail @ find posts by author name

Case 2.2: eager_load - eager loading / one query

@posts = Post.order(created_at: :desc).eager_load(:author)

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."author_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "authors"."id" AS t1_r0, "authors"."name" AS t1_r1, "authors"."created_at" AS t1_r2, "authors"."updated_at" AS t1_r3 
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id" 
ORDER BY "posts"."created_at" DESC

order posts by author name

@posts = Post.order("authors.name").eager_load(:author)

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."author_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "authors"."id" AS t1_r0, "authors"."name" AS t1_r1, "authors"."created_at" AS t1_r2, "authors"."updated_at" AS t1_r3 
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
ORDER BY authors.name

find posts by author name

@posts = Post.order(created_at: :desc).eager_load(:author).where("authors.name = ?", "Manish")

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."author_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "authors"."id" AS t1_r0, "authors"."name" AS t1_r1, "authors"."created_at" AS t1_r2, "authors"."updated_at" AS t1_r3 
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
WHERE (authors.name = 'Manish')  
ORDER BY "posts"."created_at" DESC

single query and LEFT OUTER JOINcan also be very expensive.

Case 2.3 includes - smarter

(same as preload)

@posts = Post.order(created_at: :desc).includes(:author)

SELECT "posts".* FROM "posts"  ORDER BY "posts"."created_at" DESC
SELECT "authors".* FROM "authors" WHERE "authors"."id" IN (3, 2, 1)

order posts by author name (same as eager_load)

@posts = Post.order("authors.name").includes(:author)

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."author_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "authors"."id" AS t1_r0, "authors"."name" AS t1_r1, "authors"."created_at" AS t1_r2, "authors"."updated_at" AS t1_r3 
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
ORDER BY authors.name

find posts by author name (same as eager_load)

@posts = Post.order(created_at: :desc).includes(:author).where("authors.name = ?", "Manish").references(:author)

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."author_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "authors"."id" AS t1_r0, "authors"."name" AS t1_r1, "authors"."created_at" AS t1_r2, "authors"."updated_at" AS t1_r3 
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
WHERE (authors.name = 'Manish')  
ORDER BY "posts"."created_at" DESC