Share
CRUD! What to do When Active Record, MySQL, and Your Data Betray You
Slideshow for talk at Mountain West Ruby Conference March 21st, 2014.
Related blog post: http://www.eileencodes.com/posts/crud-what-to-do-when-active-record-mysql-and-your-data-betray-you
The magic of ActiveRecord database interactions is easy to rely on and allows us assume it knows best. Without a solid understanding of how ActiveRecord translates into MySQL, however, significant issues can arise. This is particularly true with large data sets and complex model relationships. My talk explores an example for each CRUD function and shows how these queries can result in MySQL timeouts, memory issues or stack level too deep errors. The examples will examine the consequences of chaining large datasets, uses for Arel, and how to avoid encountering major problems and most importantly, how these queries can be rewritten to run more efficiently.
Eileen M. Uchitelle
Transcript
92 slides
-
CRUD! What to do When Active Record, MySQL, and Your
Data Betray You -
When MySQL crashed or my queries were slow I felt
betrayed by Active Record. -
But I knew Active Record and I could work it
out So I began studying my queries more closely... -
Address Book Example ERD Users first_name string last_name string email
string password_digest string ...[etc] Contacts first_name string last_name string birthday date user_id integer ...[etc] Categories name string description string user_id integer Categorizations contact_id integer category_id integer -
Address Book Example User has_many :contacts has_many :categories Contact belongs_to
:user has_many :categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category belongs_to :user has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
Address Book Example User has_many :contacts has_many :categories Contact belongs_to
:user has_many :categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category belongs_to :user has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
Address Book Example User has_many :contacts has_many :categories Contact belongs_to
:user has_many :categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category belongs_to :user has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
Address Book Example User has_many :contacts has_many :categories Contact belongs_to
:user has_many :categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category belongs_to :user has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
Address Book Example User has_many :contacts has_many :categories Contact belongs_to
:user has_many :categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category belongs_to :user has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
Let’s create 10,000 records the Active Record way using a
CSV CSV.foreach("#{filepath}", headers: true) do |csv| Contact.create!({ first_name: csv[0], last_name: csv[1], birthday: csv[2], ... }) end -
Let’s create 10,000 records the Active Record way using a
CSV CSV.foreach("#{filepath}", headers: true) do |csv| Contact.create!({ first_name: csv[0], last_name: csv[1], birthday: csv[2], ... }) end INSERT INTO `contacts` (`first_name`, `last_name`, `birthday`,...) VALUES ('John', 'Smith', '1987-02-01',...); -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert contact_values
= [] CSV.foreach("#{filepath}", headers: true) do |csv| contact_values << "('#{csv[0]}','#{csv[1]}','#{csv[2]}'...)" end batch_size = 2000 while !contact_values.empty? contacts_shifted = contact_values.shift(batch_size) contacts_sql = "INSERT INTO contacts(first_name, last_name, birthday,...) VALUES#{contacts_shifted.join(", ")}" ActiveRecord::Base.connection.execute(contacts_sql) end -
Let’s create the same contacts with MySQL Batch Insert ActiveRecord::Base.connection.execute(contacts_sql)
INSERT INTO contacts(first_name, last_name, birthday,...) VALUES('Lauretta','Senger','1987-02-01',...), ('Jane','Roob','1987-02-01',...), ('Blaze','Lakin','1987-02-01',...), ('Elton','Cormier','1987-02-01',...), ('John','Kohler','1987-02-01',...), ('Clementine','Marvin','1987-02-01',...), ('Ova','Aufderhar','1987-02-01',...) ... -
That’s a huge difference ActiveRecord Benchmark Data: user system total
real => 44.740000 1.180000 45.920000 ( 51.095556) -
That’s a huge difference ActiveRecord Benchmark Data: user system total
real => 44.740000 1.180000 45.920000 ( 51.095556) MySQL Batch Insert Benchmark Data: user system total real => 2.710000 0.050000 2.760000 ( 3.227031) -
Let’s read some data and output the first name of
each contact Contact.where(user_id: 1, country: “USA”).each do |contact| puts contact.first_name end Contact.where(user_id: 1, country: “USA”).find_each do |contact| puts contact.first_name end Contact.where(user_id: 1, country: “USA”) .pluck(:first_name).each do |first_name| puts first_name end -
Let’s read some data and output the first name of
each contact Contact.where(user_id: 1, country: “USA”).each do |contact| puts contact.first_name end SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`user_id` = 1 AND `contacts`.`country` = 'USA' => Lauretta Shana Jason Jermain Blaze Jessy Bradly ... -
Let’s read some data and output the first name of
each contact Contact.where(user_id: 1, country: “USA”).find_each do |contact| puts contact.first_name end SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`user_id` = 1 AND `contacts`.`country` = 'USA' ORDER BY `contacts`.`id` ASC LIMIT 1000 => Lauretta Shana Jason ... SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`user_id` = 1 AND `contacts`.`country` = 'USA' AND (`contacts`.`id` > 1001) ORDER BY `contacts`.`id` ASC LIMIT 1000 => ... -
Let’s read some data and output the first name of
each contact f Contact.where(user_id: 1, country: “USA”) .pluck(:first_name).each do |first_name| puts first_name end SELECT `contacts`.`first_name` FROM `contacts` WHERE `contacts`.`user_id` = 1 AND `contacts`.`country` = 'USA' => ["Lauretta", "Shana", "Jason", "Jermain", "Blaze", "Jessy", "Bradly", "Emma", "Cruz", "Elton", "Dashawn", "Rosanna", "Ryan", "Leonel", "Ashly", "Mittie", "Tobin", "Antonio", "Chad", "Lauryn", "Sydnie", "Sebastian", "Johnpaul", "Yasmeen", "Junior", "Monroe", "Avery",...] -
Let’s read some data and output the first name of
each contact Contact.where(user_id: 1, country: “USA”).each do |contact| puts contact.first_name end Contact.where(user_id: 1, country: “USA”).find_each do |contact| puts contact.first_name end Contact.where(user_id: 1, country: “USA”) .pluck(:first_name).each do |first_name| puts first_name end -
Which one was fastest? .each Benchmark Data: user system total
real => 0.950000 0.060000 1.010000 ( 1.050266) -
Which one was fastest? .each Benchmark Data: user system total
real => 0.950000 0.060000 1.010000 ( 1.050266) .find_each Benchmark Data: user system total real => 0.900000 0.040000 0.940000 ( 0.976979) -
Which one was fastest? .each Benchmark Data: user system total
real => 0.950000 0.060000 1.010000 ( 1.050266) .find_each Benchmark Data: user system total real => 0.900000 0.040000 0.940000 ( 0.976979) .pluck Benchmark Data: user system total real => 0.080000 0.020000 0.100000 ( 0.126814) -
Which one was fastest? .each Benchmark Data: user system total
real => 0.950000 0.060000 1.010000 ( 1.050266) .find_each Benchmark Data: user system total real => 0.900000 0.040000 0.940000 ( 0.976979) .pluck Benchmark Data: user system total real => 0.080000 0.020000 0.100000 ( 0.126814) -
Statistics for Read 0 3.8 7.5 11.3 15.0 0 10
1000 10000 100000 .each .find_each .pluck -
Utilizing find_by_sql Contact.find_by_sql([ "SELECT first_name FROM contacts WHERE user_id=? AND
country=? AND birthday > ?", 1, 'USA','1987-01-01' ]).each do |contact| puts contact.first_name end -
Let’s update the category in each categorization category = Category.where(name:
“Networking”).first Categorization.all.each do |categorization| categorization.update_attributes( category_id: category.id ) end -
Let’s update the category in each categorization category = Category.where(name:
“Networking”).first Categorization.all.each do |categorization| categorization.update_attributes( category_id: category.id ) end UPDATE `categorizations` SET `category_id` = 1 WHERE `categorizations`.`id` = 1 UPDATE `categorizations` SET `category_id` = 1 WHERE `categorizations`.`id` = 2 UPDATE `categorizations` SET `category_id` = 1 WHERE `categorizations`.`id` = 3 -
Let’s update the category in each categorization category = Category.where(name:
“Networking”).first Categorization.update_all( category_id: category.id ) -
Let’s update the category in each categorization category = Category.where(name:
“Networking”).first Categorization.update_all( category_id: category.id ) UPDATE `categorizations` SET `categorizations`.`category_id` = 1 -
Check out the difference update_attributes Benchmark Data: user system total
real => 12.990000 0.870000 13.860000 ( 17.156265) -
Check out the difference update_attributes Benchmark Data: user system total
real => 12.990000 0.870000 13.860000 ( 17.156265) update_all Benchmark Data: user system total real => 0.000000 0.000000 0.000000 ( 0.042140) -
destroy_all vs. delete_all with has_many dependencies Contact has_many :categorizations has_many
:categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations Categorizations belongs_to :contact belongs_to :category -
f f destroy_all vs. delete_all with has_many dependencies Contact has_many
:categorizations, dependent: :destroy has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :destroy has_many :contacts, through: :categorizations Contact.destroy_all -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations, dependent: :destroy has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :destroy has_many :contacts, through: :categorizations Contact.destroy_all SELECT `categorizations`.* FROM `categorizations` WHERE `categorizations`.`contact_id` = 1 DELETE FROM `categorizations` WHERE `categorizations`.`id` = 1 DELETE FROM `contacts` WHERE `contacts`.`id` = 1 -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Contact.delete_all -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations Contact.delete_all DELETE FROM `contacts` -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations has_many :categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations Contact.delete_all Contact.destroy_all -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations has_many :categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations Contact.delete_all DELETE FROM `contacts` Contact.destroy_all -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations has_many :categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations Contact.delete_all Contact.destroy_all DELETE FROM `contacts` WHERE `contacts`.`id` = 1 DELETE FROM `contacts` WHERE `contacts`.`id` = 2 DELETE FROM `contacts` WHERE `contacts`.`id` = 3 -
destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first
category.contacts.destroy_all -
destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first
category.contacts.destroy_all It looks like you’re trying to delete some records, do you need help with that? -
destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first
category.contacts.destroy_all category.categorizations.destroy_all It looks like you’re trying to delete some records, do you need help with that? -
destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first
category.contacts.destroy_all category.categorizations.destroy_all category.categorizations.delete_all It looks like you’re trying to delete some records, do you need help with that? -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations has_many :categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations category.categorizations.delete_all -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations has_many :categories, through: :categorizations Category has_many :categorizations has_many :contacts, through: :categorizations category.categorizations.delete_all UPDATE `categorizations` SET `categorizations`.`category_id` = NULL WHERE `categorizations`.`category_id` = 1 -
destroy_all vs. delete_all with has_many dependencies Contact has_many :categorizations, dependent:
:destroy has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :destroy has_many :contacts, through: :categorizations category.categorizations.delete_all f f -
destroy_all vs. delete_all with has_many dependencies Contact has_many :categorizations, dependent:
:destroy has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :destroy has_many :contacts, through: :categorizations category.categorizations.delete_all DELETE FROM `categorizations` WHERE `categorizations`.`id` = 1 DELETE FROM `categorizations` WHERE `categorizations`.`id` = 2... f f -
destroy_all vs. delete_all with has_many dependencies f f Contact has_many
:categorizations, dependent: :delete_all has_many :categories, through: :categorizations Category has_many :categorizations, dependent: :delete_all has_many :contacts, through: :categorizations category.categorizations.delete_all -
destroy_all vs. delete_all with has_many dependencies category.categorizations.delete_all user system total
real => 130.080000 0.120000 130.200000 (130.308334) -
destroy_all vs. delete_all with has_many dependencies category.categorizations.delete_all user system total
real => 130.080000 0.120000 130.200000 (130.308334) DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 -
destroy_all vs. delete_all with has_many dependencies category.categorizations.delete_all user system total
real => 130.080000 0.120000 130.200000 (130.308334) DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 AND `categorizations`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46...10000) -
Let’s fix this. category = Category.where(name: “Networking”).first Categorization.where( category_id: category.id).delete_all
user system total real => 0.010000 0.000000 0.010000 ( 0.014286) -
Let’s fix this. category = Category.where(name: “Networking”).first Categorization.where( category_id: category.id).delete_all
user system total real => 0.010000 0.000000 0.010000 ( 0.014286) DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences ‣ We can all be friends with Active Record -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences ‣ We can all be friends with Active Record ‣ Check out these helpful tools -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences ‣ We can all be friends with Active Record ‣ Check out these helpful tools New Relic -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences ‣ We can all be friends with Active Record ‣ Check out these helpful tools New Relic MiniProfiler Gem -
In Conclusion ‣ Active Record is an awesome tool ‣
Active Record queries can have major consequences ‣ We can all be friends with Active Record ‣ Check out these helpful tools New Relic MiniProfiler Gem Bullet Gem