Includes versus Joins: een vuistregel

Gepubliceerd op 14 november 2011 door Rene

In Ruby on Rails applicaties is het gebruik van relaties tussen objecten een vrij algemeen begrip. Een minder bekend gedeelte is de techniek die hier achter schuil gaat: het gebruik van een LEFT OUTER JOIN in de database en het gewicht van een dergelijke operatie. Door een keuze te maken tussen de includes en joins methode van ActiveRecord kun je de gebruikte join beïnvloeden. In dit artikel zullen we de gevolgen van deze operators demonstreren met een testcase en geven we een vuistregel die helpt bij het kiezen tussen includes en joins.

In SQL zijn de meest gangbare manieren van het koppelen van tabellen de LEFT OUTER JOIN en de INNER JOIN. Het belangrijkste verschil tussen de twee is dat bij een OUTER JOIN het niet verplicht is dat een overeenkomend record tussen de gekoppelde tabellen aanwezig is.

Scenario

Het verschil tussen een LEFT OUTER en INNER join is het beste te demonstreren met een testcase. Laten we uitgaan van een omgeving met studenten (Student), klassen (Classroom) en leraren (Teacher). Elke student heeft over een aantal jaar ingeschreven gestaan in een aantal klassen (Enrollment) en een klas wordt geleid door meerdere leraren (ClassroomTeacher).

In Ruby on Rails ziet onze applicatie er als volgt uit:

Student

class Student < ActiveRecord::Base
  has_many :enrollments
  has_many :classrooms, :through => :enrollments
  
  validates_presence_of :name
end

Enrollment

class Enrollment < ActiveRecord::Base
  belongs_to :student
  belongs_to :classroom
end

Classroom

class Classroom < ActiveRecord::Base
  has_many :classroom_teachers
  has_many :teachers, :through => :classroom_teachers

  has_many :enrollments
  has_many :students, :through => :enrollments

  validates_presence_of :year
end

ClassroomTeacher

class ClassroomTeacher < ActiveRecord::Base
  belongs_to :classroom
  belongs_to :teacher
end

Teacher

class Teacher < ActiveRecord::Base
  has_many :classroom_teachers
  has_many :classrooms, :through => :classroom_teachers

  validates_presence_of :name
end

Om de applicatie wat context te geven hebben we hem gevuld door gebruik te maken van de gem factory_randomizer. Deze gem stelt ons in staat een database te vullen met willekeurige data.

4000.times do
  Classroom.create(:year => Randomizer.number(:min => 1980, :max => 2010))
end

250.times do 
  t = Teacher.new(:name => Randomizer.full_name)
  2.times do
    t.classrooms << Classroom.order("rand()").limit(1).first
  end
  t.save
end

100000.times do
  s = Student.new(:name => Randomizer.full_name)
  5.times do
    s.classrooms << Classroom.order("rand()").limit(1).first
  end
  s.save
end

We hebben nu een applicatie met 100.000 studenten die elk in 5 van de 4000 willekeurige klassen ingeschreven zijn geweest. Elk van deze klassen is geleid door een tweetal leraren.

Testcase

Om het verschil tussen een LEFT OUTER en INNER join duidelijk te maken zoeken we naar alle studenten die in 1989 of 1995 les hebben gehad van Monica Slater of Philip Rhodes. Aangezien Rails 3.1 slim genoeg is om pas de SQL uit te voeren op het moment dat de verzameling gebruikt wordt vragen we van elke student de naam op.

In Ruby on Rails is dit geplaatst in een Rake taak:

require 'timer'

task :fetch_students => :environment do
  years = [1989, 1995]
  teachers = ["Monica Slater", "Philip Rhodes"]

  total_duration_includes = 0
  total_duration_joins = 0
  total_runs = 100
  ignore_runs = 10

  total_runs.times do |index|
    duration_includes = Timer.time do
      # .includes = LEFT OUTER JOIN
      Student.includes(:enrollments => { :classroom => :teachers }).
              where(:classrooms => { :year => years }).
              where(:teachers => { :name => teachers}).
              all.map(&:name)
    end
    
    duration_joins = Timer.time do
      # .joins = INNER JOIN
      Student.joins(:enrollments => { :classroom => :teachers }).
              where(:classrooms => { :year => years }).
              where(:teachers => { :name => teachers}).
              all.map(&:name)
    end
    
    # allow the database to 'warm up'
    if index > ignore_runs
      total_duration_includes += duration_includes
      total_duration_joins += duration_joins
    end
  end
  
  puts "Average: #{total_runs - ignore_runs} runs"
  puts "Includes: #{sprintf("%.2f", total_duration_includes)}"
  puts "Joins: #{sprintf("%.2f", total_duration_joins)}"
  puts "Remaining load: #{sprintf("%.2f", (100 / total_duration_includes) * total_duration_joins)} %"
end

De taak zal 100 maal de studenten op verschillende manieren ophalen. De eerste 10 pogingen worden buiten beschouwing gelaten, om de database server de kans te geven de gegevens in de cache op te slaan.

rake fetch_students

Average: 90 runs
Includes: 0.31
Joins: 0.17
Remaining load: 54.44 %

40% winst

In deze simpele test behalen we door enkel het woord ‘includes’ te vervangen door ‘joins’ meer dan 40% snelheidswinst. Dit verschil kan zelfs nog groter worden als:

De database wordt actief gebruikt (waardoor tabellen / records regelmatig in geblokkeerd worden door andere transacties)
De gebruikte gegevens meer dan alleen een id en een naam zijn

Wanneer toepassen?

Er is een groot verschil tussen het resultaat van een LEFT OUTER en INNER JOIN. Het belangrijkste verschil: De relatie hoeft bij een LEFT OUTER JOIN niet te bestaan, waar deze bij een INNER JOIN verplicht is. Onderstaand voorbeeld laat zien dat bij een kleine tabel met boeken met daaraan de auteurs gekoppeld. Merk op dat er geen auteur is gekoppeld aan boek 3 ‘The Magic Of Reality’.

mysql> SELECT * FROM books;
+----+-----------------------+-----------+
| id | name                  | author_id |
+----+-----------------------+-----------+
|  1 | A Dance with Dragons  |         1 |
|  2 | The Affair            |         2 |
|  3 | The Magic Of Reality  |      NULL |
+----+-----------------------+-----------+
3 rows in set

mysql> SELECT * FROM authors;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | George R.R. Martin |
|  2 | Lee Child          |
+----+--------------------+
2 rows in set

mysql> SELECT * FROM books INNER JOIN authors ON authors.id = books.author_id;
+----+----------------------+-----------+----+--------------------+
| id | name                 | author_id | id | name               |
+----+----------------------+-----------+----+--------------------+
|  1 | A Dance with Dragons |         1 |  1 | George R.R. Martin |
|  2 | The Affair           |         2 |  2 | Lee Child          |
+----+----------------------+-----------+----+--------------------+
2 rows in set

mysql> SELECT * FROM books LEFT OUTER JOIN authors ON authors.id = books.author_id;
+----+-----------------------+-----------+------+--------------------+
| id | name                  | author_id | id   | name               |
+----+-----------------------+-----------+------+--------------------+
|  1 | A Dance with Dragons  |         1 |    1 | George R.R. Martin |
|  2 | The Affair            |         2 |    2 | Lee Child          |
|  3 | The Magic Of Reality  |      NULL | NULL | NULL               |
+----+-----------------------+-----------+------+--------------------+
3 rows in set

Beide queries vragen om alle boeken met daaraan gekoppeld de auteurs. De query met de INNER JOIN stelt alleen de verplichting dat er een auteur is. Deze methode kan dus alleen gebruikt worden als met 100% zekerheid is te zeggen dat de relatie aanwezig moet zijn. Het is uiteraard wel mogelijk om LEFT OUTER en INNER JOINS te combineren.

Een vuistregel om te onthouden:

Gebruik een .joins als de condities bij het ophalen de relatie verplicht maken.

Reacties

blog comments powered by Disqus

Weblog archief

20 januari 2012
Beheer van ontwikkelomgeving met Foreman
25 november 2011
E-mail spamfilters in prijs verlaagd
22 november 2011
Applicatieversies met git describe
14 november 2011
Includes versus Joins: een vuistregel
07 november 2011
Bluerail sponsort Music BOXtel
21 oktober 2011
Hudson Continuous Integration testing met Rails 3 en RVM
05 oktober 2011
Ruby 1.9.2 nu standaard bij virtual hosting
16 september 2011
Ruby on Rails object id's in Youtube-stijl, met Redis
26 augustus 2011
Waarschuwing bij een Capistrano production deploy
19 augustus 2011
Achtergrondtaken en versiebeheer: Whenever gem
15 augustus 2011
Het Bluerail weblog, aangedreven door Jekyll
30 mei 2011
Nu nog meer schijfruimte bij het Business pakket
06 april 2011
Ruby 1.9.2 ondersteuning bij Bluerail

Follow @BluerailNL