CODEWITHSUNDEEP
mysqlruby-on-railsperformanceactiverecord
Augusto Pedraza
Augusto Pedraza

Reputation: 548

ActiveRecord raw sql slower than MySql

I would like that someone help me to understand why executing a raw sql query from ActiveRecord(33.6 s) is less performant that Mysql(14.0 s).

The query is the following:

SELECT 
      CASE 
        WHEN
        WeekDay(c.collect_date) >=0 AND WeekDay(c.collect_date) <5 THEN 1
      ELSE 0 
      END AS is_business_day, 
      HOUR(c.collect_date) consumption_hour, 
        SUM(c.energy), 
        AVG(c.power) 
    FROM consumptions c
    INNER JOIN devices d ON c.device_id=d.device_id AND d.ftp_id=1    
    GROUP BY CASE WHEN
        WeekDay(c.collect_date) >=0 AND WeekDay(c.collect_date) <5 THEN 1
      ELSE 0 
      END, HOUR(c.collect_date)

And below are the screenshot with the times.

Active Record time

enter image description here

Thanks in advance!

Upvotes: 1

Views: 464

Answers (1)

Augusto Pedraza
Augusto Pedraza

Reputation: 548

Considering the times taken from profiling(see Rails Github issue), the conclusion is:

The wasted time by ActiveRecord is similar to Mysql, but slower than *Mysql WorkBench**

Upvotes: 1

Related Questions