[Rails] find_by_sql query running incredibly slow

Ruby on Rails Tuesday, March 4, 2014

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

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+unsubscribe@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.

Ruby on Rails