Home Rails select query slowness
Reply: 0

Rails select query slowness

Rory_MacDonald
1#
Rory_MacDonald Published in 2012-02-06 15:47:37Z

I'm having a bit of a rails problem, that I can't figure out.

Locally i'm running rails 3.1 and ruby 1.9.3 using Mongrel, on production i'm running Passanger / Apache2. Whilst running locally, in development enviroment my DB queries are taking about 40 - 50 seconds, which is way to much. We've got about 15,000 records in the products table, 5,000 in playroom and 1,700 in collection_items and it seems as though rails is doing a select against these whole tables, which is causing the slowdown.

Here's an overview of my model structure:

Collection
-- has_many CollectionItems

CollectionItem
-- belongs_to Collection
-- has_many Products
-- has_many Playrooms

Product
-- has_many LineItems
-- belongs_to ProductType
-- belongs_to CollectionItem

Cart
-- has_many LineItems

LineItem
-- belongs_to Product
-- belongs_to Order
-- belongs_to Cart

Here's my log:

Processing by CartController#index as HTML
Cart Load (0.7ms)  SELECT `carts`.* FROM `carts` WHERE `carts`.`id` = 7 LIMIT 1
CollectionGroup Load (1.6ms)  SELECT `collection_groups`.* FROM `collection_groups` WHERE (status = 'live')
Rendered cart/_checkout_steps.html.erb (1.2ms)
(0.4ms)  SELECT COUNT(*) FROM `line_items` WHERE `line_items`.`cart_id` = 7
LineItem Load (2.0ms)  SELECT `line_items`.* FROM `line_items` WHERE `line_items`.`cart_id` = 7
Product Load (33592.3ms)  SELECT `products`.* FROM `products` 
Product Load (1.8ms)  SELECT `products`.* FROM `products` WHERE `products`.`id` = 1 LIMIT 1
ProductType Load (181.5ms)  SELECT `product_types`.* FROM `product_types` 
[paperclip] Duplicate URL for image with /system/:attachment/:id/:style/:filename. This will clash with attachment defined in CollectionItem class
ProductType Load (1.5ms)  SELECT `product_types`.* FROM `product_types` WHERE `product_types`.`id` = 1 LIMIT 1
CollectionItem Load (**4701.0ms**)  SELECT `collection_items`.* FROM `collection_items` 
[paperclip] Duplicate URL for image with /system/:attachment/:id/:style/:filename. This will clash with attachment defined in ProductType class
Collection Load (157.8ms)  SELECT `collections`.* FROM `collections` 
Playroom Load (18056.7ms)  SELECT `playrooms`.* FROM `playrooms` 
CollectionItem Load (1.0ms)  SELECT `collection_items`.* FROM `collection_items` WHERE `collection_items`.`id` = 1 LIMIT 1
Product Load (1.0ms)  SELECT `products`.* FROM `products` WHERE `products`.`id` = 527 LIMIT 1
ProductType Load (1.0ms)  SELECT `product_types`.* FROM `product_types` WHERE `product_types`.`id` = 5 LIMIT 1
CollectionItem Load (0.8ms)  SELECT `collection_items`.* FROM `collection_items` WHERE `collection_items`.`id` = 31 LIMIT 1
Product Load (0.8ms)  SELECT `products`.* FROM `products` WHERE `products`.`id` = 11 LIMIT 1
ProductType Load (0.7ms)  SELECT `product_types`.* FROM `product_types` WHERE `product_types`.`id` = 2 LIMIT 1
CollectionItem Load (1.1ms)  SELECT `collection_items`.* FROM `collection_items` WHERE `collection_items`.`id` = 2 LIMIT 1
Product Load (1.0ms)  SELECT `products`.* FROM `products` WHERE `products`.`id` = 2 LIMIT 1
CACHE (0.0ms)  SELECT `product_types`.* FROM `product_types` WHERE `product_types`.`id` = 1 LIMIT 1
CACHE (0.0ms)  SELECT `collection_items`.* FROM `collection_items` WHERE `collection_items`.`id` = 1 LIMIT 1

The block of code which produces this is like this:

<% @cart.line_items.each do |line_item| %>
line_item.product.product_type.title

Though the problem is occurring throughout the site, so I think it's related to the way we've setup the relationships. I can't figureout why Rails is trying to select the full table of product, playrooms and collection_items (which is taking up the 45 seconds)

Also, we've got indexing setup on the foreign keys.

Any thoughts or advise would be appreciated.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312034 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO