Handling a massive query in Rails

Question

What's the best way to handle a large result set with Rails and Postgres? I didn't have a problem until today, but now I'm trying to return a 124,000 record object of @network_hosts, which has effectively DoS'd my development server.

My activerecord orm isn't the prettiest, but I'm pretty sure cleaning it up isn't going to help in relation to performance.

@network_hosts = []
@host_count = 0
@company.locations.each do |l|
  if  l.grace_enabled == nil || l.grace_enabled == false
    l.network_hosts.each do |h|
      @host_count += 1
      @network_hosts.push(h)
      @network_hosts.sort! { |x,y| x.ip_address <=> y.ip_address }
      @network_hosts = @network_hosts.first(5)
     end
  end
end

In the end, I need to be able to return @network_hosts to the controller for processing into the view.

Is this something that Sidekiq would be able to help with, or is it going to be just as long? If Sidekiq is the path to take, how do I handle not having the @network_hosts object upon page load since the job is running asyncronously?

Answer 1

I believe you want to (1) get rid of all that looping (you've got a lot of queries going on) and (2) do your sorting with your AR query instead of in the array.

Perhaps something like:

NetworkHost.
  where(location: Location.where.not(grace_enabed: true).where(company: @company)).
  order(ip_address: :asc).
  tap do |network_hosts|
    @network_hosts = network_hosts.limit(5)
    @host_count = network_hosts.count
  end

Something like that ought to do it in a single DB query.

I had to make some assumptions about how your associations are set up and that you're looking for locations where grace_enabled isn't true (nil or false).

I haven't tested this, so it may well be buggy. But, I think the direction is correct.