DEV Community

Discussion on: Preventing Useless Database Hits

Collapse
 
moopet profile image
Ben Sinclair

There're a couple of things I don't understand about this.

Why would ActiveRecord want to ensure that no records are returned with WHERE 1=0? Unless... do you want a "chainable relation" of foos to which you can later add items? That seems like a topsy-turvy way of working. What am I getting mixed up on?

If the service is smart enough to inject a condition if the query is expected to be empty, why isn't it smart enough to not bother running it in the first place?

Why isn't the query cached, since it's identical to the previous one and most databases will have a cheap memory cache for that sort of thing?

In short, I don't understand why Ruby would be asking the database for something it already knows is going to be empty.

Collapse
 
molly profile image
Molly Struve (she/her)

Why would ActiveRecord want to ensure that no records are returned with WHERE 1=0?

Your assumption is correct, they do this so they can return an active record relation. I dove into the ActiveRecord codebase trying to find a way to update it so that it would not hit the database and would return a None Relation instead but I was never able to figure it out and get all the specs passing.

If the service is smart enough to inject a condition if the query is expected to be empty, why isn't it smart enough to not bother running it in the first place?

What we are working with when dealing with these empty arrays is a method that we expect to return an active record relation so that we can chain other things to it. This is why we want to use the none scope, then we don't have to place conditionals all over the code base when using the method since it handles that internally. Here is one of the methods we use it in

def connectors
  @connectors ||= begin
    connector_ids = vulns.select(:connector_id)
    if connector_ids.empty?
     Connector.none
    else
     Connector.with_connector_definition(connector_ids)
    end
  end
end

Then all over the codebase we can call connectors.something and never have to worry about it.

Why isn't the query cached, since it's identical to the previous one and most databases will have a cheap memory cache for that sort of thing?

I tried to simplify the concept for the example but in real life these queries are being executed across many workers so the only layer of cacheing available is at the database level. Even then, we dont want to have to make a round trip call to the database if we dont have too

I don't understand why Ruby would be asking the database for something it already knows is going to be empty.

It is not actually Ruby doing the asking, it is Rails. When we take Ruby frameworks and use them they can hide a lot of what is going on under the hood and if you are not careful you can end up with lots of these types of useless database hits. The frameworks are trying to be as user friendly as possible and in some cases that can cause a loss in performance optimizations

Hopefully that explanation makes sense. Let me know if you have any other questions!