title: Ruby on Rails website is slow; could it be the SQL queries?
body: <p>I have been working on a web application made with Ruby on Rails.  I've "completed" the site, but it runs <em>very</em> slow and pages sometimes take on the order of ten seconds to load.  I've broken this post up into three sections:</p>
  
  <ol>
  <li>Overview</li>
  <li>Diagnosis</li>
  <li>Possible Ideas</li>
  </ol>
  
  <p><strong>Overview</strong></p>
  
  <p>As a very rough overview, this website displays personal projects, each of which have their own page.  Each project (which I call a “post” in the code) is stored in a table.  I’m not sure if it’s a bad idea to store a whole post in a database as some posts have a decent amount of text and images in their body.  Each entry in the <code>posts</code> table has the following attributes:</p>
  
  <pre><code># == Schema Information
  #
  # Table name: posts
  #
  #  id                       :integer          not null, primary key
  #  title                    :string
  #  body                     :text
  #  description              :text
  #  slug                     :string
  #  created_at               :datetime         not null
  #  updated_at               :datetime         not null
  #  image_file_name          :string
  #  image_content_type       :string
  #  image_file_size          :integer
  #  image_updated_at         :datetime
  #  thumbnail_file_name      :string
  #  thumbnail_content_type   :string
  #  thumbnail_file_size      :integer
  #  thumbnail_updated_at     :datetime
  #  published                :boolean          default("f")
  #  published_at             :datetime
  #
  </code></pre>
  
  <p><strong>Diagnosis</strong></p>
  
  <p>I’m using Heroku to host the application. I’ve upgraded to Hobby level dynos, so I could have access to some of the metrics they provide, like throughput, memory usage, etc.  I don’t think Heroku is what is making the website slow, but rather my code.  In an effort to debug, I've added a third party addon, <a href="https://scoutapp.com/" rel="nofollow noreferrer">Scout</a>, that tracks the bottlenecks in the application.</p>
  
  <p>Scout offers a trace feature that quantizes which code paths are taking the most time in the application.  You can see below (in the bottom half of the picture) that there are a significant amount of traces that take upwards of ten seconds.  Not very good…</p>
  
  <p><a href="https://i.stack.imgur.com/rb2S4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rb2S4.png" alt="Trace feature in Scout"></a></p>
  
  <p>When I click on the second line (6/17 2:04 PM), it gives a breakdown of the response time:</p>
  
  <p><a href="https://i.stack.imgur.com/glGdk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/glGdk.png" alt="Breakdown of the response time for &quot;/&quot; trace"></a></p>
  
  <p>Expanding the SQL statements shows that most of the time intensive queries are from acting on the posts database, and sometimes sorting/ordering the posts (see below).</p>
  
  <p><a href="https://i.stack.imgur.com/UHpwE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UHpwE.png" alt="Time intensive SQL statements"></a></p>
  
  <p><strong>Possible Ideas</strong></p>
  
  <p>Is there anything obvious I am doing wrong here?  I am pretty stuck, and not sure how to speed things up.  Given what Scout is saying, I have two ideas:</p>
  
  <ul>
  <li>Controller/SQL queries the controller invokes are slow</li>
  <li>Embedded Ruby code in HTML is slow.</li>
  </ul>
  
  <p><em>Controller/SQL queries the controller invokes are slow:</em></p>
  
  
  
  <p>The code below shows the code where I am assigning <code>@posts</code> in the <code>PostsController</code>.  The <code>home</code> method is run when the user visits the <a href="http://www.hlove-engineering.com/" rel="nofollow noreferrer">home page</a>, and the <code>index</code> method is run when the user goes to the <a href="http://www.hlove-engineering.com/posts" rel="nofollow noreferrer">posts page</a>.  Could these queries be slow because there is a fair amount of data in the database (5 posts worth of text and images)?</p>
  
  <pre class="lang-rb prettyprint-override"><code>class PostsController &lt; ApplicationController
    #before_action :authenticate_user!
    before_action :set_post, only: [:show, :edit, :update, :destroy, :publish, :unpublish]
  
    def home
      @posts = Post.all.published.order('published_at DESC')
    end
  
    # GET /posts
    # GET /posts.json
    def index
      if user_signed_in? and current_user.admin
        @posts = Post.all.order("id")
      else
        @posts = Post.all.published
      end
    end
  </code></pre>
  
  <p><em>Embedded Ruby Code in HTML is slow:</em></p>
  
  <p>I am using Ruby in some of my HTML code to sort the posts by date and to determine the most recent post.  For example, in the sidebar of the website (to the left of the <a href="http://www.hlove-engineering.com/" rel="nofollow noreferrer">home</a> page), there is section that displays “Recent”, and the logic behind that is:</p>
  
  <pre class="lang-html prettyprint-override"><code>&lt;h4 style="border-bottom: 1px solid #bbb"&gt;Recent&lt;/h4&gt;
      &lt;% @post=Post.all.published.order("published_at").last %&gt;
      &lt;% if @post == nil or @post.published_at == nil %&gt;
          &lt;div class="temp_sidebar"&gt;Coming Soon!&lt;/div&gt;
      &lt;% else %&gt;
          &lt;%= render partial: "layouts/media", locals: {post: @post} %&gt;
      &lt;% end %&gt;
  </code></pre>
  
  <p>Similarly, in the “Archives” section of the sidebar, I’m sorting the posts by date, and am doing this logic:</p>
  
  <pre class="lang-html prettyprint-override"><code>&lt;% if Post.published.length != 0 %&gt; 
      &lt;h4&gt;Archives&lt;/h4&gt;
      &lt;div id="sidebar" class="list-group"&gt;
  
          &lt;% @published_posts = Post.published %&gt;
          &lt;% archives = Hash.new(0) %&gt;
          &lt;% @published_posts.each do |post| %&gt;
              &lt;% if archives[post.date] == 0 %&gt;
                  &lt;% archives[post.date] = 1%&gt;
              &lt;% else %&gt;
                  &lt;% archives[post.date] += 1 %&gt;
              &lt;% end %&gt;
          &lt;% end %&gt;
  
          &lt;% archives.each do |key, value| %&gt;
              &lt;button class="accordion"&gt;&lt;%= key %&gt; &lt;span class="badge"&gt; &lt;%= value %&gt;&lt;/span&gt;&lt;/button&gt;
                  &lt;div class="panel"&gt;
                      &lt;% @published_posts.each do |post| %&gt;
                          &lt;% if post.date == key %&gt;
                              &lt;p&gt;&lt;%= link_to post.title, post_path(@post) %&gt;&lt;/p&gt;
                          &lt;% end %&gt;
                      &lt;% end %&gt;
                  &lt;/div&gt;
          &lt;% end %&gt;
  
      &lt;/div&gt;
  &lt;% end %&gt;
  </code></pre>
  
  <p>My idea is that maybe iterating over posts is taking a very long time, but I’m not entirely sure.  I feel like this is valid code that is ok to be used, but maybe something about it is very slow.  Would you guys have any ideas here?  It may also be good to note here that the app's memory usage is pretty high: around 500MB.  Maybe these queries are slow because of all the data that is getting fetched, but that said, I'm am not quite sure what "a lot" of data is for a web app like this.  And of course, my hypothesis as to why this site is slow could be totally wrong, so I'm very open to your thoughts.  Lastly, if the SQL queries/code I am using is slow, are there ways I could speed it up/improve its performance?  Thank you for any help in advance! </p>