Would using Redis with Rails provide any performance benefit for this specific kind of queries
I don't know if this is the right place to ask question like this, but here it goes:

I have an intranet-like Rails 3 application managing about 20k users which are in nested-set (preordered tree - http://en.wikipedia.org/wiki/Nested_set_model).
Those users enter stats (data, just plain numeric values). Entered stats are assigned to category (we call it Pointer) and a week number.

Those data are further processed and computed to Results.
Some are computed from users activity + result from some other category... etc.
What user enters isn't always the same what he sees in reports.

Those computations can be very tricky, some categories have very specific formulae.

But the rest is just "give me sum of all entered values for this category for this user for this week/month/year".

Problem is that those stats needs also to be summed for a subset of users under selected user (so it will basically return sum of all values for all users under the user, including self).

This app is in production for 2 years and it is doing its job pretty well... but with more and more users but it's also pretty slow when it comes to server-expensive reports, like "give me list of all users under myself and their statistics. One line for summed by their sub-group and one line for their personal stats"). Of course, users wants (and needs) their reports to be as actual as possible, 5 mins to reflect newly entered data is too much for them.
To stay realtime, we cannot do the high-intensive sqls directly...
Those sqls are hard to optimize and I'm glad I've moved from this approach... (caching is not an option. See below.)

Current app goes like this:

* frontend: when user enters new data, it is saved to simple mysql table, like `[user_id, pointer_id, date, value]` and there is also insert to the queue.

* backend: then there is calc_daemon process, which every 5 seconds checks the queue for new "recompute requests". We pop the requests, determine what else needs to be recomputed along with it (pointers have dependencies... simples case is: when you change week stats, we must recompute month and year stats...). It does this recomputation the easy way.. we select the data by customized per-pointer-different sqls generated by their classes.
* those computed results are then written back to mysql, but to partitioned tables (one table per year). One line in this table is like `[user_id, pointer_id, month_value, w1_value, w2_value, w3_value, w4 value]`. This way, the tables have ~500k records (I've basically reduced 5x # of records).
* when frontend needs those results it does simple sums on those partitioned data, with 2 joins (because of the nested set conds).

The problem is that those simple sqls with sums, group by and join-on-the-subtree take like 200ms each... just for a few records.. and we run a lot of these sqls... I think they are optimized the best they can, according to `explain`... but they are just hard for it.

So... The QUESTION:

Can I rewrite this to use Redis (or other fast key-value store) and see any benefit from it when I'm using Ruby and Rails? As I see it, if I'll rewrite it to use redis, I'll have to run much more queries against it than I have to with mysql, and then perform the sum in ruby manually... so the performance can be hurt considerably... I'm not really sure if I could write all the possible queries I have now with redis... Loading the users in rails and then doing something like "redis, give me sum for users 1,2,3,4,5..." doesn't seem like right idea... But maybe there is some feature in redis that could make this simpler?)...

Or would you suggest me to rewrite this app to different language (java?) and to compute the results in memory instead? :) (I've tried to do it SOA-way but it failed on that I end up one way or another with XXX megabytes of data in ruby... especially when generating the reports... and gc just kills it...) (and a side effect is that one generating report blocks the whole rails app :/ )

Suggestions are welcome.
Would using Redis with Rails provide any performance benefit for this specific kind of queries
===

I don't know if this is the right place to ask question like this, but here it goes:

I have an intranet-like Rails 3 application managing about 20k users which are in nested-set (preordered tree - http://en.wikipedia.org/wiki/Nested_set_model).
Those users enter stats (data, just plain numeric values). Entered stats are assigned to category (we call it Pointer) and a week number.

Those data are further processed and computed to Results.
Some are computed from users activity + result from some other category... etc.
What user enters isn't always the same what he sees in reports.

Those computations can be very tricky, some categories have very specific formulae.

But the rest is just "give me sum of all entered values for this category for this user for this week/month/year".

Problem is that those stats needs also to be summed for a subset of users under selected user (so it will basically return sum of all values for all users under the user, including self).

This app is in production for 2 years and it is doing its job pretty well... but with more and more users but it's also pretty slow when it comes to server-expensive reports, like "give me list of all users under myself and their statistics. One line for summed by their sub-group and one line for their personal stats"). Of course, users wants (and needs) their reports to be as actual as possible, 5 mins to reflect newly entered data is too much for them.
To stay realtime, we cannot do the high-intensive sqls directly...
Those sqls are hard to optimize and I'm glad I've moved from this approach... (caching is not an option. See below.)

Current app goes like this:

* frontend: when user enters new data, it is saved to simple mysql table, like `[user_id, pointer_id, date, value]` and there is also insert to the queue.

* backend: then there is calc_daemon process, which every 5 seconds checks the queue for new "recompute requests". We pop the requests, determine what else needs to be recomputed along with it (pointers have dependencies... simples case is: when you change week stats, we must recompute month and year stats...). It does this recomputation the easy way.. we select the data by customized per-pointer-different sqls generated by their classes.
* those computed results are then written back to mysql, but to partitioned tables (one table per year). One line in this table is like `[user_id, pointer_id, month_value, w1_value, w2_value, w3_value, w4 value]`. This way, the tables have ~500k records (I've basically reduced 5x # of records).
* when frontend needs those results it does simple sums on those partitioned data, with 2 joins (because of the nested set conds).

The problem is that those simple sqls with sums, group by and join-on-the-subtree take like 200ms each... just for a few records.. and we run a lot of these sqls... I think they are optimized the best they can, according to `explain`... but they are just hard for it.

So... The QUESTION:

Can I rewrite this to use Redis (or other fast key-value store) and see any benefit from it when I'm using Ruby and Rails? As I see it, if I'll rewrite it to use redis, I'll have to run much more queries against it than I have to with mysql, and then perform the sum in ruby manually... so the performance can be hurt considerably... I'm not really sure if I could write all the possible queries I have now with redis... Loading the users in rails and then doing something like "redis, give me sum for users 1,2,3,4,5..." doesn't seem like right idea... But maybe there is some feature in redis that could make this simpler?)...

Or would you suggest me to rewrite this app to different language (java?) and to compute the results in memory instead? :) (I've tried to do it SOA-way but it failed on that I end up one way or another with XXX megabytes of data in ruby... especially when generating the reports... and gc just kills it...) (and a side effect is that one generating report blocks the whole rails app :/ )

Suggestions are welcome.