Is it possible to cache custom sql queries in Rails?

Question

In the home_controller of my Rails 4 app, I perform a custom sql query and save the results to an instance variable

@studentscoring = ActiveRecord::Base.connection.execute sql_string_student

I then, after setting caching to true in config development config.action_controller.perform_caching = true and restarting the application, set up caching around the relevant variable in the view.

  <% cache @studentscoring do%>
    <% for lawyer in @studentscoring  %>
    <div class="span2">
      <div class="row">
        <%=  tiny_gravatar_for lawyer['name'], lawyer['email'] %>

      </div>
      ...... #code ommitted
    </div>

    <% end %>
    <% end %>

Refreshing the browser three times shows that the query is run three separate times and the last run of the query actually takes .7ms longer than the first, so I'm assuming caching is not working or I'm not doing it correctly :). Can you tell me what I'm doing wrong?

Not being an expert by any standards, I don't understand how caching can be triggered from the view with the <% cache ... do %> syntax, since by the time the view is loading haven't the controller queries already been run, therefore it's too late to tell Rails to use a cached copy?

from the server logs...

First

 (1.1ms)  with cte_scoring as (
 select
 users.id, users.name, users.email,
 (select Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from best_answers where (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from contributions where (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score
 from
 users
 where
 users.student = 'true') 

select id,
 name,
 email,
 total_score
from cte_scoring
order by total_score desc
limit 5 

3rd

  (1.8ms)  with cte_scoring as (
 select
 users.id, users.name, users.email,
 (select Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from best_answers where (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from contributions where (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score
 from
 users
 where
 users.student = 'true') 

select id,
 name,
 email,
 total_score
from cte_scoring
order by total_score desc
limit 5 

Update

The logs show that it is reading a fragment (after the queries above are run), so why would the queries have different times and the later query be slower? I would have thought the queries wouldn't be run at all if there was a fragment to read from.

Read fragment views/id/75/name/Retarded Student/email/retarstudent@gmail.com/total_score/0/id/83/name/Jim Beam/email/jimbean@gmail.com/total_score/0/id/79/name/Weird Student/email/weirdstudent@gmail.com/total_score/0/id/80/name/VegetableSTudent/email/veggiestudent@gmail.com/total_score/0/c9638e467bfd0fbf5b619ab411182256 (0.3ms)

Answer 1

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

Answer 2

From the ActiveRecord cache perspective:

The way I understand this is that the ActiveRecord query cache is per web request. Meaning that if you ran your SQL query twice in the same request that it would use the cache, but the cache is cleared at the end of each request.

Source: ActiveRecord::QueryCache middleware source

(I do believe that `ActiveRecord::Base.execute calls are cached, in general, just like queries you make with the ActiveRecord query API)

If you only want to do the query once for the lifecycle of your app (or once per several hours) you can use another Rails API: the caching API to store your cache on the file system, in memory, in memcache, or in a custom store. The Rails Guide on Caching / Cache Stores.

If you do decide to use Rails.cache the Heroku Dev Center on Caching Strategies has some code examples showing you what the API for Rails.cache looks like. It's pretty easy to use.

Why the fragment cache doesn't work like you expect

The cache call in your view means that you are defining a fragment cache. ( see the Rails Guide on Caching / Fragment caching section ). This will cache the HTML output by the view, as you are seeing in your log.

But the fragment cache only applies to the HTML. When you do your query and assign the results to @studentscoring you're doing it in the controller (right?) before the view executes.

ActiveRecord queries are usually lazy - execution is delayed until the data is really needed, like iterating the records - so your trick might have worked when using the ActiveRecord query API. However, I'd guess that ActiveRecord::Base.execute queries are not lazy. I can't prove it, but it's something you could run an experiment on.

So your fragment cache may be used, but you already paid the price for the query in the controller.