reports_as_sparkline is slower than doing :group manually (even with a full cache)

[marcoow]

see http://github.com/marcoow/reports_as_sparkline/issues#issue/11

[marcoow]

reported by http://github.com/RogerPodacter:

>> User.benchmark("reports_as_sparkline") do
?>   10.times do
?>     for u in User.all
>>       history = Annotation.creations_report(:conditions => ["created_by_id = ?", u.id],   :limit => 30)
>>     end
>>   end
>>   nil
>> end
reports_as_sparkline (20545.5ms)
=> nil
>> 
?> User.benchmark("traditional") do
?>   10.times do
?>     for u in User.all
>>       raw = Annotation.count(:conditions => ['created_at >= ? and created_by_id = ?',    30.days.ago, u.id], :group => "DATE(created_at)")
>> 
?>       history = []
>>       30.downto(1) do |d|
?>         history << (raw[d.days.ago.to_date.to_s] || 0)
>>       end
>>     end
>>   end
>>   nil
>> end
traditional (9869.2ms)

reportable takes twice as long as selecting the rows directly

[marcoow]

the main differences are:

• reportable used DATE_FORMAT instead of DATE
• reportable also selects DATE_FORMAT(...) and orders by DATE_FORMAT(...)
• reportable has to process the data read from the cache (look for holes etc.)

[marcoow]

my tests show reportable takes 3 times as long as direct selects. Although the above statements are not really comparable, some work should be put on the topic of performance.

[marcoow]

One more note on the above benchmarks: the two above statements are of course fundamentally different:

SELECT * FROM `reportable_cache` WHERE (`model_name` = 'User' AND `report_name` = 'registrations' AND `grouping` = 'day' AND `aggregation` = 'count' AND `conditions` = '' AND reporting_period >= '2009-12-01') ORDER BY reporting_period ASC LIMIT 100

against

SELECT count(*) AS count_all, DATE(created_at) AS date_created_at FROM `users` WHERE (created_at >= '2009-12-01 20:56:32') GROUP BY DATE(created_at)