Slow queries? Frightening timeouts? Review your code and see if you can apply these simple yet powerful performance tips!

When developing a feature, we don’t always have in mind, or don’t know, how big the volume of data our queries are going to have to cope with. Over time, this volume will probably grow and your application could become sluggish and even timeout on you. To avoid this, preemptive optimization is a good idea, but in case something slipped, it is nice to review your queries at some point with a fresher pair of eyes and (perhaps) a bit more of knowledge. Here are some things that have proven to be helpful for us:

1 Query with id‘s instead of full objects

Let’s say we need to look for all the bookings that are tied to a certain set of @destinations, we could do this:

bookings = Booking.where(:destination.in => @destinations.to_a)

But we can things work much faster if we do the following:

bookings = Booking.where(:destination_id.in => @destinations.map(&:id))

2 Enter the pluck

Continuing with the previous example, we can make it even faster, if we’re lucky enough to be using mongoid >= 3.1, just change your map(&:<attribute>) calls for pluck(:<attribute>), which is blazing fast in comparison.

3 Avoid select and collect, use scopes

In the same spirit as the previous one, with queries like:

baskets_ids  = Basket.paid.where(:booking_id.in => bookings).pluck(:id)
products_ids = Product.pre_start_date.pluck(:id)
@basket_products = BasketProduct.includes(:basket, :product).where(:basket_id.in => baskets_ids, :product_id.in => products_ids).select{|bp| bp.actionnable?}

If we look at our actionnable? method is:

class BasketProduct
  #...
  def actionnable?
    basket.present? ? basket.paid_at.present? && dispatched_at.blank? && cancelled_at.blank? : false
  end
  #...
end

We’re just checking for a field not being nil or the value of a boolean field, and for that query we already have the basket.paid_at covered (see scope paid con the baskets line), so we can write up scopes in the BasketProduct model that do that check for us:

class BasketProduct
 #...
 scope :not_dispatched, ->{where(:dispatched_at.exists => false, :dispatched_at => nil)}
 scope :live,           ->{where(:cancelled_at.exists => false,  :cancelled_at => nil)}
 #...
end

Remember to have the .exists bits in case the fields are not set to a value by default. And then, use the scopes and get the select out for a dramatic performance increase:

@basket_products = BasketProduct.includes(:basket, :product).not_dispatched.live.where(:basket_id => baskets_ids, :product_id.in => products_ids)

4 Eager loading

If you’re going to use other models, tied to the main model of your query, eager load them to avoid the n+1 problem, as we’ve been doing the whole time. But I’m pretty sure you already knew about this one, didn’t you? 😉

Anyway, if you’re on mongoid v3 you’ll still be able to tinker with the IdentityMap setting, but you can forget about it if you’re on v4.

Conclusion

This post covers simple things, but I hope it’s still useful to someone. I’d be very glad to know that I’ve helped someone shave 10 seconds off a query with these little bits of advice, because that sure feels GOOD!

Picture ‘Spindle’ by Thèo, used under CC BY 2.0 license.