Optimizing Queries in Service Objects

I'm currently building an app and using namespaced service objects containing a single call method in each class.

Below is an actual method (I'm aware the ABC size is too large, I'm wanting to get it squared away before I break it apart into smaller chunks):

module Orders
  class CalculateNextStep
    include Services::Base

    def call(order)
      if order.next_step?
        order.next_step = Course.where(id: order.course_id).first.course_steps
                                .where(position: 1).first.id
      else
        position = CourseStep.find(order.next_step).position
        order.next_step = Course.where(id: order.course_id).first.course_steps
                                .where('course_steps.position > ?', position)
                                .first.id
      end
    end
  end
end

My question is should I move the queries into a query object? And should I just use raw PG queries versus AR finder methods?

Hi Jacob,

When you say "optimize", what exactly do you mean? Are your current queries slow to perform, or would you just like to refactor them so they're more efficient to work with?

You don't really gain much in the terms of speed by doing raw sql vs an AR query (in most cases), and you can actually slow your queries down if you don't know what you're doing (in some cases). Also, raw sql is generally uglier / harder to read than AR methods so maintainability can become an issue later.

As for refactoring to be more efficient to work with, will these queries only ever be used when your app calls on this service object or are these queries used in multiple places? If they're only ever used like this here, then I'd question what's the benefit of abstracting it? Do you gain anything by putting that snippet of code in another file? If not, then just reorganize your code in this file so it's easy to come back to a few weeks / months / years later and call it a day!

Well hey right back at you Jacob!

Sorry for not explaining better. I'd like to refactor them to be more efficient to work with and speed them up as much as possible.

The calls will be used in multiple places. If a call is only going to be used in one place, I just place it in the model class.

I appreciate the input!

Sweet, then yeah I'd just clean up the queries some and ensure you have proper indexes on your tables. I'm far from an expert of DB query optimization but I really don't think you're going to gain much by moving away from what AR provides you, and so I'd spend my energy elsewhere in the project.

As for abstracting it out, really all this boils down to is maintainability. I don't believe there's really any measurable performance increase by having all your queries for that model in the model file vs abstracted out into 50 files. So I'd just do what makes the most sense for how your application functions. If you're ever bored, read up on design patterns, lot of good info there!

1. If you have more than one result but always take first and you don't sort not only that it might be inconsistent , you're pulling data you don't need. Add limit(1) on the query.

2. Since you're only looking for id anyhow , don't load the entire ActiveRecord use select and pluck to to improve your memory and runtime performance.

3. Can you add a course_id indexed foreign key to CourseStep model? Than you will be able to query CourseStep.where( course_id: order.course_id , position: 1 ).limit(1).take.pluck(:id).first

Hey Ohad Dahan,

Just wanted to chime in about select - select should be used with caution as it still fetches all records and then starts 'selecting' the records to build an array of results.

Check out this SO: https://stackoverflow.com/a/34034648

select was mentioned when you still want to fecth ActiveRecord while pluck returns an array.
If you want to limit the size , use limit as I suggested.

If you want to minimze your memory footstamp Model.where( condition ).select(:id) should be smaller thatn Model.where( condition ) while Model.where( condition ).pluck will be the smallest ( pluck also receive attribute names like Model.where( condition ).pluck(:id) ).

Note in the link you provided:
Almost always, you'll want to use where **rather** than select. So, when might you use select? Well you'd use it (**often in conjunction with where**) when you want to filter a relatively small number of records based on some ruby conditions that would be reasonably hard to translate to SQL.

I never said you should use select instead of where but ONLY in conjunction , I actually urge to always use limit to avoid corner cases blowing up your machine memory.

don't load the entire ActiveRecord use select and pluck to to improve your memory and runtime performance.

That's the part that I'm referring to. If you have experience with queries then you already know this, but to newer developers the fact that select still loads every object into memory is why I said to use caution. I'm certinally not saying to never use it, just be cautious of how you're using it, it's an iceberg function.

I'm not sure that I follow , can you write the query you warn from?

It's not necessarily a single query that would illustrate the problem, it's how it's used that's the problem. That's why I say it's not something a more experienced developer may get caught by, but to a new developer, the consequences aren't so obvious.

Off the top of my head, let's say you have a process that generates a lot of stats for a large group of records. Depending on the calculation being run, you may need to do some additional filtering from the initial group of objects you passed to the generator before passing it through to that particular calculation.

In this instance, a new developer may be inclined to use select to handle this filtering, which may be a perfectly feasible solution if you know that there will never be a lot of records or you can safely use limit, however, you may not always be able to use limit or guarantee that there won't be a lot of objects to process. So care should be given as you're manipulating your data that you don't have any unexpected bottlenecks.

I'm not saying what you said was wrong in any way, just trying to add to the knowledge pool from personal experiences. :)