The first request is slow and the pre-created views do not fall (possibly)

Question

I have problems with the time when EF pulls some objects. The entity in question has a boat of details that live in 1 table, but also has several ICollection that relate to other tables. I gave up the idea of ​​loading an entire graph of objects, since that is too much data, and instead, my Silverlight client will send a new request to my WCF service as needed.

After you lose weight to 1 tablespoon thing, it takes about 8 seconds to pull out the data, and then another 1 second to .ToList () (I expect it to be <1 second). I use the stopwatch class to measure. When I run the SQL query in the SQL management studio, it only takes a fraction of a second, so I'm sure the SQL query itself is not a problem.

This is how I try to request my data:

public List<ComputerEntity> 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 => x.CompanyEntity.Name == client); // .AsNoTracking() has no impact on performance //.Include(x => x.CompanyEntity) //.Include(x => x.NetworkAdapterEntities) // <-- using these 4 includes has no impact on SQL performance, but faster to make lists without these //.Include(x => 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 => x.WSUSSoftwareEntities) //var entities = Find(x => x.CompanyEntity.Name == client); // <-- 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; } 

I assume that using .Include means impatient loading, although in my current case this does not matter, since I just need 1 tablespoon of material. The SQL generated by this statement (which runs super fast in SSMS):

 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 

Basically it is to select all the columns in this table, join the second table with the company name and filter using the where clause for file_name == for the method. The special company I pull out returns only 75 records.

Disabling object tracking using .AsNoTracking () has zero effect on runtime.

I also gave the Find a go method and had the exact runtime. The next thing I tried was to recreate the views if the problem was there. I use the code first, so I used the EF power tools for this.

This long period of time to run this request causes too much delay for my users. When I write SQL code and not touch EF, it is very fast. Any ideas on what I am missing?

It may also be related or not, but since I am doing this in WCF, which is stateless, I assume that absolutely nothing is cached? The way I think about this is that every new call launches this WCF service library for the first time, so there is no existing cache. Is this an accurate guess?

Update 1
So I ran this query twice in the same unit test to check the cold / warm request. The first request is terrible, as expected, but the second is lightning fast, in just 350 ms. Since WCF has no status, will every call to my WCF service be treated as this first ugly slow request? Still need to figure out how to get this first request, so as not to suck.
Update 2
Do you know the preliminary ideas that I mentioned earlier? Well ... I don’t think they were hit. I set a few breakpoints in the ReportingDbContext.Views.cs file with auto-generated EF-powertools, and they never hit. This combined with the cold / warm requests that I see, it sounds as if it could be meaningful. Is there any specific way I need to preview the views using the EF power tools in the first code environment?

Answer 1

Received! The main problem was the whole cold call. How to get around this cold request problem? By making a request. This will “warm up” the EntityFramework so that subsequent compilation of requests is much faster. My pre-created views didn’t help anything with the query that I compiled on this question, but they seem to work if I want to dump the entire table into an array (which is bad). Since I use WCF, which is stateless, will I have to “warm up” EF for every call? Nope! Since EF lives in the application domain and not in context, I just need to warm up when initializing the service. For dev purposes I myself accept, but in production he lives in IIS.

To make the request warm, I made a service behavior that takes care of this for me. Create your own behavior class as such:

 using System; using System.Collections.ObjectModel; using System.ServiceModel; using System.ServiceModel.Channels; // for those without resharper, here are the "usings" using System.ServiceModel.Description; public class InitializationBehavior : Attribute, IServiceBehavior { public InitializationBehavior() { } public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase) { } public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters) { Bootstrapper.WarmUpEF(); } public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase) { } } 

Then I used this to warm up:

  public static class Bootstrapper { public static int initialized = 0; public static void WarmUpEF() { using (var context = new ReportingDbContext()) { context.Database.Initialize(false); } initialized = 9999; // I'll explain this } } 

This SO question helped with the warm-up code: How to initialize Entity Framework queries to speed them up?

Then you remove this behavior in your WCF service as follows:

  [InitializationBehavior] public class InventoryService : IInventoryService { // implement your service } 

I started the project of my services in debug mode, which, in turn, activated the initialization behavior. After spamming the method that makes the request referenced in my question, my breakpoint in the behavior was not deleted (except for the hit when I first posted it). I checked what it was by checking a static initialized variable. Then I posted this bad boy to IIS with my int check, and it had the same behavior.

So, in short, if you are using Entity Framework 5 with a WCF service and don’t want a crappy first request, heat it up using the service behavior. There are probably other / better ways to do this, but this method works too!

Editing:
If you are using NUnit and want to warm up EF for your unit tests, set up your test as such:

 [TestFixture] public class InventoryTests { [SetUp] public void Init() { // warm up EF. using (var context = new ReportingDbContext()) { context.Database.Initialize(false); } // init other stuff } // tests go here }