vendredi 14 août 2015

Compare sql VS active record requests with Benchmark-ips

I'm trying to compare active record request with sql request in my rails project. I'm using Benchmark-ips and pry.

Here what's happen :

[4] pry(main)> Benchmark.ips do |x|
[4] pry(main)*   x.report("sql request") { User.where("EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8939, 8950]).where("NOT EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8942]).ids }
[4] pry(main)*   x.report("active record") { (User.joins(:groups).where(groups: {id: ["8939","8950"]}) - User.joins(:groups).where(groups: {id: 8942})).map(&:id) }
[4] pry(main)*   x.compare!
[4] pry(main)* end
Calculating -------------------------------------
         sql requestNameError: uninitialized constant User
from (pry):15:in `block (2 levels) in __pry__'

I did the same with the classic benchmark tool.

[65] pry(main)> Benchmark.bm do |x|
[65] pry(main)*   x.report("sql request") { User.where("EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8939, 8950]).where("NOT EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8942]).ids }
[65] pry(main)*   x.report("active record") { (User.joins(:groups).where(groups: {id: ["8939","8950"]}) - User.joins(:groups).where(groups: {id: 8942})).map(&:id) }
[65] pry(main)* end
       user     system      total        real
sql request   (1.5ms)  SELECT "users"."id" FROM "users" WHERE (EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (8939,8950))) AND (NOT EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (8942)))
  0.010000   0.010000   0.020000 (  0.027799)
active record  User Load (9.4ms)  SELECT "users".* FROM "users" INNER JOIN "groups_users" ON "groups_users"."user_id" = "users"."id" INNER JOIN "groups" ON "groups"."id" = "groups_users"."group_id" WHERE "groups"."id" IN (8939, 8950)
  User Load (0.8ms)  SELECT "users".* FROM "users" INNER JOIN "groups_users" ON "groups_users"."user_id" = "users"."id" INNER JOIN "groups" ON "groups"."id" = "groups_users"."group_id" WHERE "groups"."id" = $1  [["id", 8942]]
  0.050000   0.000000   0.050000 (  0.074597)
=> [#<Benchmark::Tms:0x007ff477ce7c28 @cstime=0.0, @cutime=0.0, @label="sql request", @real=0.02779875499982154, @stime=0.010000000000000009, @total=0.02000000000000024, @utime=0.010000000000000231>,
 #<Benchmark::Tms:0x007ff473f3f7e0 @cstime=0.0, @cutime=0.0, @label="active record", @real=0.07459704099892406, @stime=0.0, @total=0.04999999999999982, @utime=0.04999999999999982>]



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire