On Tuesday, December 12, 2017 at 6:29:52 PM UTC-8, Hiren Mistry wrote:
>
> Hi Jeremy,
>
> I'm glad the post actually went through! When I posted last night, there
> was no feedback after submitting it (or I missed it) and I didn't see the
> post listed afterwards.
>
> The test is very simple and basic, nothing fancy. The test is designed so
> both AR and Sequel will retrieve the same number of records from the db. I
> didn't test your suggestion today, but I remember trying it and getting
> similar result. (I did some due diligence.)
>
> The test scripts are on Github. The test of interest to you is here
> <https://github.com/hmistry/ar_sql/blob/master/sq/bench.rb#L63>. Looking
> at the memory reports, a large number of objects created are related to
> time (ruby-2.4.2/lib/ruby/2.4.0/time.rb:367). It's the biggest "offender"
> in where(), find(), and first() queries but probably necessary.
>
Thanks, that is very helpful. One difference that may affect some tests
(though probably not the test where Sequel performs worse) is that the
Sequel migrations setup real foreign keys, whereas the ActiveRecord
migrations do not. In general when doing comparative benchmarking between
ruby libraries, you should use the same database schema. One other thing I
noticed is the README mentions seeds.rb, but the file used is seed.rb.
The main cause of the performance difference is that Sequel always
typecasts on retrieval, whereas ActiveRecord typecasts on attribute access
when using the sqlite3 and pg gems. This can be offset if you are
connecting to PostgreSQL by installing sequel_pg, an extension to Sequel's
postgres adapter that does the typecasting in C (Sequel will automatically
use it if installed).
If you update the benchmarks to include accessing all fields
Sequel:
Post.where(topic: topic).where(Sequel.lit("updated_at > ?",
from)).order(:updated_at).reverse.all.each{|p| p.id; p.topic_id; p.body;
p.created_at; p.updated_at}
# 165.470 (_ 3.0%) i/s - 832.000 in 5.033041s # SQLite
# 172.142 (_ 3.5%) i/s - 864.000 in 5.025422s # PostgreSQL
# 452.644 (_ 2.9%) i/s - 2.295k in 5.074740s # PostgreSQL with
sequel_pg
AR:
Post.where(topic: topic).where("updated_at > ?", from).order(updated_at:
:desc).to_a.each{|p| p.id; p.topic_id; p.body; p.created_at; p.updated_at}
# 217.373 (_ 4.6%) i/s - 1.092k in 5.033824s # SQLite
# 200.814 (_ 4.5%) i/s - 1.020k in 5.089933s # PostgreSQL
Alternatively, you could just not select the timestamp columns if you
aren't using them, in which case Sequel does significantly better:
Sequel:
Post.where(topic: topic).where(Sequel.lit("updated_at > ?",
from)).order(:updated_at).reverse.select(:id, :topic_id, :body).all
# 977.590 (_ 4.1%) i/s - 4.888k in 5.009168s # SQLite
# 977.275 (_ 3.7%) i/s - 4.888k in 5.009086s # PostgreSQL
# 1.050k (_ 4.7%) i/s - 5.304k in 5.063688s # PostgreSQL with
sequel_pg
AR:
Post.where(topic: topic).where("updated_at > ?", from).order(updated_at:
:desc).select(:id, :topic_id, :body).to_a
# 597.064 (_ 5.0%) i/s - 3.009k in 5.052970s # SQLite
# 523.986 (_ 5.2%) i/s - 2.652k in 5.075594s # PostgreSQL
Sequel can go even farther than AR if you need maximum performance,
allowing you to use real database prepared statements:
ps = Post.where(topic_id: :$topic).
where(Sequel.lit("updated_at > ?", :$from)).
order(:updated_at).
reverse.
select(:id, :topic_id, :body).
prepare(:select, :topic_posts_index_filtered)
bench("topic_posts_index_filtered") do
ps.call(topic: topic.id, from: from)
end
# 1.248k (_ 3.8%) i/s - 6.344k in 5.091786s # SQLite
# 1.381k (_ 3.8%) i/s - 6.916k in 5.017279s # PostgreSQL
# 1.545k (_ 5.3%) i/s - 7.738k in 5.022234s # PostgreSQL with
sequel_pg
Basically, Sequel is optimized for the good SQL practice of selecting only
the columns you need, whereas ActiveRecord is optimized for the case where
you are selecting columns you are not using.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
