Efficient Query With Active Record (SQL)

I have here a basic shopping cart application that has products listed for sale. These products are sorted into categories. But each product can have many categories. We use a many-to-many association here. I have chosen to use has_and_belongs_to for this particular relationship. Products are also tagged. So similarly, each tag can have many products and each product can have many tags. I’ve also chosen to use HABTM since I don’t foresee needing any of the columns in the join table.

The goal of this post is to show how I write more efficient queries using INNER JOIN methods rather than relying on rails’ awesome. Well I don’t actually write the SQL statement since Active Record does it for me. (I still wish I paid more attention during SQL class in my college days)

How I wrote many_to_many queries when I started rails:

category = Category.find(params[:id])
@products = category.products.order('created_at DESC').page(params[:page]).per_page(8)


How I write my queries now:

@products = Product.where(active:true).joins(:categories, :tags).where(categories: {id: params[:cat_id]}, tags: {id: params[:tag_id]} ).order('created_at DESC').page(params[:page]).per_page(8)

 

The Improvement

If you look at the console while running your application, you’ll immediately notice that you reduce the number of database query by using the second method:

Log for first method

Category Load (0.7ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = $1 LIMIT 1 [["id", "1"]]

Product Load (0.7ms) SELECT "products".* FROM "products" INNER JOIN "categories_products" ON "products"."id" = "categories_products"."product_id" WHERE "categories_products"."category_id" = 1 AND "products"."active" = 't' ORDER BY created_at DESC LIMIT 8 OFFSET 0

 

Log for second method

Product Load (2.6ms) SELECT "products".* FROM "products" INNER JOIN "categories_products" ON "categories_products"."product_id" = "products"."id" INNER JOIN "categories" ON "categories"."id" = "categories_products"."category_id" WHERE "products"."active" = 't' AND "categories"."id" = 1 ORDER BY created_at DESC LIMIT 8 OFFSET 0

 

If you look at the time taken, it might look the same, but when you’re in production, this will make a difference, how big a difference depends on your server setup and traffic and where your bottleneck is.

Know a more efficient way to write this query? Please let me know!

Leave a Reply

Visit Us On TwitterVisit Us On FacebookVisit Us On Linkedin