是否可以在 Rails 中缓存自定义 sql 查询? [英] Is it possible to cache custom sql queries in Rails?

问题描述

在我的 Rails 4 应用程序的 home_controller 中，我执行自定义 sql 查询并将结果保存到实例变量

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

然后，在配置开发中将缓存设置为 true config.action_controller.perform_caching = true 并重新启动应用程序后，在视图中围绕相关变量设置缓存.

 <% cache @studentscoring do%><@studentscoring 中的律师%><div class="span2"><div class="row"><%= tiny_gravatar_for 律师['姓名'], 律师['email'] %>

...... #代码省略

<%结束%><%结束%>

刷新浏览器三次表明查询运行了三次，最后一次运行查询实际上比第一次运行时间长 0.7 毫秒，所以我假设缓存不起作用或者我没有正确执行:).你能告诉我我做错了什么吗?

不是任何标准的专家，我不明白如何使用 <% cache ... do %> 语法从视图中触发缓存，因为在加载视图时还没有控制器查询已经运行，因此现在告诉 Rails 使用缓存副本为时已晚?

来自服务器日志...

第一次

 (1.1ms) with cte_scoring as (选择users.id、users.name、users.email、(选择 Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +(从 best_answers 中选择 Coalesce(sum(value),0) 其中 (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +(从贡献中选择 Coalesce(sum(value),0) 其中 (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score从用户在哪里users.student = '真')选择身份证，名称，电子邮件，总得分来自 cte_scoring按 total_score desc 排序限制 5

第三个

 (1.8ms) with cte_scoring as (选择users.id、users.name、users.email、(选择 Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +(从 best_answers 中选择 Coalesce(sum(value),0) 其中 (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +(从贡献中选择 Coalesce(sum(value),0) 其中 (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score从用户在哪里users.student = '真')选择身份证，名称，电子邮件，总得分来自 cte_scoring按 total_score desc 排序限制 5

更新

日志显示它正在读取一个片段(在运行上述查询之后)，那么为什么查询有不同的时间并且后面的查询更慢?如果有一个片段可供读取，我会认为查询根本不会运行.

阅读片段 views/id/75/name/Retarded Student/email/[e-mail]/total_score/0/id/83/name/Jim Beam/email/[e-mail]/total_score/0/id/79/name/Weird Student/email/[e-mail]/total_score/0/id/80/name/VegetableSTudent/email/[e-mail]/total_score/0/c9638e467bfd0fbf5b61023451

解决方案

在控制器中缓存查询结果.一次调用就可以读或写回缓存(即如果缓存中的数据不存在则设置它)

def 索引@studentscoring = Rails.cache.fetch("your_cache_key", :expires_in => 5.minutes) 做ActiveRecord::Base.connection.select_rows(sql_string_student)结尾结尾

所以上面会首先检查缓存中是否有"your_cache_key"，如果数据存在，就会从缓存中返回它.如果它不存在，则该块将执行并将其设置在缓存中

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/[e-mail]/total_score/0/id/83/name/Jim Beam/email/[e-mail]/total_score/0/id/79/name/Weird Student/email/[e-mail]/total_score/0/id/80/name/VegetableSTudent/email/[e-mail]/total_score/0/c9638e467bfd0fbf5b619ab411182256 (0.3ms)

解决方案

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

这篇关于是否可以在 Rails 中缓存自定义 sql 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！