title: Entity Framework query caching
body: <p>This MSDN article lists a whole bunch of ways to improve your Entity Framework performance:</p>
  
  <p><a href="https://msdn.microsoft.com/en-us/data/hh949853.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/data/hh949853.aspx</a></p>
  
  <p>One of its suggestions (4.3) is to convert the properties of a non-mapped object into a local variable, so that EF can cache its internal query plan.</p>
  
  <p>Sounds like a great idea. So I put it to the test with a simple query that compared the performance over 10,000 iterations of an indirect property  reference in a query to a local variable. Like so:</p>
  
  <pre><code>[Fact]
  public void TestQueryCaching()
  {
      const int iterations = 1000;
  
      var quote = new Quote();
      using (var ctx = new CoreContext())
      {
          quote.QuoteId = ctx.Quotes.First().Id;
      }
  
      double indirect = 0;
      double direct = 0;
  
      10.Times(it =&gt;
      {
          indirect += PerformCoreDbTest(iterations, "IndirectValue", (ctx, i) =&gt;
             {
                 var dbQuote = ctx.Quotes.First(x =&gt; x.Id == quote.QuoteId);
             }).TotalSeconds;
          direct += PerformCoreDbTest(iterations, "DirectValue", (ctx, i) =&gt;
              {
                  var quoteId = quote.QuoteId;
                  var dbQuote = ctx.Quotes.First(x =&gt; x.Id == quoteId);
              }).TotalSeconds;
      });
  
      _logger.Debug($"Indirect seconds: {indirect:0.00}, direct seconds:{direct:0.00}");
  }
  
  protected TimeSpan PerformCoreDbTest(int iterations, string descriptor, Action&lt;ICoreContext, int&gt; testAction)
  {
      var sw = new Stopwatch();
      sw.Start();
      for (var i = 0; i &lt; iterations; i++)
      {
          using (var ctx = new CoreContext())
          {
              testAction(ctx, i);
          }
      }
      sw.Stop();
      _logger.DebugFormat("{0}: Took {1} milliseconds for {2} iterations",
          descriptor, sw.Elapsed.TotalMilliseconds, iterations);
      return sw.Elapsed;
  }
  </code></pre>
  
  <p>But I'm not seeing any real performance benefit. On two different machines, these are the results over 5 iterations:</p>
  
  <pre><code>Machine1 - Indirect seconds: 9.06, direct seconds:9.36
  Machine1 - Indirect seconds: 9.98, direct seconds:9.84
  Machine2 - Indirect seconds: 22.41, direct seconds:20.38
  Machine2 - Indirect seconds: 17.27, direct seconds:16.93
  Machine2 - Indirect seconds: 16.35, direct seconds:16.32
  </code></pre>
  
  <p>Using a local variable - the "direct" approach that the MSDN article recommends - is maybe the tiniest bit faster (4/5 times), but not consistently, and not really by much.</p>
  
  <p>Am I doing something wrong in my testing? Or is the effect really so slight that it doesn't make much difference? Or is the MSDN article basically wrong, and that way of referring to objects doesn't make any difference to query caching?</p>
  
  <p>** Edits 10/9/16 **
  I modified the query to (a) make it more complex, and (b) to pass in a different quoteId each time. I suspect the latter is important, as otherwise the query does in fact get cached - since there aren't any parameters. See the answer from @raderick below.</p>
  
  <p>Here's the more complex test:</p>
  
  <pre><code>[Fact]
  public void TestQueryCaching()
  {
      const int iterations = 1000;
  
      List&lt;EFQuote&gt; quotes;
      using (var ctx = new CoreContext())
      {
          quotes = ctx.Quotes.Take(iterations).ToList();
      }
  
      double indirect = 0;
      double direct = 0;
      double iqueryable = 0;
  
      10.Times(it =&gt;
      {
          indirect += PerformCoreDbTest(iterations, "IndirectValue", (ctx, i) =&gt;
          {
              var quote = quotes[i];
              var dbQuote = ctx.Quotes
               .Include(x =&gt; x.QuoteGroup.QuoteGroupElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
               .Include(x =&gt; x.QuoteElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
               .Include(x =&gt; x.QuotePackage)
               .Include(x =&gt; x.QuoteDefinition)
               .Include(x =&gt; x.QuoteLines)
               .First(x =&gt; x.Id == quote.Id);
          }).TotalSeconds;
          direct += PerformCoreDbTest(iterations, "DirectValue", (ctx, i) =&gt;
          {
              var quoteId = quotes[i].Id;
              var dbQuote = ctx.Quotes
                  .Include(x =&gt; x.QuoteGroup.QuoteGroupElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
                  .Include(x =&gt; x.QuoteElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
                  .Include(x =&gt; x.QuotePackage)
                  .Include(x =&gt; x.QuoteDefinition)
                  .Include(x =&gt; x.QuoteLines)
                  .First(x =&gt; x.Id == quoteId);
          }).TotalSeconds;
          iqueryable += PerformCoreDbTest(iterations, "IQueryable", (ctx, i) =&gt;
          {
              var quoteId = quotes[i].Id;
              var dbQuote = ctx.Quotes
                      .Include(x =&gt; x.QuoteGroup.QuoteGroupElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
                      .Include(x =&gt; x.QuoteElements.Select(e =&gt; e.DefaultElement.DefaultChoices))
                      .Include(x =&gt; x.QuotePackage)
                      .Include(x =&gt; x.QuoteDefinition)
                      .Include(x =&gt; x.QuoteLines)
                      .Where(x =&gt; x.Id == quoteId).First();
          }).TotalSeconds;
      });
  
      _logger.Debug($"Indirect seconds: {indirect:0.00}, direct seconds:{direct:0.00}, iqueryable seconds:{iqueryable:0.00}");
  }
  </code></pre>
  
  <p>And the results (over 10,000 total iterations) are much more like what the MSDN article above describes:</p>
  
  <p><code>Indirect seconds: 141.32, direct seconds:91.95, iqueryable seconds:93.96</code></p>