[Rails] find_by_sql query running incredibly slow

[John Merlino]

Initially, I was using this query:


    report_ids = []
    if units.size > 0
      units.map(&:id).uniq.each do |id|
        report_id = Report.select(:id).where(unit_id: id).order("time 
desc").first
        unless report_id.nil?
          report_ids << report_id
        end
      end
    end    
    reports = Report.where(id: report_ids).order("longitude desc")

It looks sloppy, and it took around 7 or 8 seconds to load all the data.

So I wanted to create a cleaner, quicker query, so I tried this:

    reports = Report.find_by_sql(["SELECT DISTINCT ON (unit_id) r.* 
                                  FROM reports r 
                                  WHERE r.unit_id IN (?) 
                                  ORDER BY unit_id, time DESC", 
                                  units.map(&:id)])

But this is running incredibly slow. In fact, after 15 minutes, I just 
cancelled the process. 


Any idea why the second query is taking markedly  longer than the first?

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-talk+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/rubyonrails-talk/cd947975-2605-432d-bdfb-6e25be66e806%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.