SQL缓存和实体框架 [英] SQL Caching and Entity Framework

问题描述

我整理了一个小的ASP.NET MVC 2网站，该网站进行了一些非常广泛的日期挖掘/table-joins/etc.

I have put together a small ASP.NET MVC 2 site that does some very extensive date mining/table-joins/etc.

使用MVC，我有一个控制器，它以许多不同的形式(表，图像等)返回数据.为了避免频繁访问数据库，我有双重缓存机制:

Using MVC, I have a controller that returns the data in many different forms (tables, images, etc). To save hitting the database frequently I have a dual cache mechanism:

1. 对于相同操作的相同参数，我将OutputCacheAttribute与VaryByParam = "*"一起使用.
2. 假设该操作的某些参数已更改(或调用了另一个操作)，则仍然有可能以前曾请求过我的数据"，因此在数据库第一次命中后，我将数据存储在视图模型中，我是通过.NET 4.0 System.Runtime.Caching.ObjectCache实现的.

1. For identical parameters to the same action I use the OutputCacheAttribute with VaryByParam = "*".
2. Assuming some parameter to the action has changed (or another action is called), it is still possible that my "data" has previously been requested, so I store the data in a view model after the first hit of the database, I achieve this with a .NET 4.0 System.Runtime.Caching.ObjectCache.

控制器内部ObjectCache的示例:

private static readonly ObjectCache cache = 
      new MemoryCache("CompareControllerCache");
private static void CacheObject(ViewModel obj, 
                                string param1, 
                                int someOtherParam )
{
    string key = string.Format("{0}-{1}", param1, someOtherParam);
    Trace.WriteLine(string.Format("Adding {0} to the cache", key));
    cache.Add(key, obj, new CacheItemPolicy
         {
             SlidingExpiration = TimeSpan.FromMinutes(1)
         });
}

// Corresponding GetCachedObject with similar key defining logic.

这给我带来了很好的性能改进，但是失败的地方是CacheItemPolicy非常简单.理想情况下，我希望缓存窗口更大，但是如果数据库发生更改，缓存项将过期.

This gives me a good performance improvement, but where it fails is on the CacheItemPolicy being very simple. Ideally, I'd like the cache-window to be bigger but have the cached item expire if the database changes.

CacheItemPolicy似乎支持ChangeMonitors集合，我可以在其中添加一个SqlChangeMonitor，但是在尝试构造它时，我就停了下来.

The CacheItemPolicy seems to support this with the ChangeMonitors collection, to which I could add a SqlChangeMonitor, but when trying to construct this is where I come to a halt.

我正在使用Entity Framework 4访问SQL数据库，我如何如何构造SqlChangeMonitor来监视可能触发缓存过期的几个数据库表?

I am using Entity Framework 4 to access an SQL Database, how how do I construct the SqlChangeMonitor to monitor the couple of database tables that are likely to trigger a cache expire?

SqlChangeMonitor由带有SqlCommand的SqlDependency构造-我该如何闩锁Entity Framework对我的数据库的封装?

SqlChangeMonitor is constructed with an SqlDependency which takes an SqlCommand - how can I latch upon Entity Framework's encapsulation of my database?

推荐答案

可以将任何LINQ查询包装在SqlDependency中，包括EF Linq查询，请参见基于SqlDependency的LINQ查询缓存中谈到了这一点. .

It is possible to wrap any arbitrary LINQ query in a SqlDependency, including EF Linq queries, see LinqToCache. But unfortunately the way EF chooses to formulate the SQL for the queries, even the most simple from t in context.table select t, is incompatible with the Query Notificaiton restriction and the SqlDependency is invalidated straight away as an invalid statement. I have talked about this in SqlDependency based caching of LINQ Queries.

您可以做的是将SqlChangeMonitor与简单的SqlCommand对象结合使用，这些对象在表上可能会更改为简单的SELECT ... FROM Table.您需要了解，设置通知的成本和轮询的成本之间存在平衡，如果您的表频繁更改，则监视更改可能比轮询更昂贵.请参阅这篇文章神秘通知，以了解QN的工作原理和作用监控成本.

What you can do is use SqlChangeMonitor with straightforward SqlCommand objects constructed as simple SELECT ... FROM Table on your tables that are likely to change. You need to understand that there is a balance between the cost of setting up notifications and the cost of polling, if your tables change frequently then monitoring for changes could turn out to be more expensive than polling. See this article The Mysterious Notification to understand how QN works and what is the cost of monitoring.

这篇关于SQL缓存和实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！