Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Active Model Serializer - Increasing Render Performance

I'm running into a particular situation where the rendered json generated by ActiveModel::Serializer is extraordinary slow (around 6-8 seconds). How can I improve the speed of this rendering? Here's the code.

Models:

class Comment < ActiveRecord::Base
  has_many :children_comments,
           class_name: 'Comment',
           foreign_key: 'parent_comment_id'

  belongs_to :user
  belongs_to :parent_comment,
             class_name: 'Comment',
             foreign_key: 'parent_comment_id'
end

Serializers:

class CommentSerializer < ActiveModel::Serializer
  include ActionView::Helpers::DateHelper

  attributes :id, :message, :created_at_in_words,
             :created_at, :parent_comment_id
  belongs_to :user
  has_many :children_comments

  def created_at_in_words
    time_ago_in_words(object.created_at) + ' ago'
  end

  def children_comments
    object.children_comments.map do |comment|
      CommentSerializer.new(comment).as_json
    end
  end
end

class UserSerializer < ActiveModel::Serializer
  attributes :id, :name, :avatar_url

  def avatar_url
    object.avatar.url
  end
end

In my controller I have

  parent_comments = Comment.where(parent_comment_id: nil)

  render status: :ok,
         json: parent_comments,
         each_serializer: CommentSerializer,
         key_transform: :camel_lower

Here is my partial log output when I make the call to the server. As you ca see Active Model Serializer is taking around 20ms to make each query call.

Started GET "/comments?lesson_id=420" for ::1 at 2016-09-01 11:09:14 -0400
Processing by Api::CommentsController#index as HTML
  Parameters: {"lesson_id"=>"420"}
  User Load (0.4ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1  ORDER BY "users"."id" ASC LIMIT 1  [["id", 102]]
  Lesson Load (0.5ms)  SELECT  "lessons".* FROM "lessons" WHERE "lessons"."id" = $1  ORDER BY position ASC LIMIT 1  [["id", 420]]
  Comment Load (53.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."commentable_id" = $1 AND "comments"."commentable_type" = $2 AND "comments"."parent_comment_id" IS NULL  [["commentable_id", 420], ["commentable_type", "Lesson"]]
[active_model_serializers]   User Load (0.5ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (24.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41401]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41402]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (22.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41403]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (21.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41404]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41405]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41406]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41407]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41408]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41409]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41410]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41411]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41412]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41413]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (23.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41414]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41415]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41416]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (23.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41417]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41418]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41419]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41420]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41421]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.9ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41422]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41423]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41424]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41425]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41426]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41427]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41428]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41429]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41430]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.9ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41431]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41432]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41433]]
[active_model_serializers]   CACHE (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (21.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41434]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41435]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (21.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41436]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41437]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41438]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (22.9ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41439]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41440]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41441]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.9ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41442]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41443]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41444]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41445]]
[active_model_serializers]   CACHE (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41446]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41447]]
[active_model_serializers]   CACHE (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41448]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41449]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41450]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41451]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41452]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41453]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41454]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (22.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41455]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (22.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41456]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41457]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41458]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41459]]
[active_model_serializers]   CACHE (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41460]]
[active_model_serializers]   CACHE (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41461]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41462]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41463]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41464]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41465]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41466]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41467]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.5ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41468]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41469]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41470]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41471]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41472]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41473]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41474]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41475]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41476]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (19.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41477]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41478]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41479]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41480]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41534]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41535]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41536]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41537]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (18.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 41538]]
[active_model_serializers] Rendered ActiveModel::Serializer::CollectionSerializer with ActiveModelSerializers::Adapter::Json (3895.33ms)
Completed 200 OK in 4007ms (Views: 1222.1ms | ActiveRecord: 2743.8ms)

With Michal's answer, here is a small sample from the log.

Started GET "/comments?lesson_id=370" for ::1 at 2016-09-02 17:13:06 -0400
Processing by Api::CommentsController#index as HTML
  Parameters: {"lesson_id"=>"370"}
  User Load (0.4ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1  ORDER BY "users"."id" ASC LIMIT 1  [["id", 102]]
  Lesson Load (0.4ms)  SELECT  "lessons".* FROM "lessons" WHERE "lessons"."id" = $1  ORDER BY position ASC LIMIT 1  [["id", 370]]
  Comment Load (23.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" IS NULL AND "comments"."commentable_type" = 'Lesson' AND "comments"."commentable_id" = 370
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (102)
  Comment Load (25.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" IN (38641, 38687, 38733)
  CACHE (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (102)
[active_model_serializers]   Comment Load (20.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 38642]]
[active_model_serializers]   User Load (0.5ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (20.7ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 38643]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]
[active_model_serializers]   Comment Load (30.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_comment_id" = $1  [["parent_comment_id", 38644]]
[active_model_serializers]   CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 102]]

My theory is this. I'm convinced the children_comments serialization is causing the majority of the performance problems. Because I have to call children_comments for every comment, this results in a cascading effect. I wonder if I can rewrite the code in a way to improve performance.

like image 220
thank_you Avatar asked Aug 29 '16 17:08

thank_you


People also ask

What is Active model serializer?

What is Active Model Serializers? Serializer gem allows us to format our JSON easily. It will enable us to select only the data we want and access our relationships with a single request. Active Model Serializers provides a way of creating custom JSON in an object-oriented manner.

What is the purpose of a serializer in rails?

Serialization converts an object in memory into a stream of bytes that can be recreated when needed. Serializers in Ruby on Rails convert a given object into a JSON format. Serializers control the particular attributes rendered when an object or model is converted into a JSON format.

What do Serializers do for us?

Helpfully, serializers allow us to specify what associations an object has, in the same way we do when we write out our models. That's more like it.

What is serializer in backend?

According to Microsoft documentation: Serialization is the process of converting an object into a stream of bytes to store the object or transmit it to memory, a database or file. Its main purpose is to save the state of an object in order to be able to recreate it when needed.


2 Answers

You're running into a n+1 queries type problem. Unfortunately includes isn't really helping you here because it only helps you with one level of the association - it avoids separate fetches of the children of the top level comment, but not grand children or great grandchildren.

You could probably optimise the user lookup by maintaining your own cache of user id to user objects (the rails cache caches the raw data but will be reinstantiating the objects over and over again), but to make this substantially faster you need to change how you load the comments.

If you are using a database that supports it (such as postgresql), then recursive queries ar an option (see https://hashrocket.com/blog/posts/recursive-sql-in-activerecord for a worked example). I don't know this scales as the tree gets deeper and deeper.

If recursive queries aren't an option, then there are a few approaches that involve changing what you store.

One is the materialised path patten. For example say that the root comment has id 1, a child has id 101 and one of its children has id 426. That last comment's path is 1/101/426. All of its siblings have paths starting with 1/101/. This means you can use like queries (with wildcards at the end) to find subtrees quickly. The ancestry gem implements this. If comments get moved then you need to rewrite the paths of all the comments children (and grand children etc.), but that may not be relevant to your use case. Very deep trees are problematic I think.

Another is the nested set pattern. The core idea is that the parent node stores the minimum and maximum id of all of its children and their children's children etc. This allows retrieving of all these children in one go. The flip side is that inserts and updates require rewriting a lot of this data (more so than with materialised path). There have been various gems that implement this over the years (a current one seems to be awesome_nested_set).

Also worth checking that you have got the right indexes to support your queries - unless there are really quite a lot of comments with a given parent, 20-30ms seems quite a long time for one query.

like image 196
Frederick Cheung Avatar answered Oct 13 '22 14:10

Frederick Cheung


Change your ActiveRecord query to this

parent_comments = Comment.where(parent_comment_id: nil).includes(:user, children_comments: :user)

It will get rid of N + 1 queries.

like image 45
Michał Młoźniak Avatar answered Oct 13 '22 14:10

Michał Młoźniak