Après une discussion sur le Slack de Paris.rb à propos des perfs des requêtes Active Record vs raw SQL, j’ai refait un rapide benckmark des solutions qu’on a lorsqu’on veut extraire beaucoup de données d’une base pour effectuer des opération dessus. Comme par exemple sortir des rapports, tracer des graphiques etc.
Pour l’opération, j’ai pris un simple *2 qui n’est là que pour l’exemple mais j’en ai profité pour comparer aussi les temps de cette multiplication entre l’exécution sur le serveur ruby ou sur le serveur de base de données.
Tests réalisés pour 250 et 100k records, en sqlite et PostgreSQL.
250 records, en local, sqlite
#select
puts Benchmark.measure { 10000.times {User.select(:id).map{|u| u.id*2}} }
45.170000 0.410000 45.580000 ( 45.900239)
#raw sql
puts Benchmark.measure { 10000.times {
ActiveRecord::Base.connection.execute("SELECT `users`.`id` FROM `users`").map{|u| u["id"]*2}}
}
12.340000 0.270000 12.610000 ( 12.648145)
#pluck
puts Benchmark.measure { 10000.times {User.pluck(:id).map{|id| id*2}} }
7.390000 0.350000 7.740000 ( 7.803935)
#select_value
puts Benchmark.measure { 10000.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id` FROM `users`").map{|id| id*2}}
}
4.560000 0.230000 4.790000 ( 4.803681)
#select_values with sql operation
puts Benchmark.measure { 10000.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id`*2 FROM `users`")}
}
4.560000 0.200000 4.760000 ( 4.782249)On voit sans grande surprise que le select prend largement plus de temps.
100k, en local, sqlite
Voyons ce que ça donne avec 100k éléments, toujours sur un sqlite en local :
#Parce que ça va quand même plus vite avec une seule transaction
ActiveRecord::Base.transaction{100_000.times {User.create}}Comme les opérations prennent significativement plus de temps, j’ai dû réduire le nombre d’itération (je t’ai dis que c’était un bench rapide 😛).
#select (⚠️ only 100 iterations)
puts Benchmark.measure { 100.times {User.select(:id).map{|u| u.id*2}} }
142.750000 0.220000 142.970000 (143.872549)
#raw sql
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.execute("SELECT `users`.`id` FROM `users`").map{|u| u["id"]*2}}
}
243.680000 1.510000 245.190000 (246.491226)
#pluck
puts Benchmark.measure { 500.times {User.pluck(:id).map{|id| id*2}} }
86.190000 1.080000 87.270000 ( 87.819341)
#select_values
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id` FROM `users`").map{|id| id*2}}
}
58.950000 1.090000 60.040000 ( 60.468712)
#select_values with sql operation
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id`*2 FROM `users`")}
}
54.400000 1.020000 55.420000 ( 55.676626)Le select prend presque 3 fois plus de temps que le raw sql
pluck et select_values sont clairement plus intéressants ici. Je ne m’étend pas plus dessus ça reste du sqlite.
100k, PostgreSQL, Heroku
Mais comme on sait bien qu’un sqlite en local ne représente pas la réalité de l’app qu’on aura en prod, voilà le même test sur un heroku (free dyno) + pqsql (hobby dev), toujours avec 100k records :
#select (⚠️ only 100 iterations)
puts Benchmark.measure { 100.times {User.select(:id).map{|u| u.id*2}} }
221.890000 1.610000 223.500000 (232.301079)
#raw sql
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.execute("SELECT users.id FROM users").map{|u| u["id"]*2}}
}
48.690000 4.990000 53.680000 ( 81.522866)
#pluck
puts Benchmark.measure { 500.times {User.pluck(:id).map{|id| id*2}} }
52.020000 5.160000 57.180000 ( 84.834389)
#select_values
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id` FROM `users`").map{|id| id*2}}
}
15.340000 2.740000 18.080000 ( 57.887047)
#select_values with sql operation
puts Benchmark.measure { 500.times {
ActiveRecord::Base.connection.select_values("SELECT users.id*2 FROM users")}
}
11.760000 2.900000 14.660000 ( 65.014972)On retrouve toujours des temps pour select » (raw sql et pluck) > select_values. Par contre, la multiplication est maintenant plus lente via pgsql que via ruby. J’ai envie de mettre ça sur le dos de la base peut-être un peu lente en version gratuite chez Heroku.
100k, MySQL, local
[EDIT]: J’ajoute le même bench sur MySQL à la demande de @alexnarb et sous forme de tableau cette fois 🙂
n = 1000
Benchmark.bm do |x|
x.report("select") { n.times {
User.select(:id).map{|u| u.id*2}
}}
x.report("raw sql") { n.times {
ActiveRecord::Base.connection.exec_query("SELECT users.id FROM users").map{|u| u["id"]*2}
}}
x.report("pluck") { n.times {
User.pluck(:id).map{|id| id*2}
}}
x.report("select_values") { n.times {
ActiveRecord::Base.connection.select_values("SELECT `users`.`id` FROM `users`").map{|id| id*2}
}}
x.report("select_values with sql operation") { n.times {
ActiveRecord::Base.connection.select_values("SELECT users.id*2 FROM users")
}}
end
user system total real
select 124.910000 0.090000 125.000000 (128.181143)
raw sql 15.060000 0.070000 15.130000 ( 17.766282)
pluck 9.880000 0.090000 9.970000 ( 12.579079)
select_values 5.270000 0.050000 5.320000 ( 7.832085)
select_values with sql operation 4.870000 0.080000 4.950000 ( 7.618461)On reste sur la même conclusion qu’avant.
Si tu es attentif, tu as vu que j’utilise exec_query au lieu de execute pour MySQL. Pourquoi ? execute ne renvoie pas les résultats de la requête quand on utilise l’adaptateur mysql2.
Mais alors, il y a une différence de perf entre les 2 sur du pgSQL ? Oui, moi aussi je me suis posé la question (toujours sur heroku, 100k records).
n = 10000
Benchmark.bm do |x|
x.report("execute") { n.times {
ActiveRecord::Base.connection.execute("SELECT users.id FROM users").map{|u| u["id"]*2}
}}
x.report("exec_query") { n.times {
ActiveRecord::Base.connection.exec_query("SELECT users.id FROM users").map{|u| u["id"]*2}
}}
end
user system total real
execute 17.640000 2.500000 20.140000 ( 41.666590)
exec_query 21.440000 2.680000 24.120000 ( 39.037137)L’écart n’est pas énorme, j’ai relancé le bench plusieurs fois et on tourne autours de 10%.
Conclusion et TL;DR
⚠️ attention, j’enfonce des portes ouvertes
- Ça vaut le coup de prendre 5 min pour faire un bench avec un grand nombre de records
- Il vaut mieux éviter les select et préférer pluck ou mieux select_values
- Il faut faire le bench sur la même techno de base que la prod
Merci à @mickeyben pour l’ajout de select_value 🙂