Skip to content

Make RdvsOverlapping faster

Vincent Agnano requested to merge 1754-faster-ends-at into recette

Created by: n-b

refs #1754 (closed)

Dans mes tests rapides et peu fiables, l’impact de perf est mitigé, et ça casse des tests 😓. C’est maintenant plus rapide et correct (d’après les tests :D)

Perfs avant:

➜  rdvs git:(2a843c96) rails c
Loading development environment (Rails 6.0.4.1)
irb(main):001:0> Benchmark.realtime { Rdv.first(100).each { RdvsOverlapping.new(_1).rdvs_overlapping_rdv.count } }
=> 1.966220999835059

Perfs après:

➜  rdvs git:(1754-faster-ends-at) ✗ rails c       
Loading development environment (Rails 6.0.4.1)
irb(main):001:0> Benchmark.realtime { Rdv.first(100).each { RdvsOverlapping.new(_1).rdvs_overlapping_rdv.count } }
=> 0.8169749998487532

Je m’attendais à plus, mais on va dire que 50% c’est pas mal. C’est sur ma machine, on verra en prod comment ça marche. Il y a aussi des chances que le fait de ne faire qu’une seule requête SQL ait un impact plus important en prod.

SQL avant:

irb(main):001:0> RdvsOverlapping.new(Rdv.first).rdvs_overlapping_rdv.map{ [_1.starts_at, _1.ends_at]}
  Rdv Load (1.2ms)  SELECT "rdvs".* FROM "rdvs" ORDER BY "rdvs"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Agent Load (2.1ms)  SELECT "agents".* FROM "agents" INNER JOIN "agents_rdvs" ON "agents"."id" = "agents_rdvs"."agent_id" WHERE "agents_rdvs"."rdv_id" = $1  [["rdv_id", 1]]
  Rdv Load (4.3ms)  SELECT "rdvs"."id" FROM "rdvs" WHERE ((starts_at + (duration_in_min::text|| 'minute')::INTERVAL) BETWEEN '2021-10-10 08:00:01' AND '2021-10-10 08:30:00')
  Rdv Load (0.4ms)  SELECT "rdvs"."id" FROM "rdvs" WHERE (starts_at BETWEEN '2021-10-10 08:00:00' AND '2021-10-10 08:29:59')
  Rdv Load (2.2ms)  SELECT "rdvs"."id" FROM "rdvs" WHERE (starts_at < '2021-10-10 08:00:00') AND ((starts_at + (duration_in_min::text|| 'minute')::INTERVAL) > '2021-10-10 08:30:00')
  Rdv Load (1.5ms)  SELECT "rdvs".* FROM "rdvs" INNER JOIN "agents_rdvs" ON "agents_rdvs"."rdv_id" = "rdvs"."id" INNER JOIN "agents" ON "agents"."id" = "agents_rdvs"."agent_id" WHERE "rdvs"."id" != $1 AND "rdvs"."status" IN ($2, $3, $4) AND (starts_at > '2021-10-07 14:28:34.067366') AND "agents"."id" = $5 AND "rdvs"."id" IN ($6, $7, $8, $9) ORDER BY (starts_at + (duration_in_min::text|| 'minute')::INTERVAL)  [["id", 1], ["status", "unknown"], ["status", "waiting"], ["status", "seen"], ["id", 1], ["id", 1], ["id", 5877], ["id", 1], ["id", 5877]]
=> [[Sun, 10 Oct 2021 10:00:00 CEST +02:00, Sun, 10 Oct 2021 10:30:00 CEST +02:00]]

SQL après

irb(main):001:0> RdvsOverlapping.new(Rdv.first).rdvs_overlapping_rdv.map{ [_1.starts_at, _1.ends_at]}
  Rdv Load (1.7ms)  SELECT "rdvs".* FROM "rdvs" ORDER BY "rdvs"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Agent Load (1.8ms)  SELECT "agents".* FROM "agents" INNER JOIN "agents_rdvs" ON "agents"."id" = "agents_rdvs"."agent_id" WHERE "agents_rdvs"."rdv_id" = $1  [["rdv_id", 1]]
  Rdv Load (3.0ms)  SELECT "rdvs".* FROM "rdvs" INNER JOIN "agents_rdvs" ON "agents_rdvs"."rdv_id" = "rdvs"."id" INNER JOIN "agents" ON "agents"."id" = "agents_rdvs"."agent_id" WHERE "rdvs"."id" != $1 AND "rdvs"."status" IN ($2, $3, $4) AND (starts_at > '2021-10-07 14:36:20.611961') AND "agents"."id" = $5 AND (tsrange(starts_at, ends_at, '[]') && tsrange('2021-10-10 08:00:00', '2021-10-10 08:30:00')) ORDER BY "rdvs"."ends_at" ASC  [["id", 1], ["status", "unknown"], ["status", "waiting"], ["status", "seen"], ["id", 1]]
=> [[Sun, 10 Oct 2021 10:00:00 CEST +02:00, Sun, 10 Oct 2021 10:30:00 CEST +02:00]]

Checklist avant review:

  • reparcourir le code rapidement pour voir les problèmes évidents (fichiers touchés inutilement, debug logs qui trainent…).
  • Tester la fonctionnalité sur la review app

Merge request reports