我还应该在SQL Profiler中看到查询命中吗? [英] Should I still see the query hit in SQL Profiler?

问题描述

我目前正在建立一个网站，并且我只是像这样使用LinqToSQL实现了SqlCacheDependency.

I am currently building a web site and I just implemented SqlCacheDependency using LinqToSQL like so.

   public IQueryable<VictoryList> GetVictoryList()
   {
                string cacheKey = HttpContext.Current.User.Identity.Name + "victoryCacheKey";
                IQueryable<VictoryList> cachednews = (IQueryable<VictoryList>)HttpContext.Current.Cache.Get(cacheKey);

                if (cachednews == null)
                {

                    var results = from v in _datacontext.ViewVictoryLists
                                  orderby _datacontext.GetNewId()
                                  select new VictoryList
                                  {
                                      MemberID = v.MemberID,
                                      Username = v.Aspnetusername,
                                      Location = v.Location,
                                      DaimokuGoal = v.DaimokuGoal,
                                      PreviewImageID = v.PreviewImageID,
                                      TotalDaimoku = v.TotalDaimoku,
                                      TotalDeterminations = v.TotalDeterminations,
                                      DeterminationID = v.DeterminationID,
                                      DeterminationName = v.DeterminationName
                                  };
                    SqlCacheDependency dependency =
                     new SqlCacheDependency(_datacontext.GetCommand(results) as SqlCommand);

                    HttpContext.Current.Cache.Add(cacheKey, results, dependency, DateTime.MaxValue,
                                  TimeSpan.Zero, CacheItemPriority.Normal, null); 

                    return results.ToList().AsQueryable();
                }
                return cachednews;
   }

它似乎工作得特别快，特别是在某些复杂的查询上，但是，在查看SQLProfiler中的内容时，我仍然看到查询正在运行，我使用的是SqlCacheDependency的CommandBroker模式.即使数据显然来自缓存的对象，我仍然应该看到查询吗?

It appears to be working as things are noticbly faster especially on some complex queries, however while looking at things in SQLProfiler I still see the query run through, I'm using the CommandBroker mode of SqlCacheDependency. Should I still see the query even though the data is obviously coming from a cached object?

推荐答案

我认为问题在于您正在将IQueryable存储在缓存中，然后cachednews包含一个命中数据库的IQueryable.

I think that the problem is that you are storing IQueryable's in your cache, and then cachednews contains an IQueryable that hits the database.

尝试以下更改.

public IQueryable<VictoryList> GetVictoryList() {
            // ...
            if (cachednews == null)
            {

                var results = from // ...
                results = results.ToList().AsQueryable(); // force query execution

                SqlCacheDependency dependency = // ...;
                HttpContext.Current.Cache.Add(cacheKey, 
                    results, // now just the result are stored
                    dependency, 
                    DateTime.MaxValue,
                    TimeSpan.Zero, 
                    CacheItemPriority.Normal, 
                    null); 

                return results;
            }
            return cachednews;
}

这篇关于我还应该在SQL Profiler中看到查询命中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！