search

drhenner / ror_ecommerce

Ruby on Rails Ecommerce platform, perfect for your small business solution.
www.ror-e.com
MIT License
1.21k stars 409 forks source link

N + 1 queries Shopping::CartItemsController.index #180

Open lelelelemon opened 7 years ago

lelelelemon commented 7 years ago

In Shopping::CartItemsController.index, variant, variant_properties, inventory should be eager loaded with shopping_cart_items, or else there will be n + 1 queries like:

Shopping::CartItemsController.index total_duration=496.536672 db_time=107.07184499999995 view_runtime=381.6350809991499 query_len=59 query_time=114.53561100000002 query_in_view=0.11400755199999997 render_partial_time=296.397592 render_template_time=285.767357 query 0.303371 ms, SQL: SELECT carts. FROM carts WHERE carts.id = 108 LIMIT 1 query 0.224688 ms, SQL: SELECT cart_items. FROM cart_items WHERE cart_items.active = 1 AND cart_items.item_type_id = 1 AND cart_items.cart_id = 108 query 0.570142 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 4 LIMIT 1 query 0.45632700000000004 ms, SQL: SELECT products. FROM products WHERE products.id = 2 LIMIT 1 query 0.460517 ms, SQL: SELECT 1 AS one FROM images WHERE images.imageable_id = 2 AND images.imageable_type = 'Product' LIMIT 1 query 93.37295900000001 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 4 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.46100800000000003 ms, SQL: SELECT brands. FROM brands WHERE brands.id = 1 LIMIT 1 query 0.22509400000000002 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 4 LIMIT 1 query 0.536578 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 1 LIMIT 1 query 0.33050199999999996 ms, SQL: SELECT products. FROM products WHERE products.id = 1 LIMIT 1 query 0.39194 ms, SQL: SELECT 1 AS one FROM images WHERE images.imageable_id = 1 AND images.imageable_type = 'Product' LIMIT 1 query 0.238672 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 1 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.241573 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 1 LIMIT 1 query 0.41736399999999996 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 8 LIMIT 1 query 0.305438 ms, SQL: SELECT products. FROM products WHERE products.id = 10 LIMIT 1 query 0.436793 ms, SQL: SELECT 1 AS one FROM images WHERE images.imageable_id = 10 AND images.imageable_type = 'Product' LIMIT 1 query 0.24696700000000002 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 8 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.32817399999999997 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 8 ORDER BY variant_properties.id ASC LIMIT 1 query 0.663492 ms, SQL: SELECT brands. FROM brands WHERE brands.id = 2 LIMIT 1 query 0.21121900000000002 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 9 LIMIT 1 query 0.402007 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 13 LIMIT 1 query 0.82424 ms, SQL: SELECT products. FROM products WHERE products.id = 14 LIMIT 1 query 0.366366 ms, SQL: SELECT 1 AS one FROM images WHERE images.imageable_id = 14 AND images.imageable_type = 'Product' LIMIT 1 query 0.27228 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 13 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.300834 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 13 ORDER BY variant_properties.id ASC LIMIT 1 query 0.289 ms, SQL: SELECT brands. FROM brands WHERE brands.id = 618 LIMIT 1 query 0.21562299999999998 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 14 LIMIT 1 query 0.31499 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 5 LIMIT 1 query 0.22973500000000002 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 5 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.207653 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 5 LIMIT 1 query 0.354605 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 106 LIMIT 1 query 0.321844 ms, SQL: SELECT products. FROM products WHERE products.id = 107 LIMIT 1 query 0.851368 ms, SQL: SELECT 1 AS one FROM images WHERE images.imageable_id = 107 AND images.imageable_type = 'Product' LIMIT 1 query 0.733815 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 106 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.324875 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 106 ORDER BY variant_properties.id ASC LIMIT 1 query 0.366147 ms, SQL: SELECT brands. FROM brands WHERE brands.id = 333 LIMIT 1 query 0.215831 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 107 LIMIT 1 query 0.41136 ms, SQL: SELECT 1 AS one FROM cart_items WHERE cart_items.cart_id = 108 AND cart_items.active = 1 AND cart_items.item_type_id = 2 LIMIT 1 query 0.211869 ms, SQL: SELECT 1 AS one FROM cart_items WHERE cart_items.cart_id = 108 AND cart_items.active = 1 AND cart_items.item_type_id = 2 LIMIT 1 query 0.415937 ms, SQL: SELECT cart_items. FROM cart_items WHERE cart_items.cart_id = 108 AND cart_items.active = 1 AND cart_items.item_type_id = 2 query 0.313407 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 7 LIMIT 1 query 0.2397 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 8 LIMIT 1 query 1.018529 ms, SQL: SELECT products. FROM products WHERE products.id = 9 LIMIT 1 query 0.365777 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 7 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.303579 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 7 ORDER BY variant_properties.id ASC LIMIT 1 query 0.34452 ms, SQL: SELECT variants. FROM variants WHERE variants.id = 9 LIMIT 1 query 0.294159 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id = 10 LIMIT 1 query 0.565085 ms, SQL: SELECT products. FROM products WHERE products.id = 3 LIMIT 1 query 0.396156 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 9 AND variant_properties.primary = 1 ORDER BY variant_properties.id ASC LIMIT 1 query 0.314029 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id = 9 ORDER BY variant_properties.id ASC LIMIT 1 query 0.30986 ms, SQL: SELECT product_types. FROM product_types WHERE product_types.parent_id IS NULL ORDER BY product_types.lft query 0.24868600000000002 ms, SQL: SELECT cart_items. FROM cart_items WHERE cart_items.cart_id = 108 AND cart_items.active = 1 AND cart_items.item_type_id = 1 LIMIT 20 query 0.270459 ms, SQL: SELECT variants. FROM variants WHERE variants.id IN (4, 1, 8, 13, 5, 106) query 0.271287 ms, SQL: SELECT variant_properties. FROM variant_properties WHERE variant_properties.variant_id IN (4, 1, 8, 13, 5, 106) query 0.215117 ms, SQL: SELECT inventories. FROM inventories WHERE inventories.id IN (4, 1, 9, 14, 5, 107) query 0.204109 ms, SQL: SELECT products. FROM products WHERE products.id IN (2, 1, 10, 14, 107) query 0.208388 ms, SQL: SELECT brands. FROM brands WHERE brands.id IN (1, 2, 618, 333)

I change it to this: @cart_items = session_cart.shopping_cart_items.includes(:variant, variant: [:variant_properties, :inventory, :product, product: [:brand, :images]]) @saved_cart_items = session_cart.saved_cart_items.includes(:variant, variant: [:variant_properties, :inventory, :product, product: [:brand, :images]])

drhenner commented 7 years ago

Thank you

lelelelemon commented 7 years ago

So, they are indeed N + 1 queries, right?