Find_by_sql query running incredibly slow

Rails
1

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?

2

On 5 March 2014 00:32, John M. [email protected] wrote:

    end
                              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?

No, but have you got indexes on unit_id and time?

Colin

3

On Wednesday, March 5, 2014 12:32:04 AM UTC, John M. wrote:

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

Run the query through explain (depending on your rails version, that
might
get logged for you automatically) and see what your database is doing.

Fred