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?

[Colin Law]

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

Colin

[Frederick Cheung]

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

    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?

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