Making Tables Faster

I Have had trouble with tables a couple of times. Not because of how to create the actual cells, but because of performance issues.

Let me use as an example the time I had to create a table that would routinely timeout my page on Heroku (requests timeout after 30s) and the path I took to get the same tables to take less than 3s per request.

The tech stack used throughout this process consists of:

• Ruby (on Rails)
• Postgresql

Setting the stage

Let’s imagine you have a CRMish app that puts leads through a process.

These leads are associated with a status that can be of one of four types:

• In treatment
• Accepted
• Refused
• New

We want to display them by an arbitrary value. To make it difficult for ourselves, let’s have each user correspond to a row.

We want to display the following columns:

• Number of treated leads (Accepted or Refused)
• Number of Reached leads (Accepted or Refused with a flag)
• Rate of reach (Treated / Reached)
• Number of Accepted leads
• Rate of acceptance
• Rate of Accepted over Treated
• Revenue (field in lead)
• Cost (field in lead)
• Profit (revenue — cost)
• profit rate (profit over revenue)

The end result should look something like this:

Take a moment to absorb the information above and think how you would execute in the broad-strokes before continuing :)

My naive first approach

If you are like me, the first idea that came into your mind was to iterate line by line and then calculate each column, something like this:

<table>
  <thead>
    <th>User</th>
    <th># Treated</th>
    <th># Reached</th>
    <th>Reach %</th>
    <th># Accepted</th>
    <th>Acceptance %</th>
    <th>Accepted/Treated</th>
    <th>Revenue </th>
    <th>Cost </th>
    <th>Profit</th>
    <th>Profit %</th>
  </thead>
  <tbody>
    <%= @users.each do |user| %>
      <td><%= user.full_name %></td>
      <td><%= user.leads.treated.size %></td>
      <td><%= user.leads.reached.size %></td>
      <td><%= user.leads.reached.size / user.leads.treated.size %></td>
      <td><%= user.leads.accepted.size %></td>
      <td><%= user.leads.accepted.size / user.leads.reached.size %></td>
      <td><%= user.leads.accepted.size / user.leads.treated.size %></td>
      <td><%= user.leads.sum(:revenue) %></td>
      <td><%= user.leads.sum(:cost) %></td>
      <td><%= user.leads.sum(:revenue) — user.leads.sum(:cost) %></td>
      <td><%= (user.leads.sum(:revenue) — user.leads.sum(:cost)) / user.leads.sum(:revenue) %></td>
    <% end %>
  </tbody>
</table>

This solution will work if you don’t have too many lines, but you might run into trouble. This is primarily due to querying the DB at least (number of columns * number of lines) times.

Taking a step further.

Problem number one is the easiest to solve, but it does require that we change our perspective. Until now, we thought of the table vertically (i.e., as a succession of lines), but if we think of it horizontally (i.e., as a succession of columns), we will see that the calls to the DB will be drastically lowered.

A table can be considered just a succession of hashes that we then access once per line if we think about it. Using PostgreSQL (and Rails) better, we can make each hash one call to the DB.

@full_names = User.all.pluck(:full_name).distinct
@treated_leads = Leads.treated.joins(:user).group(‘users.full_name).count
@reached_leads = Leads.reached.joins(:user).group(‘users.full_name).count
@revenue = Leads.joins(:user).group(‘users.full_name).sum(:revenue)
...
etc.

The code to our table would now be:

<table>
  <thead>
    <th>User</th>
    <th># Treated</th>
    <th># Reached</th>
    <th>Reach %</th>
    <th># Accepted</th>
    <th>Acceptance %</th>
    <th>Accepted/Treated</th>
    <th>Revenue </th>
    <th>Cost </th>
    <th>Profit</th>
    <th>Profit %</th>
  </thead>
  <tbody>
    <%= @full_names.each do |full_name| %>
      <td><%= full_name %></td>
      <td><%= @treated_leads[full_name] %></td>
      <td><%= @reached_leads[full_name] %></td>
      <td><%= @reached_leads[full_name] / @treated_leads[full_name] %></td>
      <td><%= @accepted_leads[full_name] %></td>
      <td><%= @accepted_leads[full_name] / @reached_leads[full_name] %></td>
      <td><%= @accepted_leads[full_name] / @treated_leads[full_name %></td>
      <td><%= @revenue[full_name] %></td>
      <td><%= @cost[full_name] %></td>
      <td><%= @revenue[full_name] — @cost[full_name] %></td>
      <td><%= @(revenue[full_name] — @cost[full_name] ) / @revenue[full_name] %></td>
    <% end %>
  </tbody>
</table>

As you can see, we are just making 11 calls to the DB.

But, you may ask, Pablo, isn’t this too good to be true?

Well… kind of. In all honesty, the problem you will have is that the hashes might not have all the required keys, and for ‘combined’ columns, you will have to fiddle a bit.

Also, there is no default value if a cell is empty, and it might cause issues when calculating the rates.

There are ways around that using Hash#merge, but if you don’t want to go through the trouble, see my:

Shameless plug

To avoid having to tweak it yourselves, and to have you forgive me for raising your hopes, I created a Ruby Gem that will handle that for you :)

If you want to contribute to open-source, I would be super glad to review and receive your contributions! :=)