How to Cache a Calculated Column in Rails

How can I cache a calculated column in rails?

1. You can stuff the actually cached values in the Rails cache (use memcached if you require that it be distributed).

2. The tough bit is cache expiry, but cache expiry is uncommon, right? In that case, we can just loop over each of the parent objects in turn and zap its cache, too. I added some ActiveRecord magic to your class to make getting the parent objects simplicity itself -- and you don't even need to touch your database. Remember to call Part.sweep_complicated_cache(some_part) as appropriate in your code -- you can put this in callbacks, etc, but I can't add it for you because I don't understand when complicated_calculation is changing.

   class Part < ActiveRecord::Base
     has_many :sub_parts, :class_name => "Part"
     belongs_to :parent_part, :class_name => "Part", :foreign_key => :part_id
   
     @@MAX_PART_NESTING = 25 #pick any sanity-saving value
   
     def complicated_calculation (...)
       if cache.contains? [id, :complicated_calculation]
         cache[ [id, :complicated_calculation] ]
       else
         cache[ [id, :complicated_calculation] ] = complicated_calculation_helper (...)
       end
     end
   
     def complicated_calculation_helper
       #your implementation goes here
     end
   
     def Part.sweep_complicated_cache(start_part)
       level = 1  # keep track to prevent infinite loop in event there is a cycle in parts
       current_part = self
   
       cache[ [current_part.id, :complicated_calculation] ].delete
       while ( (level <= 1 < @@MAX_PART_NESTING) && (current_part.parent_part)) {
        current_part = current_part.parent_part)
        cache[ [current_part.id, :complicated_calculation] ].delete
       end
     end
   end
   

How can I cache a calculated attribute in Rails 3 similar to counter_cache?

Edit, based on comment:

Ok, so you've got a Like model and a User model. Put the function to update popularity in a method in User, then an after_create or after_save (if it can change after creation) callback on Like to trigger it for the User who received it:

class User < ActiveRecord::Base
  has_many :likes

  def calculate_popularity
    update_attribute(:popularity, existing_function(foo))
  end
end

class Like < ActiveRecord::Base
  belongs_to :user

  after_create :update_user_popularity

  def update_user_popularity
    user.calculate_popularity
  end
end

Obviously, if what receives Likes is various sorts of user activity, rather than the user themselves, you'll need to dig down through your associations to reach the user from the Like, but that shouldn't be too hard.

:counter_cache = true for storing sum

No. You'll have to implement it yourself. Counter-cache is for storing the number of associated records only. You could implement it using a callback on Score to update the associated User. See also How can I cache a calculated column in rails?

Further, unless you have noticeable performance issues with summing each time, avoid using a cache like this. It's just something that can easily go wrong and get out-of-date. It's not worth the trouble if you don't really need it.

Cache a complex calculation in Rails 3 model

You're basically paying a price for not wanting to store the balance in each transaction. You could optimize your database with indices and use caches etc; but fundamentally you'll run into the problem that calculating a balance will take a long time, if you have lots of transactions.

Keep in mind that you'll continue to get new transactions, and your problem will thus get worse over time.

You could consider several design alternatives. First, like Douglas Lise mentioned, you could store the balance in each transaction. If an earlier dated transaction comes in, it means you may have to do an update of several transaction since that date. However, this has an upper-bound (depending on how "old" transactions you want to allow), so it has a reasonable worst-case behavior.

Alternatively, you can do a reconciliation step. Every month you "close the books" on transactions older than X weeks. After reconciliation you store the Balance you calculated. In def balance you now use your existing logic, but also refer to "balance as of the previous reconciliation". This again, provides a reasonable and predictable worst-case scenario.

Is it possible to cache custom sql queries in Rails?

Cache the query results in your controller. You can read or write back to the cache in one call (that is, set the data in the cache if it does not already exist)

def index
  @studentscoring = Rails.cache.fetch("your_cache_key", :expires_in => 5.minutes) do
    ActiveRecord::Base.connection.select_rows(sql_string_student)
  end
end

So the above will first check the cache for "your_cache_key" and if the data exists will return it from the cache. If it does not exist than the block will execute and it will be set in the cache

Approaches for caching calculated values

In my work I use Bold for Delphi that can manage unlimited complex structures of cached values depending on each other. Usually each variable only holds a small part of the problem. In this framework that is called derived attributes. Derived because the value is not saved in the database, It just depends on on other derived attributes or persistant attributes in the database.

The code behind such attribute is written in Delphi as a procedure or in OCL (Object Constraint Language) in the model. If you write it as Delphi code you have to subscribe to the depending variables. So if attribute C depends on A and B then whenever A or B changes the code for recalc C is called automatically when C is read. So the first time C is read A and B is also read (maybe from the database). As long as A and B is not changed you can read C and got very fast performance. For complex calculations this can save quite a lot of CPU-time.

The downside and bad news is that Bold is not offically supported anymore and you cannot buy it either. I suppose you can get if you ask enough people, but I don't know where you can download it. Around 2005-2006 it was downloadable for free from Borland but not anymore.
It is not ready for D2009 as someone have to port it to Unicode.

Another option is ECO with dot.net from Capable Objects. ECO is a plugin in Visual Studio. It is a supported framwork that have the same idea and author as Bold for Delphi. Many things are also improved, for example databinding is used for the GUI-components. Both Bold and ECO use a model as a central point with classes, attributes and links. Those can be persisted in a database or a xml-file. With the free version of ECO the model can have max 12 classes, but as I remember there is no other limits.

Bold and ECO contains lot more than derived attributes that makes you more productive and allow you to think on the problem instead of technical details of database or in your case how to cache values. You are welcome with more questions about those frameworks!

Edit:
There is actually a download link for Embarcadero registred users for Bold for Delphi for D7, quite old... I know there was updates for D2005, ad D2006.

calculated fields: to store in DB or not to store?

If you're asking "is it really necessary to store calculated values in the DB" I answer you. No, it's not necessary.

But it can give you some pros. For example if you have lots of users and the users call those values calculating a lot then it could be more winnable strategy to calculate them once in a while. It will save your server resources.

Your real question now is "What will be more effective for you? Calculate values each time or calculate them once in a while and store in DB?"

---

Related Topics