WHO’S AFRAID OF DATABASE VIEWS? Robbie Clutton

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

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

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

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

@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?

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

@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

@robb1e O(n ) 3

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

@robb1e

@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

@robb1e O(n ) 3

@robb1e 15.8 seconds

@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

@robb1e 8x faster 2 seconds

@robb1e O(n ) 3

@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

@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

@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

@robb1e SQL can be scary

@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

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

@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

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

@robb1e class ProductCost < ActiveRecord::Base end

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

@robb1e O(n)

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

@robb1e But now I have business logic in two places

@robb1e maybe… but…

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

@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

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

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