Making Tables Faster

  • Ruby (on Rails)
  • Postgresql

Setting the stage

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

  • In treatment
  • Accepted
  • Refused
  • New
  • 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)
example table

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>

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.

@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)
<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>

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 :)

easy_data_tables github readme



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pablo Curell Mompo

Pablo Curell Mompo

Full Stack developer @ Seraphin, learned how to code @ Le Wagon. I love coding and plan to keep learning as much as I can about it :)