Rails: scaling with group calculations

Question

Hello, currently I am running something similar to this command:

Person.sum(:cholesterol, :group => :age)

which seems to work great when I have a small number of records. However when I attempt to call this with a few thousand records or more in the table, it takes literally minutes to run. However, the SQL querey:

SELECT sum(`people`.cholesterol) AS sum_cholesterol, age AS age FROM `people` GROUP BY age

takes around 0.02 seconds on the same thousand number of records. Once I have the data I dont need to do much more than display it. Is there a more efficient way of doing this? I am aware that I can manually make sql queries from models and controllers, I dont know if this would be a viable option as I have not found a clear way to work with the result once the query is made.

Answer 1

Probably what it's happening is that Rails is instantiating an object per row requested or used on the sum, or keeping in memory information to help that calculation.

Try using the find_by_sql method, something like:

Person.find_by_sql("SELECT sum(`people`.cholesterol) AS sum_cholesterol, age AS age FROM `people` GROUP BY age")

And see how much it takes.

Also, check your logs. They gave you plenty of information on where is taking so long render.

Answer 2

How are you running your app?

Logs can give you great information and with mongrel, in development mode you will see the sql queries being executed by your app directly in the terminal or in your log file if you detached mongrel.

Maybe rails is not doing the query you are expecting?

You can also use the solution suggested by Yaraher.

find_by_sql will directly execute the request an return you a Ruby object with getter methods ready to be used

result = person.find_by_sql("SELECT sum(`people`.cholesterol) AS sum_cholesterol, age AS age FROM `people` GROUP BY age")

p result.sum_cholesterol

Answer 3

You can also try:

Person.find(:all, :select => "sum(cholestorol) as 'sum_cholesterol'", :group => "age")

It may be a bit cleaner, but I don't know if it'll be faster than find by sql.