Optimizing Performance in Active Record: A Brief Primer

Table of Contents

• What is Active Record?
• Why Should We Care About Performance?
• Common Active Record Problems
  • Memory mismanagement
  • Loading too much data
  • Unnecessary Database Queries
  • N+1 Queries
• Troubleshooting Methods

What is Active Record?

Active Record is an all-in-one database interface that is baked into Rails and is one of the framework’s biggest features. It manages your database connection, builds database queries in whatever flavor you choose (PostgreSQL, MySQL, MongoDB, etc.), and maps that raw data into your defined classes. This tool is incredibly powerful and can make your life a good deal easier; but, like any tool, it can be misused and introduce problems into your application, specifically performance issues.

Why Should We Care About Performance?

Performance can be a somewhat nebulous term that can depend on factors outside of our control (upload/download speeds, browser inconsistencies, hardware), but an app’s performance can have a major impact on the end user experience. The fact of the matter is that nobody wants to use a slow application. If your app is slow, users will complain, or worse, stop using the app altogether.

Another reason to be concerned with performance is that hardware is expensive. If your application is not designed with performance concerns in mind, it will require more CPU and more RAM to run quickly, which will translate into higher costs for you or your client.

Common Active Record Problems

Memory mismanagement

When you execute a query, you grab that data from the database and load that data into memory, which allows it to be accessed quickly within your application. This can lead to issues if you’re loading a particularly large table.

.limit

The .limit method grabs a set number of records from the database and loads them into memory. This can be useful if you only need a small subset of the data when working with a large table that can not be loaded entirely into memory.

UserLog.limit(1000)
  UserLog Load (22.3ms)  SELECT  `user_logs`.* FROM `user_logs`  LIMIT 1000
=> #<ActiveRecord::Relation [#<UserLog id: 2, user_id: 21202, user_name: "Ernesto Sanchez", branch_id: nil, url: "https://floorplanxpress.com/users/sign_in", referrer: "https://floorplanxpress.com/", params: "{\"action\":\"create\",\"authenticity_token\":\"lYA05jcJN...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:24:44", updated_at: "2014-01-15 22:24:44", controller: "login", action: "create", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "108.1.196.195", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 102, user_id: 21202, user_name: "Ernesto Sanchez", branch_id: nil, url: "https://floorplanxpress.com/access/sort", referrer: "https://floorplanxpress.com/", params: "{\"action\":\"sort\",\"controller\":\"access\"}", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:24:44", updated_at: "2014-01-15 22:24:44", controller: "access", action: "sort", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "108.1.196.195", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 202, user_id: 21202, user_name: "Ernesto Sanchez", branch_id: nil, url: "https://floorplanxpress.com/access/first_time_chec...", referrer: "https://floorplanxpress.com/", params: "{\"action\":\"first_time_check\",\"controller\":\"access\"...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:24:45", updated_at: "2014-01-15 22:24:45", controller: "access", action: "first_time_check", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "108.1.196.195", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 302, user_id: 21202, user_name: "Ernesto Sanchez", branch_id: nil, url: "https://floorplanxpress.com/access/show", referrer: "https://floorplanxpress.com/", params: "{\"action\":\"show\",\"controller\":\"access\"}", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:24:45", updated_at: "2014-01-15 22:24:45", controller: "access", action: "show", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "108.1.196.195", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 402, user_id: 7402, user_name: "JESUS GARZA", branch_id: nil, url: "https://fpxus.com/users/sign_out", referrer: "https://fpxus.com/access/show", params: "{\"_method\":\"delete\",\"action\":\"destroy\",\"authentici...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:24:58", updated_at: "2014-01-15 22:24:58", controller: "login", action: "destroy", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "75.1.155.15", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 502, user_id: 3028, user_name: "Jared Morgan", branch_id: nil, url: "https://www.floorplanxpress.com/dfp/online_users", referrer: "https://www.floorplanxpress.com/web_management/auc...", params: "{\"action\":\"fpi_redirect\",\"controller\":\"web_managem...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:25:02", updated_at: "2014-01-15 22:25:02", controller: "web_management/base", action: "fpi_redirect", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "192.168.1.65", user_type: "User", as_user_type: nil>, #<UserLog id: 602, user_id: 3028, user_name: "Jared Morgan", branch_id: nil, url: "https://www.floorplanxpress.com/remote_admin/users", referrer: "https://pdx.floorplanxpress.com/dfp/online_users", params: "{\"action\":\"users\",\"controller\":\"remote_admin\"}", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:25:08", updated_at: "2014-01-15 22:25:08", controller: "remote_admin", action: "users", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "192.168.1.65", user_type: "User", as_user_type: nil>, #<UserLog id: 702, user_id: 3247, user_name: "Sam Khader", branch_id: nil, url: "https://fpxus.com/users/sign_in", referrer: "https://fpxus.com/", params: "{\"action\":\"create\",\"authenticity_token\":\"IwJuUf2fh...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:25:10", updated_at: "2014-01-15 22:25:10", controller: "login", action: "create", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "98.199.231.5", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 802, user_id: 3247, user_name: "Sam Khader", branch_id: nil, url: "https://fpxus.com/access/sort", referrer: "https://fpxus.com/", params: "{\"action\":\"sort\",\"controller\":\"access\"}", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:25:10", updated_at: "2014-01-15 22:25:10", controller: "access", action: "sort", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "98.199.231.5", user_type: "DealerUser", as_user_type: nil>, #<UserLog id: 902, user_id: 3247, user_name: "Sam Khader", branch_id: nil, url: "https://fpxus.com/access/first_time_check", referrer: "https://fpxus.com/", params: "{\"action\":\"first_time_check\",\"controller\":\"access\"...", trusted_ip_branch_id: nil, created_at: "2014-01-15 22:25:10", updated_at: "2014-01-15 22:25:10", controller: "access", action: "first_time_check", params_id: nil, failed_username: nil, failed_password_enc: nil, latitude: nil, longitude: nil, accuracy: nil, screen_height: nil, screen_width: nil, user_agent: nil, session_id: nil, view_format: 0, template_path: nil, as_user_id: nil, pid: 0, duration: 0, auth_token_id: nil, ip_address: "98.199.231.5", user_type: "DealerUser", as_user_type: nil>, ...]>

.find_each

The .find_each method grabs data in batches (defaults to 1000, but can be set), loads them into memory, then garbage collects before fetching the next batch.

UserLog.find_each(batch_size: 5000)

Loading too much data

This is a problem common on older applications where the tables have grown to includes dozens or even hundreds of columns. As apps grow and business requirements demand more data, grabbing a set of rows with a lot of columns can put unnecessary strain on your database and on your application.

Some potential solutions to this involve utilizing the .pluck and .select methods from Active Record.

.pluck

Queries the database and returns an array of arrays containing the properties in whatever order you called them in the method.

irb(main):007:0> UserLog.limit(5).pluck(:id, :created_at, :url)
   (0.6ms)  SELECT  `user_logs`.`id`, `user_logs`.`created_at`, `user_logs`.`url` FROM `user_logs`  LIMIT 5
=> [[2, Wed, 15 Jan 2014 22:24:44 UTC +00:00, "https://floorplanxpress.com/users/sign_in"], [102, Wed, 15 Jan 2014 22:24:44 UTC +00:00, "https://floorplanxpress.com/access/sort"], [202, Wed, 15 Jan 2014 22:24:45 UTC +00:00, "https://floorplanxpress.com/access/first_time_check"], [302, Wed, 15 Jan 2014 22:24:45 UTC +00:00, "https://floorplanxpress.com/access/show"], [402, Wed, 15 Jan 2014 22:24:58 UTC +00:00, "https://fpxus.com/users/sign_out"]]

.select

Queries the database and returns an ActiveRecord::Relation (an array essentially) of instances with the data that you queried.

irb(main):008:0> UserLog.limit(5).select(:id, :created_at, :url)
  UserLog Load (0.3ms)  SELECT  `user_logs`.`id`, `user_logs`.`created_at`, `user_logs`.`url` FROM `user_logs`  LIMIT 5
=> #<ActiveRecord::Relation [#<UserLog id: 2, url: "https://floorplanxpress.com/users/sign_in", created_at: "2014-01-15 22:24:44">, #<UserLog id: 102, url: "https://floorplanxpress.com/access/sort", created_at: "2014-01-15 22:24:44">, #<UserLog id: 202, url: "https://floorplanxpress.com/access/first_time_chec...", created_at: "2014-01-15 22:24:45">, #<UserLog id: 302, url: "https://floorplanxpress.com/access/show", created_at: "2014-01-15 22:24:45">, #<UserLog id: 402, url: "https://fpxus.com/users/sign_out", created_at: "2014-01-15 22:24:58">]>

Unnecessary Database Queries

A common issue due to the ease of calling ActiveRecord methods, it can be very easy to run unnecessary queries, which will slow down performance.

.count vs. .size

.count will always make a call to your database, period. Instead, utilize .size. The .size method is “smart” in a way: if the collection is already loaded into memory, it will just get the size of the collection. Otherwise, it will call the COUNT method in SQL.

N+1 Queries

This is a very common anti-pattern among less experienced Rails developer. An N+1 query is any operation that occurs when you query the database for as many iterations as you have records you are querying, plus the first query that kicked off the operation. This pattern typically occurs when you are querying associated models.

class Branch < ActiveRecord::Base
  has_many :builds
end

class Build < ActiveRecord::Base
  belongs_to :branch
end

builds = Build.order(:finished_at).limit(10)

builds.each do |build|
  puts "#{build.branch.name} build number #{build.number}"
end

Build Load (1.7ms) SELECT "builds".* FROM "builds" ORDER BY "builds"."finished_at" ASC LIMIT 10

Branch Load (0.4ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 11]]
Branch Load (0.8ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 13]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 15]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 17]]
Branch Load (0.2ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 19]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 111]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 113]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 115]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 117]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 119]]

The above example results in 11 queries being made: the initial query on the builds table, plus a query to the branches table for each build. On a query this small, the bottleneck could be negligible, but if that limit changes from 10 to 1000000, it quickly becomes a performance concern.

.includes

One way to prevent an N+1 query is to use the .includes method, which will eager load our branches table, resulting in less queries to our database.

builds = Build.order(:finished_at).includes(:branches).limit(10)

builds.each do |build|
  puts "#{build.branch.name} build number #{build.number}"
end

Build Load (0.5ms) SELECT "builds".* FROM "builds" ORDER BY "builds"."finished_at" ASC LIMIT 10
Branch Load (0.5ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" IN (11, 13, 15, 17, 19, 111, 113, 115, 117, 119)

Troubleshooting Methods

• Built in methods (.to_sql, .explain, .analyze)
• Benchmark (part of the standard Ruby library)
• Gems, e.g. Bullet or rack-mini-profiler