Improving ActiveRecord Performance

Using eager loading, some tips, and bullet

Transcript

1. Optimizing ActiveRecord Usage - tips, tricks, and useful gems By Thu Trang Pham, 2015.
2. Shameless Self Plug Me: - interned at Google - 3+ years experience in Rails - started programming at age 12 https://github.com/TrangPham ca.linkedin.com/in/thupham/en
3. ActiveRecord is a SQL ORM ORM means Object Relational Model It maps: table -> model row -> instance
4. Simple Example referenced: http://guides.rubyonrails.org/association_basics.html
5. Don’t query for knowns > Order.first.customer.id Order Load (0.8ms) SELECT "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT 1 Customer Load (0.2ms) SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT 1 [["id", 1234567]] => 1234567 VS > Order.first.customer_id Order Load (0.8ms) SELECT "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT 1 => 1234567 Minimize number of queries!
6. Indexing Indexes allow the database application to find data fast; without reading the whole table. add_index(:table, [:column1, :column2], unique: false) Example: add_index(:order, :customer_id) Order.where(customer_id: 1234567) -> faster! Makes querying faster, but slows down create.
7. JOIN customers = Customer.joins(:orders) # => SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" returns set of customers whom have order(s) N+1 problem! customers.each {|c| puts c.orders}
8. Being greedy preload, eager_load and includes Be greedy when joined data will be iterated on Beware! preload, eager_load and includes do not play nice with default scope, single table inheritance, and/or polymorphic associations
9. eager_load eager loading loads all association in a single query using LEFT OUTER JOIN. Customer.eager_load(:orders) # => SELECT "customers"."id" AS t0_r0, "customers"."name" AS t0_r1, "orders"."id" AS t1_r0, "orders"."order_date" AS t1_r1, "orders"."customer_id" AS t1_r2 FROM "customers" LEFT OUTER JOIN "orders" ON "orders"."customer_id" = "orders"."id" You now have access to the orders without N+1 queries!
10. preload Preload loads the association data in a separate query. Customer.preload(:orders).to_a # => SELECT "customers".* FROM "customers" SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN (1) Good for complex queries where relation is 1:N or N:N.
11. includes Default behavior: two separate queries (acts like preload) But it knows when to switch to a single query (acts like eager_load) If you want to force includes to use a single query you can use references. Customer.includes(:orders).references(:orders) # => SELECT "customers"."id" AS t0_r0, "customers"."name" AS t0_r1, "orders"."id" AS t1_r0, "orders"."order_date" AS t1_r1, "orders"."customers_id" AS t1_r2 FROM "customers" LEFT OUTER JOIN "orders" ON "orders"."customer_id" = "orders"."id"
12. Using Bullet Noticing when to be greedy can be hard! Bullet is only in development mode and will monitor your queries for when to be greedy and when you’re being too greedy! Gem source code: https://github.com/flyerhzm/bullet Railscast: http://railscasts.com/episodes/372-bullet
13. Questions ? More resources on eager loading: http://blog.arkency.com/2013/12/rails4-preloading/ http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html