Speeding Up Requests

Episode #322 by Teacher's Avatar David Kimura

Summary

In this episode, we look at several issues around pagination and different kinds of querying that could affect performance when working with large datasets.
performance rails search pagination 29:51

Resources

Download Source Code

Summary

# Terminal
docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres
docker stop postgres
docker start postgres
brew install ctop && ctop

rails g model author name
rails g model publisher name
rails g model genre name
rails g scaffold books title author:belongs_to publisher:belongs_to genre:belongs_to
rails g migration add_index_to_tables

bundle add faker
bundle add pg_search
bundle add kaminari

# config/database.yml
development:
  <<: *default
  database: template_development
  url: postgresql://postgres:postgres@localhost:5432/template_development

# db/seeds.rb
100.times do
  Author.create(name: Faker::Book.author)
end

100.times do
  Publisher.create(name: Faker::Book.author)
end

100.times do
  Genre.create(name: Faker::Book.genre)
end

100.times do
  [].tap do |array|
    10_000.times do
      time = Time.current
      array << {
        title: Faker::Book.title,
        author_id: 1 + rand(100),
        publisher_id: 1 + rand(100),
        genre_id: 1 + rand(100),
        updated_at: time,
        created_at: time
      }
    end
    Book.insert_all(array)
  end
end

# 1_000_000.times do
#   Book.create(
#     title: Faker::Book.title,
#     author_id: 1 + rand(100),
#     publisher_id: 1 + rand(100),
#     genre_id: 1 + rand(100)
#   )
# end

# books_controller.rb
def index
  @books = Book.search(params[:q])
    .includes(:author, :publisher, :genre)
    .page(params[:page])
    .without_count
    .per(8)
end

# models/book.rb
class Book < ApplicationRecord
  belongs_to :author
  belongs_to :publisher
  belongs_to :genre

  include PgSearch::Model
  pg_search_scope :pg_search,
    against: :title,
    using: {
      tsearch: { dictionary: 'english' }
    },
    associated_against: {
      author: :name,
      publisher: :name,
      genre: :name
    }

  def self.search(query)
    return all unless query.present?

    # where(title: query)

    # where("title ILIKE ?", "%#{query}%")

    # where("to_tsvector('english', title) @@ plainto_tsquery('english', :q)", q: query)

    # where("to_tsvector('english', title) @@ plainto_tsquery('english', :q)", q: query)
    # .joins(:author).or(Book.where("authors.name ILIKE ?", "%#{query}%"))
    # .joins(:publisher).or(Book.where("publishers.name ILIKE ?", "%#{query}%"))
    # .joins(:genre).or(Book.where("genres.name ILIKE ?", "%#{query}%"))

    pg_search(query)
  end
end


# views/books/index.html.erb
<%= form_with url: books_path, method: :get do |form| %>
  <div class='input-group'>
    <%= form.text_field :q, value: params[:q], placeholder: 'Search', class: 'form-control' %>
    <%= form.submit 'Search', class: 'btn btn-outline-secondary' %>
  </div>
<% end %>

<table class='table'>
  <thead>
    <th>Title</th>
    <th>Author</th>
    <th>Publisher</th>
    <th>Genre</th>

    <th colspan=3>Actions</th>
  </thead>
  <tbody>
    <% @books.each do |book| %>
      <tr>
        <td><%= highlight(book.title, query_array) %></td>
        <td><%= highlight(book.author.name, query_array) %></td>
        <td><%= highlight(book.publisher.name, query_array) %></td>
        <td><%= highlight(book.genre.name, query_array) %></td>

        <td><%= link_to "Show", book_path(book) %></td>
        <td><%= link_to "Edit", edit_book_path(book) %></td>
        <td><%= link_to "Delete", book_path(book), "data-turbo-method": :delete %></td>
      </tr>
    <% end %>
  </tbody>
</table>

<%# paginate @books %>
<%= link_to_prev_page @books, 'Previous Page', class: 'btn btn-outline-primary' %>
<%= link_to_next_page @books, 'Next Page', class: 'btn btn-outline-primary' %>

# app/helpers/application_helper.rb
module ApplicationHelper
  def query_array
    params[:q].to_s.split(' ')
  end
end

# db/migrate/XXXXXXXX_add_index_to_tables.rb
class AddIndexToTables < ActiveRecord::Migration[7.0]
  def change
    add_index :books, "to_tsvector('english', title)", using: :gin
    add_index :authors, "to_tsvector('english', name)", using: :gin
    add_index :publishers, "to_tsvector('english', name)", using: :gin
    add_index :genres, "to_tsvector('english', name)", using: :gin
  end
end