Topic: Adding Indexes and General Application Enhancements

Does this process look normal, should the database be selecting that many times?
I think there has to be a better way to do this, any help is appreciated.

Rendered products/_product (0.02709)
Rendered products/_side (0.00046)
Rendered /layouts/partials/_universal_header (0.00172)
  Category Load (0.002067)   SELECT * FROM categories ORDER BY list
  Join Table Columns (0.011168)   SHOW FIELDS FROM categories_products
  Product Load (0.004900)   SELECT * FROM products INNER JOIN categories_products ON products.id = categories_products.product_id WHERE (categories_products.category_id = 5 )
  Join Table Columns (0.007489)   SHOW FIELDS FROM categories_products
  Product Load (0.006806)   SELECT * FROM products INNER JOIN categories_products ON products.id = categories_products.product_id WHERE (categories_products.category_id = 1 )
  Join Table Columns (0.008121)   SHOW FIELDS FROM categories_products
  Product Load (0.007109)   SELECT * FROM products INNER JOIN categories_products ON products.id = categories_products.product_id WHERE (categories_products.category_id = 2 )
  Join Table Columns (0.011102)   SHOW FIELDS FROM categories_products
  Product Load (0.006453)   SELECT * FROM products INNER JOIN categories_products ON products.id = categories_products.product_id WHERE (categories_products.category_id = 3 )
  Join Table Columns (0.006691)   SHOW FIELDS FROM categories_products
  Product Load (0.005724)   SELECT * FROM products INNER JOIN categories_products ON products.id = categories_products.product_id WHERE (categories_products.category_id = 4 )
Rendered /layouts/partials/_productsnav (0.10492)

Here is what generates that:

#main.product
  - if logged_in?
    = link_to 'New Product', :action => 'new'
  %p.list-info
  %ul#product_list
    -cache do
      = render :partial => 'product', :collection => @category.products
-if logged_in?
  = sortable_element('product_list', :update => 'list-info', :url => {:controller => 'products', :action => "order"}, :ghosting => true)
-cache do   
  = render :partial => 'side'

Re: Adding Indexes and General Application Enhancements

Well, yes, it does look normal. You don't seem to be using an :include => :products in your Category.find(:all) - if you'd do, it would poll all information in a single SQL query (attention: this might need lots of memory if you have many, many products). Currently rails grabs all products for each category one after the other, therefor resulting in more SQL queries.

Re: Adding Indexes and General Application Enhancements

And, just to add what aleco is saying, more queries aren't always bad. If you have 5 queries, all selecting 5 things, it can be a lot faster than 1 query grabbing 1000 things, but it could also be slower than 1 query grabbing 25 things. It just depends on what you data looks like.

BJ Clark
the science department
http://www.scidept.com/