Aujourd’hui nous allons nous intéresser aux choix que nous faisons pour réaliser nos requêtes en base, dans le cadre d’un projet rails.

Il peut nous arriver de voir notre application présenter des lenteurs en production dès qu’il commence à y avoir une grande quantité de données en base. A partir de ce moment, selon les applications, nous allons nous tourner en premier lieu vers les requêtes SQL. Si elles sont à l’origine de ce ralentissement alors la question qui se pose est « dois-je ré-écrire la requête SQL à la main pour me passer de Ruby ? »

Pourquoi se poser cette question et ne pas coder toutes les requêtes en SQL ? Le framework Rails et notamment ActiveRecord nous permettent de simplifier l’écriture de nos requêtes, de les rendre plus maintenable et lisible. Il est donc plus confortable d’utiliser ActiveRecord dans un premier temps.

Pour une requête précise, nous allons déterminer à partir de quel moment nous devons prendre le temps de créer nos requêtes SQL à la main. Pour cela, nous allons réaliser des benchmarks en fonction du type de requête (ActiveRecord / Ruby ou SQL) et de la quantité de données à traiter pour une requête largement utilisée au travers de l’application.

Voici le sommaire de la suite d’articles à venir :

• partie 1 : Mise en place du contexte et génération des données nécessaires aux tests
• partie 2 : Le code de notre benchmark
• partie 3 : Tests et interprétation des résultats

Dans ce premier article, nous allons donc poser le contexte et voir comment générer nos données.

Contexte

Les tests sont réalisés sur un Mac mini 2,6 Ghz i7, 8Go DDR3, Rails 4.1.2, ruby 2.1.1 et une base Postgresql.

Afin de réaliser les tests, j’ai choisi d’utiliser une association assez simple, l’association 1-N. Pour modéliser cette association, nous allons prendre l’exemple de posts auxquels des utilisateurs vont pouvoir attribuer des notes sur cinq.

# app/models/post.rb
class Post < ActiveRecord::Base
  has_many :ratings
end

# app/models/rating.rb
class Rating < ActiveRecord::Base
  belongs_to :post
end

Pour nos tests, nous avons limité les attributs de nos modèles au minimum, à savoir:

• Post(id: integer, created_at: datetime)
• Rating(id: integer, score: integer, post_id: integer)

Ceci n’est qu’un exemple, les articles peuvent devenir des voitures, des photos ou même des lapins de Patagonie… Enfin vous l’avez compris, cette relation se retrouvera dans toutes vos applications.

Requête concernée

Nous allons nous intéresser à trier nos articles dans l’ordre de leur moyenne de votes. Créons donc deux méthodes de classe, l’une en ActiveRecord pur et l’autre en SQL :

# app/models/post.rb
class Post < ActiveRecord::Base
  has_many :ratings

  # ActiveRecord
  def self.order_by_rating_ar 
    includes(:ratings).group('posts.id').sort_by(&:avg_rating)
  end

  # SQL
  def self.order_by_rating_sql
    find_by_sql("
      SELECT posts.*, AVG(ratings.score) AS average FROM posts
      LEFT OUTER JOIN ratings ON posts.id = ratings.post_id
      GROUP BY posts.id, posts.created_at
      ORDER BY average DESC NULLS LAST, posts.created_at DESC")
  end

  def avg_rating
    ratings.average(:score) || 0
  end
end

Ici nous comprenons mieux la différence en terme de lisibilité et de simplicité du code.

Dans notre requête AR, nous utilisons la méthode Ruby sort_by qui instancie tous les objets de la requête (ici tous les objets de la base) dans un tableau pour ensuite les trier.

Si nous avions voulu éviter de faire le tri en Ruby et avoir une requête 100% ActiveRecord / SQL, il aurait fallu remplacer .sort_by par .order. Il aurait également fallu écrire du SQL à la main ce qui dans notre cas nous intéresse pas.

Les Données

Afin de réaliser nos benchmarks nous devons peupler notre base de données. Nos benchmarks vont être réalisés plusieurs fois en faisant varier la quantité de données à traiter.

Il faut donc que la base de données soit vidée et repeuplée avec la bonne quantité de donnée après chaque benchmark.

Nous déterminons l’intervalle du nombre de données à traiter de 0 à 2 millions de posts avec chacun 3 votes ( soit 6 millions de votes).

Dans un premier temps, je me suis dit que j’allais simplement créer les données par le biais de “factories” en créant la tâche rake suivante :

# /lib/tasks/sample.rb
namespace :sample do
  desc "Fill database with sample data"
  task :populate => :environment do
    # Supprimons les données
    Post.delete_all
    Rating.delete_all

    2000000.times do
      Post.create!
    end

    Post.find_each do |p|
      3.times do |t|
        p.ratings.create!(score: (1..5).to_a.sample)
      end
    end
  end
end

Prêt pour générer 2 millions de posts et 6 millions de votes de tests, je lance donc ma tâche rake et là … j’attends, …. j’attends, au bout d’environ 120 minutes mes données sont générées.

Cette méthode instancie chacun des objets en Ruby (soit 2 + 6 = 8 millions) et chaque objet va générer une requête SQL (soit 8 millions de requêtes).

L’insertion des données est trop longue, car pendant nos benchmarks nous allons effacer et recréer plusieurs fois notre base de données. Cette solution est pratique et rapide à mettre en place, mais ne correspond pas à notre besoin actuel.

Afin d’améliorer ce temps d’insertion, nous codons notre requête en SQL pur :

# /lib/tasks/sample.rb
namespace :sample do
  desc "Fill database with sample data"
  task :populate => :environment do
    # Supprimons les données
    Post.delete_all
    Rating.delete_all

    # Mise en place des données pour le prochain benchmark
    sql_post = "insert into posts
      select t.id as id,
      now() as created_at
      from (select * from generate_series(1,2000000) as id)
      as t;"
    ActiveRecord::Base.connection.execute(sql_post)

    sql_rating = "insert into ratings (score, post_id, created_at, updated_at)
      select (random() * 5)::integer as score, t.post_id as post_id
      from (select posts.id as post_id from posts, (select * from generate_series(1,3) as id) as p) as t;"
    ActiveRecord::Base.connection.execute(sql_rating)
  end
end

Prenons l’exemple des posts dans le détail:

insert into posts      -- insertion dans la table posts
select
  t.id  as id,         -- définition des colonnes a insérer
  now() as created_at  --
from (
  select *
  from generate_series(1,2000000) as id
) as t;

generate_series est une fonction PostgreSQL qui permet de générer des séries de nombre entiers. Elle prend comme argument, la valeur de départ (ici 1), la valeur maximale (2000000) et le saut (intervalle, par défaut 1).

La génération de données prend environ 4 minutes soit 30 fois plus rapide que notre code AR. Ici aucun objet Ruby n’est instancié et une seule requête est envoyé à PostgreSQL, ce qui explique le gain de temps.

Dans la prochaine partie nous aborderons la mise en place de notre code pour le benchmark.

L’équipe Synbioz.

Libres d’être ensemble.