Tweet
Tweet

Slide 1

Slide 1 text

CRUD! Dear Active Record, It’s Not You, It’s me

Slide 2

Slide 2 text

eileencodes.com @eileencodes github.com/eileencodes Eileen M. Uchitelle

Slide 3

Slide 3 text

You - Should - Tell Uchitelle

Slide 4

Slide 4 text

You - Should - Tell Uchitelle X

Slide 5

Slide 5 text

Lead Developer at PhishMe

Slide 6

Slide 6 text

* always check where a link is going before clicking on it

Slide 7

Slide 7 text

At PhishMe we have A LOT of data

Slide 8

Slide 8 text

Active Record is a magical unicorn

Slide 9

Slide 9 text

When MySQL crashed or my queries were slow I felt betrayed by Active Record.

Slide 10

Slide 10 text

But I knew Active Record and I could work it out So I began studying my queries more closely...

Slide 11

Slide 11 text

Learn from my mistakes... And avoid #firstworldproblems

Slide 12

Slide 12 text

CREATE READ UPDATE DELETE

Slide 13

Slide 13 text

CREATE create!, new, INSERT

Slide 14

Slide 14 text

READ where, find_each, pluck, SELECT

Slide 15

Slide 15 text

UPDATE update_attributes, edit, update_all, UPDATE

Slide 16

Slide 16 text

DELETE destroy, delete, destroy_all, delete_all, DELETE

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

CREATE

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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',...);

Slide 26

Slide 26 text

MySQL Batch Insert, FTW.

Slide 27

Slide 27 text

Don’t be afraid to write raw SQL

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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',...) ...

Slide 35

Slide 35 text

That’s a huge difference Active Record Benchmark Data: user system total real => 44.740000 1.180000 45.920000 ( 51.095556)

Slide 36

Slide 36 text

That’s a huge difference Active Record 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)

Slide 37

Slide 37 text

That’s a huge difference Active Record 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)

Slide 38

Slide 38 text

READ

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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 ...

Slide 41

Slide 41 text

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 => ...

Slide 42

Slide 42 text

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",...]

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

Which one was fastest? .each Benchmark Data: user system total real => 0.950000 0.060000 1.010000 ( 1.050266)

Slide 45

Slide 45 text

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)

Slide 46

Slide 46 text

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)

Slide 47

Slide 47 text

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)

Slide 48

Slide 48 text

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)

Slide 49

Slide 49 text

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)

Slide 50

Slide 50 text

Statistics for Read 0 3.8 7.5 11.3 15.0 0 10 1000 10000 100000 .each .find_each .pluck

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

UPDATE

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

Let’s update the category in each categorization category = Category.where(name: “Networking”).first Categorization.update_all( category_id: category.id )

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

Check out the difference update_attributes Benchmark Data: user system total real => 12.990000 0.870000 13.860000 ( 17.156265)

Slide 58

Slide 58 text

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)

Slide 59

Slide 59 text

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)

Slide 60

Slide 60 text

DELETE

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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`

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first category.contacts.destroy_all

Slide 70

Slide 70 text

destroy_all vs. delete_all with has_many dependencies category = Category.where(name: “Networking”).first category.contacts.destroy_all

Slide 71

Slide 71 text

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?

Slide 72

Slide 72 text

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?

Slide 73

Slide 73 text

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?

Slide 74

Slide 74 text

CollectionProxy

Slide 75

Slide 75 text

CollectionProxy Object that holds the association category

Slide 76

Slide 76 text

CollectionProxy Object that holds the association category Associated object categorizations

Slide 77

Slide 77 text

Rails 4.1 and below

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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 AND `categorizations`.`id` IN (1, 2, 3, 4, 5, 6...10000)

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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)

Slide 84

Slide 84 text

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)

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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)

Slide 87

Slide 87 text

Solutions to this problem category = Category.where(name: “Networking”).first Categorization.where( category_id: category.id).delete_all

Slide 88

Slide 88 text

Solutions to this problem 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)

Slide 89

Slide 89 text

Solutions to this problem 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

Slide 90

Slide 90 text

Rails 4.2.0+

Slide 91

Slide 91 text

I made assumptions about Active Record

Slide 92

Slide 92 text

So...why don’t they just fix Active Record?! “They” is tenderlove

Slide 93

Slide 93 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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

Slide 94

Slide 94 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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`.`category_id` = 1 Thanks to @neerajdotname for this pull request f f

Slide 95

Slide 95 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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 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,...10000) f f

Slide 96

Slide 96 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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 DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 f f

Slide 97

Slide 97 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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 AND `categorizations`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,...10000)

Slide 98

Slide 98 text

delete_all behavior with has_many dependencies in Rails 4.2+ 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

Slide 99

Slide 99 text

Comparison of delete_all from 4.0 to 4.2 Version SQL Callbacks 4.1 & below no dependency UPDATE `categorizations` SET `categorizations`.`category_id` = NULL WHERE `categorizations`.`category_id` = 1 AND `categorizations`.`id` IN (1, 2, 3, 4, 5... NO destroy DELETE FROM `categorizations` WHERE `categorizations`.`id` = 1 YES delete_all DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 AND `categorizations`.`id` IN (1, 2, 3, 4, 5... NO 4.2+ no dependency UPDATE `categorizations` SET `categorizations`.`category_id` = NULL WHERE `categorizations`.`category_id` = 1 NO destroy DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 NO delete_all DELETE FROM `categorizations` WHERE `categorizations`.`category_id` = 1 NO

Slide 100

Slide 100 text

Active Record source code

Slide 101

Slide 101 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 def delete_all(dependent = nil) [...] dependent = [...] delete(:all, dependent: dependent).tap do reset loaded! end end

Slide 102

Slide 102 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 def delete_all(dependent = nil) [...] dependent = [...] delete(:all, dependent: dependent).tap do reset loaded! end end f

Slide 103

Slide 103 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 def delete(*records) dependent = options[:dependent] if records.first == :all if loaded? || dependent == :destroy delete_or_destroy(load_target, dependent) else delete_records(:all, dependent) end else... end end

Slide 104

Slide 104 text

f File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 def delete(*records) dependent = options[:dependent] if records.first == :all if loaded? || dependent == :destroy delete_or_destroy(load_target, dependent) else delete_records(:all, dependent) end else... end end

Slide 105

Slide 105 text

And then a bunch of PR’s Happened

Slide 106

Slide 106 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 delete_all delete delete_or_destroy remove_records delete_records etc...

Slide 107

Slide 107 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.0 - 4.1 delete_all delete delete_or_destroy remove_records delete_records etc...

Slide 108

Slide 108 text

f File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.2+ def delete_all(dependent = nil) [...] dependent = [...] - delete(:all, dependent: dependent).tap do + delete_records(:all, dependent: dependent).tap do reset loaded! end end

Slide 109

Slide 109 text

File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.2+ def delete(*records) dependent = options[:dependent] if records.first == :all if loaded? || dependent == :destroy delete_or_destroy(load_target, dependent) else delete_records(:all, dependent) end else... end end - - - - - - - -

Slide 110

Slide 110 text

File: activerecord/lib/active_record/ associations/has_many_association.rb # => Rails 4.2+ def delete_or_nullify_all_records(method) count = delete_count(method, self.scope) update_counter(-count) end def delete_records(records, method) if method == :destroy records.each(&:destroy!) update_counter(-records.length) unless ... else scope = self.scope.where(reflection.klass.primary_key ... update_counter(-delete_count(method, scope)) end end

Slide 111

Slide 111 text

delete delete_or_destroy remove_records delete_records etc... File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.2+ delete_all delete_or_nullify_all_records etc..

Slide 112

Slide 112 text

f File: activerecord/lib/active_record/ associations/collection_association.rb # => Rails 4.2+ def delete_all(dependent = nil) [...] dependent = [...] - delete_records(:all, dependent: dependent).tap do + delete_or_nullify_all_records(dependent).tap do reset loaded! end end

Slide 113

Slide 113 text

Active Record is a complex library

Slide 114

Slide 114 text

Check out these helpful tools

Slide 115

Slide 115 text

Check out these helpful tools ‣ New Relic rewrelic.com

Slide 116

Slide 116 text

Check out these helpful tools ‣ New Relic rewrelic.com ‣ MiniProfiler github.com/MiniProfiler/rack-mini-profiler

Slide 117

Slide 117 text

Check out these helpful tools ‣ New Relic rewrelic.com ‣ MiniProfiler github.com/MiniProfiler/rack-mini-profiler ‣ Bullet Gem github.com/flyerhzm/bullet

Slide 118

Slide 118 text

In Conclusion

Slide 119

Slide 119 text

Active Record is an awesome tool

Slide 120

Slide 120 text

Active Record queries have consequences

Slide 121

Slide 121 text

We can all be friends with Active Record

Slide 122

Slide 122 text

Sometimes it really is a bug

Slide 123

Slide 123 text

much thanks so database many Active Record very rubynation such data wow

Slide 124

Slide 124 text

eileencodes.com @eileencodes speakerdeck.com/eileencodes github.com/eileencodes/crud_project Eileen M. Uchitelle We’re Hiring!