Tweet
Tweet

Slide 1

Slide 1 text

CRUD! What to do When Active Record, MySQL, and Your Data Betray You

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

Senior 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 ActiveRecord Benchmark Data: user system total real => 44.740000 1.180000 45.920000 ( 51.095556)

Slide 36

Slide 36 text

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)

Slide 37

Slide 37 text

READ

Slide 38

Slide 38 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 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 SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`user_id` = 1 AND `contacts`.`country` = 'USA' => Lauretta Shana Jason Jermain Blaze Jessy Bradly ...

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”).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 41

Slide 41 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 42

Slide 42 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 43

Slide 43 text

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

Slide 44

Slide 44 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 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) .pluck Benchmark Data: user system total real => 0.080000 0.020000 0.100000 ( 0.126814)

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

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

Slide 48

Slide 48 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 49

Slide 49 text

UPDATE

Slide 50

Slide 50 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 51

Slide 51 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 52

Slide 52 text

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

Slide 53

Slide 53 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 54

Slide 54 text

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

Slide 55

Slide 55 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 56

Slide 56 text

DELETE

Slide 57

Slide 57 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 58

Slide 58 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 59

Slide 59 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 60

Slide 60 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 61

Slide 61 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 62

Slide 62 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 63

Slide 63 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 64

Slide 64 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 65

Slide 65 text

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

Slide 66

Slide 66 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 67

Slide 67 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 68

Slide 68 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 69

Slide 69 text

CollectionProxy

Slide 70

Slide 70 text

CollectionProxy Object that holds the association category

Slide 71

Slide 71 text

CollectionProxy Object that holds the association category Associated object categorizations

Slide 72

Slide 72 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 73

Slide 73 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

Slide 74

Slide 74 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 75

Slide 75 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 76

Slide 76 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 77

Slide 77 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 78

Slide 78 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 79

Slide 79 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 80

Slide 80 text

Let’s fix this. category = Category.where(name: “Networking”).first Categorization.where( category_id: category.id).delete_all

Slide 81

Slide 81 text

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)

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

In Conclusion

Slide 84

Slide 84 text

In Conclusion ‣ Active Record is an awesome tool

Slide 85

Slide 85 text

In Conclusion ‣ Active Record is an awesome tool ‣ Active Record queries can have major consequences

Slide 86

Slide 86 text

In Conclusion ‣ Active Record is an awesome tool ‣ Active Record queries can have major consequences ‣ We can all be friends with Active Record

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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