CRUD! Dear Active Record, It's Not You, It's Me

Transcript

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

   View Slide

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

   View Slide

3. You - Should - Tell
   Uchitelle
   

   View Slide

4. You - Should - Tell
   Uchitelle
   X
   

   View Slide

5. Lead Developer at PhishMe
   

   View Slide

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

   View Slide

7. At PhishMe we have A LOT of data
   

   View Slide

8. Active Record is a magical unicorn
   

   View Slide

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

   View Slide

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

    View Slide

11. Learn from my mistakes...
    And avoid #firstworldproblems
    

    View Slide

12. CREATE
    READ
    UPDATE
    DELETE
    

    View Slide

13. CREATE
    create!, new, INSERT
    

    View Slide

14. READ
    where, find_each, pluck, SELECT
    

    View Slide

15. UPDATE
    update_attributes, edit, update_all, UPDATE
    

    View Slide

16. DELETE
    destroy, delete, destroy_all, delete_all, DELETE
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

23. CREATE
    

    View Slide

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

    View Slide

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

    View Slide

26. MySQL Batch Insert, FTW.
    

    View Slide

27. Don’t be afraid to write raw SQL
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

38. READ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

52. UPDATE
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

60. DELETE
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

74. CollectionProxy
    

    View Slide

75. CollectionProxy
    Object that holds
    the association
    category
    

    View Slide

76. CollectionProxy
    Object that holds
    the association
    category
    Associated object
    categorizations
    

    View Slide

77. Rails 4.1 and below
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

90. Rails 4.2.0+
    

    View Slide

91. I made assumptions about Active Record
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

100. Active Record
     source code
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

105. And then a bunch of PR’s Happened
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

113. Active Record is a complex library
     

     View Slide

114. Check out these helpful tools
     

     View Slide

115. Check out these helpful tools
     ‣ New Relic
     rewrelic.com
     

     View Slide

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

     View Slide

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

     View Slide

118. In Conclusion
     

     View Slide

119. Active Record is an awesome tool
     

     View Slide

120. Active Record queries have consequences
     

     View Slide

121. We can all be friends with Active Record
     

     View Slide

122. Sometimes it really is a bug
     

     View Slide

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

     View Slide

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

     View Slide