In the home_controller of my Rails 4 app, I perform a custom sql query and save the results to an instance variable
在我的Rails 4应用程序的home_controller中,我执行自定义SQL查询并将结果保存到实例变量
@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.
然后,在配置开发config.action_controller.perform_caching = true中将缓存设置为true并重新启动应用程序之后,在视图中围绕相关变量设置缓存。
<% 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?
刷新浏览器三次显示查询分别运行三次,查询的最后一次运行实际上比第一次运行时长.7ms,所以我假设缓存不起作用或者我没有正确执行:)。你能告诉我我做错了什么吗?
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?
不是任何标准的专家,我不明白如何使用<%cache ... do%>语法从视图中触发缓存,因为在加载视图时,控制器查询已经没有了运行,因此告诉Rails使用缓存副本为时已晚?
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)
2 个解决方案
#1
7
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
因此,上面将首先检查缓存中的“your_cache_key”,如果数据存在则将其从缓存中返回。如果它不存在,则块将执行,它将在缓存中设置
#2
7
From the ActiveRecord cache perspective:
从ActiveRecord缓存角度来看:
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.
我理解这一点的方式是ActiveRecord查询缓存是每个Web请求。这意味着如果您在使用缓存的同一请求中运行SQL查询两次,但在每个请求结束时清除缓存。
Source: ActiveRecord::QueryCache middleware source
来源:ActiveRecord :: QueryCache中间件源
(I do believe that `ActiveRecord::Base.execute calls are cached, in general, just like queries you make with the ActiveRecord query API)
(我确实相信`ActiveRecord :: Base.execute调用通常被缓存,就像你使用ActiveRecord查询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.
如果您只想对应用程序的生命周期执行一次查询(或每隔几个小时执行一次),则可以使用另一个Rails API:缓存API,用于将缓存存储在文件系统,内存,内存缓存或内存中定制商店。缓存/缓存商店的Rails指南。
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.
如果你决定使用Rails.cache,那么Heroku开发策略中心就会有一些代码示例向你展示Rails.cache的API是什么样的。它很容易使用。
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.
视图中的缓存调用意味着您正在定义片段缓存。 (请参阅缓存/片段缓存部分的Rails指南)。这将缓存视图的HTML输出,就像您在日志中看到的那样。
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.
但片段缓存仅适用于HTML。当您执行查询并将结果分配给@studentscoring时,您将在视图执行之前在控制器中执行此操作(对吗?)。
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.
ActiveRecord查询通常是懒惰的 - 执行被延迟直到真正需要数据,比如迭代记录 - 所以你的技巧可能在使用ActiveRecord查询API时起作用。但是,我猜想ActiveRecord :: Base.execute查询不是懒惰的。我无法证明这一点,但这是你可以进行实验的东西。
So your fragment cache may be used, but you already paid the price for the query in the controller.
因此可以使用您的片段缓存,但您已经为控制器中的查询付出了代价。
#1
7
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
因此,上面将首先检查缓存中的“your_cache_key”,如果数据存在则将其从缓存中返回。如果它不存在,则块将执行,它将在缓存中设置
#2
7
From the ActiveRecord cache perspective:
从ActiveRecord缓存角度来看:
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.
我理解这一点的方式是ActiveRecord查询缓存是每个Web请求。这意味着如果您在使用缓存的同一请求中运行SQL查询两次,但在每个请求结束时清除缓存。
Source: ActiveRecord::QueryCache middleware source
来源:ActiveRecord :: QueryCache中间件源
(I do believe that `ActiveRecord::Base.execute calls are cached, in general, just like queries you make with the ActiveRecord query API)
(我确实相信`ActiveRecord :: Base.execute调用通常被缓存,就像你使用ActiveRecord查询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.
如果您只想对应用程序的生命周期执行一次查询(或每隔几个小时执行一次),则可以使用另一个Rails API:缓存API,用于将缓存存储在文件系统,内存,内存缓存或内存中定制商店。缓存/缓存商店的Rails指南。
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.
如果你决定使用Rails.cache,那么Heroku开发策略中心就会有一些代码示例向你展示Rails.cache的API是什么样的。它很容易使用。
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.
视图中的缓存调用意味着您正在定义片段缓存。 (请参阅缓存/片段缓存部分的Rails指南)。这将缓存视图的HTML输出,就像您在日志中看到的那样。
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.
但片段缓存仅适用于HTML。当您执行查询并将结果分配给@studentscoring时,您将在视图执行之前在控制器中执行此操作(对吗?)。
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.
ActiveRecord查询通常是懒惰的 - 执行被延迟直到真正需要数据,比如迭代记录 - 所以你的技巧可能在使用ActiveRecord查询API时起作用。但是,我猜想ActiveRecord :: Base.execute查询不是懒惰的。我无法证明这一点,但这是你可以进行实验的东西。
So your fragment cache may be used, but you already paid the price for the query in the controller.
因此可以使用您的片段缓存,但您已经为控制器中的查询付出了代价。