Thanks for catching the typo and missing DB constraint. I'll update them accordingly. Rails added FK constraints since 4.2 but I forgot to add them in the migration.
So what you're saying and showing, is basically it's typecasting that's the bottleneck and more specifically timestamps in my example. The speed drops from ~1K to 170 without the sequel_pg gem. What makes timestamps so expensive compared to other data types if I may ask? It's interesting to learn that AR delays typecasting to attribute access. I noticed it delaying things when calling queries and so I made sure that the DB queries were actually made and data gathered - that's why I forced some queries to return an array vs ARCollectionProxy thing. Are there other such optimizations (i.e. delay computation till needed) in AR and Sequel that I should be aware of in benchmarking? Thanks for all your feedback. Hiren. On Tuesday, December 12, 2017 at 10:18:06 PM UTC-8, Jeremy Evans wrote: > > 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.
