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.

1668868370ee5829339e06031ad0b145?s=128

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

  2. @robb1e Who am I and why am I so interested

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

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

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

  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?
  7. @robb1e 10,000 parts 1,000 components 10 products ! each component

    has 100 parts each product has 100 components
  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
  9. @robb1e O(n ) 3

  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.
  11. @robb1e

  12. @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
  13. @robb1e O(n ) 3

  14. @robb1e 15.8 seconds

  15. @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
  16. @robb1e 8x faster 2 seconds

  17. @robb1e O(n ) 3

  18. @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
  19. @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
  20. @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
  21. @robb1e SQL can be scary

  22. @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
  23. @robb1e Component Part Component Part component_id cost (component cost +

    sum(parts cost) 123 1234 456 2345
  24. @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
  25. @robb1e product_id product_name cost 1 prod_1 12345 2 prod_2 23456

    Product Component Product Component Cost
  26. @robb1e class ProductCost < ActiveRecord::Base end

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

  28. @robb1e O(n)

  29. @robb1e 5x faster than includes 42x faster than memory 0.38

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

  31. @robb1e maybe… but…

  32. @robb1e before do // create Parts // create Components //

    create ComponentParts // create Products // create ProductComponents end
  33. @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
  34. @robb1e Sacrifice local optimisations for system wide improvements …and you

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