How can I improve this ActiveRecord query performance?

Question

I have a rails applications with the following models (and their relationships)

class SchoolCLass < ApplicationRecord
  belongs_to :school
  
  has_many :class_teachers
  has_many :teachers, through: :class_teachers

  belongs_to :course
  has_many :lessons, through: :course
end

class AnsweredQuestion < ApplicationRecord
  belongs_to :question
  belongs_to :response
  belongs_to :school_class
end

class Response < ApplicationRecord
  belongs_to :student
  has_many :answered_questions, dependent: :destroy
end

class Question < ApplicationRecord
  belongs_to :section
end

class Section
  belongs_to :lesson
  has_many :questions
end

class Lesson < ApplicationRecord
  belongs_to :course
  has_many :sections
end

I need to build a new view where I need to compute the status of each lesson based on the % of students that have answered a % of questions.

I went through several iterations of the code and the "fastest" (or least slow) logic I came up with is the following

class SchoolsController < ApplicationController
  def progress
    @purchased_courses = @school.purchased_courses.includes(course: [:lessons, school_classes: [:students, teacher: [:user]]])
  end
end

# school_class.rb

  def status_of_lesson(lesson)
    total_questions = lesson.questions.count
    students_who_answered_50_percent_of_questions = 0
    students_who_answered_one_question = 0

    students.each do |student|
      answered_questions = AnsweredQuestion.includes(:response, question: [:section]).where(school_class: self, responses: {student_id: student.id}, questions: {sections: {lesson_id: lesson.id}}).count

      students_who_answered_50_percent_of_questions += 1 if answered_questions > total_questions / 2.0
      students_who_answered_one_question += 1 if answered_questions.positive?
    end

    if students_who_answered_50_percent_of_questions > students.count * 0.4
      'Complete'
    elsif students_who_answered_one_question > students.count * 0.2
      'Started'
    else
      'Not started'
    end
  end

Then I use this method in the view like so

<% purchased_course.course.lessons.each do |lesson| %>
  
    <%= school_class.status_of_lesson(lesson) %>
  
<% end %>

Loading this page takes around 15 seconds

Completed 200 OK in 14749ms (Views: 11360.6ms | ActiveRecord: 3354.2ms | Allocations: 24898688)

For a school with 281 students, 16 school classes and 2850 answered question. Although this is a decently sized dataset, I find hard to believe that this query can't be optimized.

I tried to replace most of the above with a custom SQL query

select responses.student_id, count(responses.student_id)
from answered_questions
join questions on answered_questions.question_id = questions.id
join sections on questions.questionnaire_type = 'Section' and questions.questionnaire_id = sections.id
join responses on answered_questions.response_id = responses.id
where answered_questions.school_class_id = :school_class and sections.lesson_id = :lesson
group by responses.student_id

but this makes the query even slower. I'm thinking I'm missing an index somewhere but I can't quite figure out where that is.

How can I improve this ActiveRecord query performance?

0 Answers