active record presentation feb 12

8/14/2019 Active Record Presentation Feb 12

1/26


2/26

Discussion tonight

Intended for new Rails Developers

People that think Rails is slow Focus on simple steps to improvecommon :has_many performance problems

Short - 15mins All links/references up on

http://work.rowanhick.com tomorrow

2
http://work.rowanhick.com/http://work.rowanhick.com/http://work.rowanhick.com/http://work.rowanhick.com/


3/26

About me

New Zealander (not Australian)

Product Development Mgr for a startup in Toronto

Full time with Rails for 2 years

Previously PHP/MySQL for 4 years

6 years Prior QA/BA/PM for Enterprise CAD/CAM software dev company

3


4/26


5/26

ActiveRecord lets you get in

trouble far to quick.

Super easy syntax comes at a cost.@orders = Order.find(:all)

@orders.each do |order|

puts order.customer.name

puts order.customer.country.name

end

Congratulations, you just overloaded your DBwith (total number of Orders x 2) unnecessarySQL calls

5


6/26

What happened there?

One query to get the orders@orders = Order.find(:all)

SELECT * FROM orders

For every item in the orders collectioncustomer.name:

SELECT * FROM customers WHERE id = x

customer.country.name:

SELECT * FROM customers WHERE id = y

6


7/26

Systemic Problem in

Web develo mentIve seen:

-15 Second page reloads

- 10000 queries per page language performs

really poorly, were going to get itredeveloped in

7


8/26

Atypical root cause

Failure to build application with *real* data

ie It worked fine on my machine but the

developer never loaded up 100000 recordsto see what would happen

Using Rake tasks to build realistic data sets

Test, test, test tail -f log/development.log

8


9/26

Faker to the rescue

in lib/xchain.rakenamespace :xchain do

desc "Load fake customers"task :load_customers => :environment do

require 'Faker'Customer.find(:all, :conditions => "email LIKE('%XCHAIN_%')").each { |c| c.destroy }

1..300.times doc = Customer.newc.status_id = rand(3) + 1c.country_id = rand(243) + 1c.name = Faker::Company.namec.alternate_name = Faker::Company.name

c.phone = Faker::PhoneNumber.phone_numberc.email = "XCHAIN_"+Faker::Internet.emailc.save

endend

$ rake xchain:load_customers

9


10/26

Eager loading

By using :include in .finds you create sql joins

Pull all required records in one query

find(:all, :include => [ :customer, :order_lines ])

order.customer, order.order_lines

find(:all, :include => [ { :customer

=> :country }, :order_lines ])

order.customer order.customer.country

order.order_lines

10


11/26

Improvement

Lets start optimising ...@orders = Order.find(:all, :include => {:customers => :country} )

Resulting SQL ...SELECT orders.*, countries.* FROM orders LEFT JOINcustomers ON ( customers.id = orders.customers_id )LEFT JOIN countries ON ( countries.id =

customers.country_id)

7.70 req/s 1.4x faster

11


12/26

Select only what you

need Using the :select parameter in the find

options, you can limit the columns you arerequesting back from the database

No point grabbing all columns, if you onlywant :id and :name

Orders.find(:all, :select => orders.id,

orders.name)

12


13/26

The last slide was very

im ortant Not using selects is *okay* provided you

have very small columns, and never any

binary, or large text data

You can suddenly saturate your DBconnection.

Imagine our Orders table had an Invoicecolumn on it storing a pdf of the invoice...

13


14/26

Oops

Cant show a benchmark

:select and :include dont work together !,reverts back to selecting all columns

Core team for a long time have notincluded patches to make it work

One little sentence in ActiveRecord rdocBecause eager loading generates the SELECTstatement too, the :select option is ignored.

14


15/26

mrj to the rescue

http://dev.rubyonrails.org/attachment/ticket/7147/init.5.rb Monkey patch to fix select/include problem

Produces much more efficient SQL

15
http://dev.rubyonrails.org/attachment/ticket/7147/init.5.rbhttp://dev.rubyonrails.org/attachment/ticket/7147/init.5.rbhttp://dev.rubyonrails.org/attachment/ticket/7147/init.5.rbhttp://dev.rubyonrails.org/attachment/ticket/7147/init.5.rb


16/26

Updated finder

Now :select and :include playing nice:@orders = Order.find(:all,:select => 'orders.id, orders.created_at, customers.name,

countries.name, order_statuses.name',

:include => [{:customer[:name]

=> :country[:name]}, :order_status[:name]],

:conditions => conditions,

:order => 'order_statuses.sort_order ASC,order_statuses.id ASC,

orders.id DESC')

15.15 req/s 2.88x faster

16


17/26

r8672 change

http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/

The following uses new improved association load(12 req/s)@orders = Order.find(:all, :include => [{:customer

=> :country}, :order_status] )

The following does not@orders = Order.find(:all, :include => [{:customer

=> :country}, :order_status], :order =>

order_statuses.sort_order)

17
http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/http://blog.codefront.net/2008/01/30/living-on-the-edge-of-rails-5-better-eager-loading-and-more/


18/26

r8672 output...

Heres the SQL

Order Load (0.000837) SELECT * FROM `orders` WHERE (order_status_id

active record presentation feb 12

Documents