title: First query is slow and pre-generated views aren't being hit (probably)
body: <p>I'm having a bit of trouble with the time it takes EF to pull some entities. The entity in question has a boatload of props that live in 1 table, but it also has a handful of ICollection's that relate to other tables. I've abandoned the idea of loading the entire object graph as it's way too much data and instead will have my Silverlight client send out a new request to my WCF service as details are needed.</p>
  
  <p>After slimming down to 1 table's worth of stuff, it's taking roughly 8 seconds to pull the data, then another 1 second to .ToList() it up (I expect this to be &lt; 1 second). I'm using the stopwatch class to take measurements. When I run the SQL query in SQL management studio, it takes only a fraction of a second so I'm pretty sure the SQL statement itself isn't the problem. </p>
  
  <p>Here is how I am trying to query my data:</p>
  
  <pre><code>public List&lt;ComputerEntity&gt; FindClientHardware(string client)
  {
          long time1 = 0;
          long time2 = 0;
          var stopwatch = System.Diagnostics.Stopwatch.StartNew();
  
          // query construction always takes about 8 seconds, give or a take a few ms.
          var entities =
              DbSet.Where(x =&gt; x.CompanyEntity.Name == client); // .AsNoTracking() has no impact on performance
          //.Include(x =&gt; x.CompanyEntity)
          //.Include(x =&gt; x.NetworkAdapterEntities) // &lt;-- using these 4 includes has no impact on SQL performance, but faster to make lists without these
          //.Include(x =&gt; x.PrinterEntities)        // I've also abandoned the idea of using these as I don't want the entire object graph (although it would be nice)
          //.Include(x =&gt; x.WSUSSoftwareEntities)
  
          //var entities = Find(x =&gt; x.CompanyEntity.Name == client); // &lt;-- another test, no impact on performance, same execution time
  
          stopwatch.Stop();
          time1 = stopwatch.ElapsedMilliseconds;
          stopwatch.Restart();
  
          var listify = entities.ToList(); // 1 second with the 1 table, over 5 seconds if I use all the includes. 
  
          stopwatch.Stop();
          time2 = stopwatch.ElapsedMilliseconds;
  
          var showmethesql = entities.ToString();
  
          return listify;
      }
  </code></pre>
  
  <p>I'm assuming that using the .Include means eager loading, although it isn't relevant in my current case as I just want the 1 table's worth of stuff. The SQL generated by this statement (which executes super fast in SSMS) is:</p>
  
  <pre><code>SELECT 
    [Extent1].[AssetID] AS [AssetID],  
    [Extent1].[ClientID] AS [ClientID],  
    [Extent1].[Hostname] AS [Hostname],  
    [Extent1].[ServiceTag] AS [ServiceTag],  
    [Extent1].[Manufacturer] AS [Manufacturer],  
    [Extent1].[Model] AS [Model],  
    [Extent1].[OperatingSystem] AS [OperatingSystem],  
    [Extent1].[OperatingSystemBits] AS [OperatingSystemBits],  
    [Extent1].[OperatingSystemServicePack] AS [OperatingSystemServicePack],  
    [Extent1].[CurrentUser] AS [CurrentUser],  
    [Extent1].[DomainRole] AS [DomainRole],  
    [Extent1].[Processor] AS [Processor],  
    [Extent1].[Memory] AS [Memory],  
    [Extent1].[Video] AS [Video],  
    [Extent1].[IsLaptop] AS [IsLaptop],  
    [Extent1].[SubnetMask] AS [SubnetMask],  
    [Extent1].[WINSserver] AS [WINSserver],  
    [Extent1].[MACaddress] AS [MACaddress],  
    [Extent1].[DNSservers] AS [DNSservers],  
    [Extent1].[FirstSeen] AS [FirstSeen],  
    [Extent1].[IPv4] AS [IPv4],  
    [Extent1].[IPv6] AS [IPv6],  
    [Extent1].[PrimaryUser] AS [PrimaryUser],  
    [Extent1].[Domain] AS [Domain],  
    [Extent1].[CheckinTime] AS [CheckinTime],  
    [Extent1].[ActiveComputer] AS [ActiveComputer],  
    [Extent1].[NetworkAdapterDescription] AS [NetworkAdapterDescription],  
    [Extent1].[DHCP] AS [DHCP] 
  FROM  
    [dbo].[Inventory_Base] AS [Extent1] 
    INNER JOIN [dbo].[Entity_Company] AS [Extent2] 
      ON [Extent1].[ClientID] = [Extent2].[ClientID] 
  WHERE 
    [Extent2].[CompanyName] = @p__linq__0
  </code></pre>
  
  <p>Which is basically a select all columns in this table, join a second table that has a company name, and filter with a where clause of companyname == input value to the method. The particular company I'm pulling only returns 75 records.</p>
  
  <p>Disabling object tracking with .AsNoTracking() has zero impact on execution time.</p>
  
  <p>I also gave the Find method a go, and it had the exact same execution time. The next thing I tried was to pregenerate the views in case the issue was there. I am using code first, so I used the EF power tools to do this.</p>
  
  <p>This long period of time to run this query causes too long of a delay for my users. When I hand write the SQL code and don't touch EF, it is super quick. Any ideas as to what I'm missing?</p>
  
  <p>Also, maybe related or not, but since I'm doing this in WCF which is stateless I assume absolutely nothing gets cached? The way I think about it is that every new call is a firing up this WCF service library for the first time, therefore there is no pre-existing cache. Is this an accurate assumption?</p>
  
  <p><strong>Update 1</strong><br>
  So I ran this query twice within the same unit test to check out the cold/warm query thing. The first query is horrible as expected, but the 2nd one is lightning fast clocking in at 350ms for the whole thing. Since WCF is stateless, is every single call to my WCF service going to be treated as this first ugly-slow query? Still need to figure out how to get this first query to not suck.
  <br>
  <strong>Update 2</strong><br>
  You know those pre-generated views I mentioned earlier? Well... I don't think they are being hit. I put a few breakpoints in the autogenerated-by-EF-powertools ReportingDbContext.Views.cs file, and they never get hit. This coupled with the cold/warm query performance I see, this sounds like it could be meaningful. Is there a particular way I need to pregenerate views with the EF power tools in a code first environment? </p>