Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Who's afraid of database views?

Who's afraid of database views?

A talk about using database views within Rails at the London Ruby User Group in January 2015.

Robbie Clutton

January 12, 2015
Tweet

More Decks by Robbie Clutton

Other Decks in Technology

Transcript

  1. WHO’S AFRAID OF
    DATABASE VIEWS?
    Robbie Clutton

    View full-size slide

  2. @robb1e
    Who am I and why am
    I so interested in this?

    View full-size slide

  3. @robb1e
    What are your go-to
    tools for solving
    performance issues?

    View full-size slide

  4. @robb1e
    caching?
    de-normalised data?
    document database?
    client side rendering?

    View full-size slide

  5. @robb1e
    Let’s look at an
    example of a
    manufacturing process

    View full-size slide

  6. @robb1e
    Product
    Component
    Part
    A p ro d u c t i s m a d e u p o f m a n y
    components which in turn is made up of
    many parts.
    Each part has a cost associated to is, and
    each component and each product has a
    manufacturing cost.
    How much does it take to produce a given
    product?

    View full-size slide

  7. @robb1e
    10,000 parts
    1,000 components
    10 products
    !
    each component has 100 parts
    each product has 100 components

    View full-size slide

  8. @robb1e
    Product.each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end
    Product.each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end
    Product.each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end

    View full-size slide

  9. @robb1e
    O(n )
    3

    View full-size slide

  10. @robb1e
    http://rob-bell.net/2009/06/a-beginners-guide-to-big-o-notation/
    Big O notation is used in Computer Science to describe the
    performance or complexity of an algorithm. Big O specifically
    describes the worst-case scenario, and can be used to describe
    the execution time required or the space used (e.g. in memory or
    on disk) by an algorithm.

    View full-size slide

  11. @robb1e
    Product.each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end

    View full-size slide

  12. @robb1e
    O(n )
    3

    View full-size slide

  13. @robb1e
    15.8 seconds

    View full-size slide

  14. @robb1e
    Product.
    includes(components: [:parts]).
    each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end

    View full-size slide

  15. @robb1e
    8x faster
    2 seconds

    View full-size slide

  16. @robb1e
    O(n )
    3

    View full-size slide

  17. @robb1e
    Product.
    includes(components: [:parts]).
    each do |product|
    // add product costs
    product.components.each do |component|
    // add component costs
    component.parts.each do |part|
    // add part costs
    end
    end
    // print product price
    end

    View full-size slide

  18. @robb1e
    class AddProductCosts < ActiveRecord::Migration
    !
    def up
    execute <<-SQL
    CREATE OR REPLACE VIEW product_costs AS

    SQL
    end
    !
    def down
    execute “DROP VIEW product_costs”
    end
    !
    end

    View full-size slide

  19. @robb1e
    CREATE OR REPLACE VIEW product_costs AS
    SELECT products.id AS product_id,
    products.name AS product_name,
    products.manufacture_cost + sum(component_costs.cost)) AS
    product_manufacture_cost
    FROM products
    INNER JOIN product_components ON product_components.product_id =
    products.id
    INNER JOIN (
    SELECT components.id AS component_id, (components.manufacture_cost +
    sum(cost)) AS cost FROM components
    INNER JOIN component_parts ON components.id =
    component_parts.component_id
    INNER JOIN parts ON parts.id = component_parts.part_id
    GROUP BY components.id
    ) AS component_costs ON component_costs.component_id =
    product_components.component_id
    GROUP BY products.id, products.name
    ORDER BY products.id

    View full-size slide

  20. @robb1e
    SQL can be scary

    View full-size slide

  21. @robb1e
    SELECT
    components.id AS component_id,
    (components.manufacture_cost + sum(cost)) AS cost
    FROM components
    INNER JOIN component_parts ON components.id = component_parts.component_id
    INNER JOIN parts ON parts.id = component_parts.part_id
    GROUP BY components.id

    View full-size slide

  22. @robb1e
    Component
    Part
    Component Part
    component_id
    cost
    (component cost +
    sum(parts cost)
    123 1234
    456 2345

    View full-size slide

  23. @robb1e
    SELECT products.id AS product_id,
    products.name AS product_name,
    products.manufacture_cost + sum(component_costs.cost)) AS
    product_manufacture_cost
    FROM products
    INNER JOIN product_components ON product_components.product_id =
    products.id
    INNER JOIN (

    ) AS component_costs ON component_costs.component_id =
    product_components.component_id
    GROUP BY products.id, products.name
    ORDER BY products.id

    View full-size slide

  24. @robb1e
    product_id product_name cost
    1 prod_1 12345
    2 prod_2 23456
    Product
    Component
    Product
    Component
    Cost

    View full-size slide

  25. @robb1e
    class ProductCost < ActiveRecord::Base
    end

    View full-size slide

  26. @robb1e
    ProductCost.each do |product_cost|
    // print product cost
    end

    View full-size slide

  27. @robb1e
    5x faster than includes
    42x faster than memory
    0.38 seconds

    View full-size slide

  28. @robb1e
    But now I have business
    logic in two places

    View full-size slide

  29. @robb1e
    maybe…
    but…

    View full-size slide

  30. @robb1e
    before do
    // create Parts
    // create Components
    // create ComponentParts
    // create Products
    // create ProductComponents
    end

    View full-size slide

  31. @robb1e
    it 'calculates the cost' do
    product_cost = ProductCost.where(product_name: 'product_1').first
    !
    expected_cost = 1000 + 100 + 200 + 10 + 20
    expect(product_cost.product_manufacture_cost).to eq(expected_cost)
    end

    View full-size slide

  32. @robb1e
    Sacrifice local optimisations
    for system wide improvements
    …and you can keep TDDing

    View full-size slide

  33. WHO’S AFRAID OF
    DATABASE VIEWS?
    (still)
    ^
    github.com/robb1e/rails_db_views_demo
    !
    @robb1e

    View full-size slide