Power of ActiveRecord and when to stop using it
Tags: ruby, rails, activerecord, sql
Published: 2019-01-31 07:55:11

---
title: Power of ActiveRecord and when to stop using it
published: true
description: 
tags: ruby, ruby on rails, activerecord, sql
---

In Rails, `ActiveRecord` makes it super easy to query for data from the database--but how well does it perform for your use case? 

# Scenario
Let's assume I have a `Project` model on my rails app. I would like to display the total number of project that are active and pending at a status against the total number of active projects on a dashboard.

Which would look something like this:
```ruby
  Team Lead Action (25/100) | Manager Action (32/100)
```

# Implementation

Thanks to `ActiveRecord`, we can easily achieve get the total count:

```ruby
  active_projects = Project.where(status: 'active')
  @total_number_of_active_projects = active_projects.size
  @projects_pending_team_lead_approval = active_projects.reject(&:team_lead_action_time).count
  @projects_pending_manager_approval = active_projects.reject(&:manager_action_time).count
```

Voila! We can easily call these variables to display them on the dashboard. Problem solved! 

However, I'm a little concerned if the amount of projects scales to a large amount. For example, just try imagining if we now need to display 5 different values, our queries will scale like this:

```ruby
active_projects = Project.where(status: 'active')
  @total_number_of_active_projects = active_projects.size
  @projects_pending_team_lead_approval = active_projects.reject(&:team_lead_action_time).count
  @projects_pending_manager_approval = active_projects.reject(&:manager_action_time).count
  @projects_pending_manager_2_approval = active_projects.reject(&:manager_2_action_time).count
  @projects_pending_manager_3_approval = active_projects.reject(&:manager_3_action_time).count
  @projects_pending_manager_4_approval = active_projects.reject(&:manager_4_action_time).count
  @projects_pending_manager_5_approval = active_projects.reject(&:manager_5_action_time).count
```

That don't look very scalable. For such simple data value, it should be retrieved efficiently. 

Let's verify things using data with the help of `Benchmark` with `N = 1000`:
```ruby

                        user     system      total        real
count_active_project  0.066184   0.108178   0.174362 (  0.182557)

```

It takes about `0.18s`. This time let's just change the query:

```ruby
  active_projects = Project.where(status: 'active')
  @total_number_of_active_projects = active_projects.size
  @projects_pending_team_lead_approval = active_projects.where(team_lead_action_time: nil).size
  @projects_pending_manager_approval = active_projects.where(manager_action_time: nil).size

```

And benchmark it: 

```ruby

                              user     system      total        real
count_active_projects_ar    0.001460   0.000166   0.001626 (  0.002965)

```

This query which does the same thing takes only `0.002s` - significantly faster. The difference is this time we are not using `.reject` - ( array method ) but instead relying on `ActiveRecord`. But why? Let's take a look at the logs. 

Using `.reject`:
```ruby
irb(main):036:0> Project.where(status: 'active').reject(&:team_lead_action_time).count
  Project Load (2.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."status" = $1  [["status", 0]]
```

Using `ActiveRecord`:
```ruby
irb(main):040:0> Project.where(status: 'active', team_lead_action_time: nil).size
   (0.9ms)  SELECT COUNT(*) FROM "projects" WHERE "projects"."status" = $1 AND "projects"."team_lead_action_time" IS NULL  [["status", 0]]
```

As we can see from the output, `.reject` load the `projects` into the memory before processing while `ActiveRecord` will let the database handle the query. Database is fast and was designed to handle such process hence the speed improvement.  

# Annnd there's raw SQL
Taking a step further, we can write raw SQL in rails.

```ruby
  query = <<~SQL
    SELECT
      SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END),
      SUM(CASE WHEN status = 0 AND team_lead_action_time IS null THEN 1 ELSE 0 END),
      SUM(CASE WHEN status = 0 AND manager_action_time IS null THEN 1 ELSE 0 END)
    FROM
      PROJECTS
  SQL

  ActiveRecord::Base.connection.execute(query)
```

And the result is:

```ruby

                              user     system      total        real
count_active_projects_sql    0.000250   0.000047   0.000297 (  0.000966)

```

Raw SQL only takes `0.0009s` which is about 20 times faster.

Taking a look at the log:

```ruby
irb(main):044:0> Project.count_active_projects_sql
   (0.8ms)  SELECT
  SUM(CASE WHEN status = 0 AND team_lead_action_time IS null THEN 1 ELSE 0 END)
FROM
  PROJECTS
=> #<PG::Result:0x00007fa3cdc1c460 status=PGRES_TUPLES_OK ntuples=1 nfields=1 cmd_tuples=1>
```

Raw SQL is significantly faster because it does not need to instantiate a new ActiveRecord objects which is slow but instead it returns a `PG::Result` object.  

If we increase `N = 100000`, the benchmark result will be:
```ruby
                              user     system      total        real
count_active_projects      4.428081   7.626659  12.054740 ( 12.177786)
count_active_projects_ar   0.002107   0.000303   0.002410 (  0.062542)
count_active_projects_sql  0.000380   0.000057   0.000437 (  0.026585)
```
We can clearly see that using raw SQL is the fastest 🏆.

# Takeaway
1) In the end, I decided to go with the raw SQL method. Putting performance in mind, raw SQL is suited in handling such scenario. 
2) Between raw SQL and `ActiveRecord`, in most cases `ActiveRecord` would be preferable for developers productivity. 
3) Be careful with the methods used. Ruby `.reject` - (array methods) are great but use them with caution.
 
Depending on the scenario, solutions may vary. We can write our code creatively and test it out to fit our needs. We need to stay curious and be creative in improving our solutions. 

** The example code can be found here https://github.com/sooyang/query_speed_example