# How can active record be faster?

**URL:** <https://community.rubybench.org/t/how-can-active-record-be-faster/142>\
**Category:** Uncategorized\
**Created:** [June 20, 2017, 12:36am UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142 "2017-06-20T00:36:04Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [June 20, 2017, 12:36am UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/1 "2017-06-20T00:36:04Z")

</div>

Looking at: [RubyBench - Long Running Ruby Benchmark](https://rubybench.org/jeremyevans/sequel/commits?result_type=sequel/postgres_scope_where&display_count=2000&compare_with=activerecord/postgres_scope_where)

I am super confused at how Active Record is faster @bmarkons

Some questions I have:

1. Are we using sequel pg? [GitHub - jeremyevans/sequel\_pg: Faster SELECTs when using Sequel with pg](https://github.com/jeremyevans/sequel_pg)

2. Why can I not see the source for the comparison test, we got to add that…

3. I have … questions… about the actual benchmark

```auto
  User.where(name: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.")
      .where(Sequel.ilike(:email, 'foobar00%@email.com')).to_a

```

This is kind of weak, it is not doing anything with the actual data, so if you defer materialize you automatically get a huge speed boost. We should probably always generate an actual string from these benchmarks to ensure that we materialize stuff, winning on deferred materialization is kind of winning by cheating. Yes, it happens sometimes in the real world but it is a bit of a crutch, why are you even selecting data you are not going to use?

1. Why do I have to work so hard to find the comparison benchmark?

* * *

This actual feature is enormous @bmarkons you have made an excellent improvement to rubybench 🤗 let’s just nut out all the rough edges!

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 20, 2017, 8:33am UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/2 "2017-06-20T08:33:21Z")

</div>

> I am super confused at how Active Record is faster

I have run these two locally against **postgres\_scope\_where** to make myself sure and AR is indeed faster than Sequel here. Maybe doing some actual work with fetched records will show us different results. I will change both benches to construct string from data.

> 1. Are we using sequel pg?

No, all the Sequel benchmarks are being run with [GitHub - jeremyevans/sequel: Sequel: The Database Toolkit for Ruby](https://github.com/jeremyevans/sequel).  
Do you want the same setup for [GitHub - jeremyevans/sequel\_pg: Faster SELECTs when using Sequel with pg](https://github.com/jeremyevans/sequel_pg) ?

> 1. Why can I not see the source for the comparison test, we got to add that…

Will add it 👍

> Why do I have to work so hard to find the comparison benchmark?

Sure, that needs to be improved. I will work on it 👍

Thanks for feedback @sam

🙂 🎉

---

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [June 20, 2017, 12:19pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/3 "2017-06-20T12:19:34Z")

</div>

I asked Jeremy on Twitter and got:

> <https://twitter.com/jeremyevans0/status/876987720739962880>

> <https://twitter.com/jeremyevans0/status/876988253395591168>

> <https://twitter.com/jeremyevans0/status/876989190356979714>

> <https://twitter.com/jeremyevans0/status/876993103806177280>

> <https://twitter.com/jeremyevans0/status/876993345360244736>

So @bmarkons please have a look at this, also, I want you to test on local:

- Perf improvement of sequel pg

- Fixing the sequel test so it uses `like` and not `ilike`, our tests need to be consistent

- Generating a big string so nobody gains from defer materialization.

- Various bits of Jeremy’s feedback

Can you have a shot on local and report back here?

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 20, 2017, 12:34pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/4 "2017-06-20T12:34:21Z")

</div>

Now that makes sense. Sure @sam, I’ll post the results here 👍

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 20, 2017, 12:57pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/5 "2017-06-20T12:57:52Z")

</div>

Here are results i got locally:

**Sequel.ilike** : ~900 ips, 163 objects  
**Sequel.like** : ~2500 ips, 164 objects  
**Sequel.lit** : ~2500 ips, 153 objects

---

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [June 20, 2017, 1:00pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/6 "2017-06-20T13:00:21Z")

</div>

Nice, so it already beats AR big time… we better fix our specs

What kind of diff does adding sequel\_pg gem make?

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 20, 2017, 3:04pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/7 "2017-06-20T15:04:06Z")

</div>

Hmm, adding sequel\_pg doesn’t affect performance noticeably. For example Sequel.like:  
Iterations per second result is the same : ~2500 ips  
Objects: 148

I setup Gemfile with `gem 'sequel_pg', :require => 'sequel'` as noted in sequel\_pg README.  
I am not sure if I need to change something in benchmark? But I guess not.

---

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [June 20, 2017, 3:27pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/8 "2017-06-20T15:27:15Z")

</div>

Try materializing a bunch of rows to see what kind of impact it makes, especially dates. Eg: try selecting 10,000 dates from a table and then making a giant string out of it, with or without the gem.

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 21, 2017, 9:35am UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/9 "2017-06-21T09:35:33Z")

</div>

I compared it against scope\_all benchmark and there is an obvious speed up:

with sequel\_pg : 620 ips, 4026 objects  
without : 420 ips, 5032 objects

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 21, 2017, 10:04am UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/10 "2017-06-21T10:04:30Z")

</div>

After adding two more DateTime columns in model and creating string from 10,000 records:

with sequel\_pg : 680 ips, 219 objects  
without : 530 ips, 777 objects

Following spec I used:

```
require 'bundler/setup'
require 'sequel'
require_relative 'support/benchmark_sequel'

DB = Sequel.connect(ENV.fetch('DATABASE_URL'))

DB.create_table!(:users) do
  primary_key :id
  String :name, size: 255
  String :email, size: 255
  DateTime :birthday
  DateTime :graduation_day
  DateTime :created_at, null: true
  DateTime :updated_at, null: true
end

DB.add_index :users, :email, unique: true

class User < Sequel::Model
  self.raise_on_save_failure = true
  self.set_allowed_columns :name, :email, :birthday, :graduation_day, :created_at, :updated_at
end

10000.times do |i|
  User.create({
    name: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",
    email: "foobar#{"%03d" % i}@email.com",
    birthday: Time.at(i),
    graduation_day: Time.at(i)
  })
end

Benchmark.sequel("sequel/#{db_adapter}_scope_where", time: 5) do
  str = ""
  User
    .where(name: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.")
    .where(Sequel.lit('email LIKE ?', 'foobar00%@email.com')).each do |user|
      str << "name: #{user.name} email: #{user.email} birthday: #{user.birthday} graduation_day: #{user.graduation_day}\n"
    end
end
```

---

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [June 21, 2017, 2:02pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/11 "2017-06-21T14:02:04Z")

</div>

Excellent, I guess this highlights a few problems we have that I would like you to address:

- We should always be generating strings in our “selection” perf tests. We should fix all our existing specs to do so. I don’t care if we already have data for specs, we can backfill the corrections.

- We should add a spec that selects a large amount of columns from a table and generates a giant string.

- We should either always use `sequel_pg` OR optionally use it. I think for simplicity sake we should always use it, cause that is what people are using in production.

- We got to ensure parity between our AR and Sequel specs, otherwise we can not compare them, fixing up that ILIKE is urgent.

Thoughts?

cc @jeremyevans

---

<div class="post-metadata">

**Author:** ![bmarkons](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/bmarkons/32/87_2.png) [@bmarkons](https://community.rubybench.org/u/bmarkons)\
**Post date:** [June 21, 2017, 2:31pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/12 "2017-06-21T14:31:48Z")

</div>

All right, I will fix the specs first and configure sequel suite to run only with sequel\_pg (since that should be miniature change).

Later on I will configure sequel suite to run with and without sequel\_pg. That should be similar to running with and without prepared statements.

---

<div class="post-metadata">

**Author:** ![jeremyevans](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/jeremyevans/32/102_2.png) [@jeremyevans](https://community.rubybench.org/u/jeremyevans)\
**Post date:** [June 21, 2017, 3:19pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/13 "2017-06-21T15:19:57Z")

</div>

I think it makes sense to generate strings in all benchmarks, and make sure the output is the same for both (or at least have the differences not be material). It may also be worthwhile to check that the SQL used is the same or comparable to make sure you are comparing ORM differences instead of query differences, unless the ORMs have different approaches to handling similar cases (such as eager loading of associations).

Adding a spec that selects a large amount of columns makes sense. You also may want to use different column types in the spec, or separate specs for large amounts of columns, one for each type.

Always using sequel\_pg is probably best, since the Sequel postgres adapter picks it up automatically. But if you want to benchmark both with and without sequel\_pg, that’s fine too.

---

<div class="post-metadata">

**Author:** ![sgrif](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sgrif/32/110_2.png) [@sgrif](https://community.rubybench.org/u/sgrif)\
**Post date:** [July 24, 2017, 5:52pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/14 "2017-07-24T17:52:13Z")

</div>

> [@sam](#):
>
> why are you even selecting data you are not going to use?

Regardless of whether that’s the right thing to do or not, it is the most common usage pattern for Rails users, so it’s going to be the usage pattern we optimize for. I like @jeremyevans idea of materializing a subset of fields

---

<div class="post-metadata">

**Author:** ![sam](https://sea2.discourse-cdn.com/flex016/user_avatar/community.rubybench.org/sam/32/5_2.png) [@sam](https://community.rubybench.org/u/sam)\
**Post date:** [July 24, 2017, 6:15pm UTC](https://community.rubybench.org/t/how-can-active-record-be-faster/142/15 "2017-07-24T18:15:16Z")

</div>

Keep in mind our actual spec here has no `select` clause, so this is already captured

[https://rubybench.org/rails/rails/commits?result\_type=active\_record/postgres\_discourse&display\_count=2000](https://rubybench.org/rails/rails/commits?result_type=active_record/postgres_discourse&display_count=2000)

We do not have a “super optimized” AR going yet, but I would like to focus on figuring out what we have messed up on our test runner that is messing up results first.