Who's afraid of database views?

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

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