Either my ASP.NET/API or my EF6 SQL Database is super slow, either on Azure or my machine

Question

I am coming today to talk about server, my slow server side.. The fact is, my server side is getting slower, day after day, week after week and I don't get why... I wrote this huge post in order to put you in my current context/situation. It's a long read and I will try to get you into it in a theoretical way as much as I can because of the confidentiality of the project.

Thanks for the time you will spend in order to help me :)

---

My SQL Server plan has the following information:

• Used space 52 MB
• Allocated space 64 MB
• Max size 250 GB
• Pricing tier: Standard S0: 10 DTUs

My AppPlan has the following details as well:

• App Service plan/pricing tier : Standard: 1 Small
  • 1x cores
  • 100 total ACU
  • 1.75 GB memory
  • A-Series compute

My development environnement is the following:

• MacOS (MID 2015) (i7 2,8 GHz, 16 GB 1600 MHz DDR3, High Sierra)
• Win10 (Parallel Desktop 13, 8GB allocated)

Specs & Configs of the project:

• ASP.NET (.NET Framework 4.6.1)
• Entity Framework 6.2.0 (Code-First)

---

In this database, there is static data and dynamics data. The thing is that, my database, even if the first call is really slow (not warmed up), the calls after will be long still.

I took the following situation in consideration:

We have 12 people living in 9 different houses. These people have relations and we make a calcul (not inside of the database) which calcul a certain score that relate to the importance/weight of their relations between each others (the conversation amount, the link they have by their connection, etc).

So in this case, the database only has to retrieve all the connections (max 11) of an user by its relation. Once found, we then request a second time the database to get the house of each given user (including the address of it, the details of the house). Then, we make a C# calcul based on the data we've got to rerank the users by placing for the most important ones.

However, even if with time this calculus can be heavy, it already takes between 0.8s and 2.5s (and sometimes it takes 15s or 30s...) and it's really light...

So I wanted to try on my machine (The VM) and it's almost the same. However, let say that we add to our situation the fact that an user can change its details and edit its house. Well the performances decreases so bad... Even a small edit will take 0.8 to 1.5.. From it, I began to think that, any call to the API is slow..

However, I also realised that, the concurrency is also a big issue. If our users, altogether edit one of their information (firstname) and at the same time, one call the API to get its relations with the others users, then everything is slowed down so bad.. I don't get why since it's only one cell in the database that gets edited. Also, the other one is a read only (getRelations), so I added AsNoTracking() to my LinQ which didn't change nothing..

Why did I looked at the database first is because, from Azure, when I get into the 'profiler' section, I can see that my database has many BLOCKED_TIME. Also, when concurrency happens, I can see WAIT_TIME, why the hell is the CPU is blocked or is waiting for something? Since I have no external needs, I searched on internet and find this complet answer:

https://stackoverflow.com/a/46553440/6093604

---

Answer of @dstj for What does AWAIT_TIME exactly mean in the Azure profiler?

From Azure's documentation:

Waiting (AWAIT_TIME)

AWAIT_TIME indicates the code is waiting for another task to complete. This typically happens with C# 'await' statement. When the code does a C# 'await', the thread unwinds and returns control to the thread-pool, and there is no thread that is blocked waiting for the 'await' to finish. However, logically the thread that did the await is 'blocked' waiting for the operation to complete. The AWAIT_TIME indicates the blocked time waiting for the task to complete.+

Blocked Time

BLOCKED_TIME indicates the code is waiting for another resource to be available, such as waiting for a synchronization object, waiting for a thread to be available, or waiting for a request to finish.

---

So it's waiting on something necessary to continue with processing. We have had the same problem of long AWAIT_TIME with file uploads and it turned out the request was waiting for the Request's stream to be read (ReadAsMultiPartAsync() for us)... If you look at the code in RecASPRequest and _RtlUserThreadStart, you'll probably the culprit...

---

"I query my database and something isn't available"... I'm sorry, what? There is a little screenshot I can provide tho:

Because I couldn't find anything about it, I kept searching on the web some information about Entity Framework performances and I found this blog : https://www.red-gate.com/simple-talk/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/

This post is really interesting tho, I invite you to read it :) From this post, I noticed some important points:

• Multiple result sets

  Entity Framework supports Multiple Result Sets, which allows it to make and receive multiple requests to SQL Server over a single connection, reducing the number of roundtrips. This is particularly useful if there’s high latency between your application server and the database. Just make sure your connection string contains:

  MultipleActiveResultSets=True;

  However, if it does really change something, I don't fully understand what does it really change? In addition, even if it's set as false on my Azure side, it's set by default as trueon my VM. Would it be the source of my problem? The fact the database cannot be accessed by multiple instance at the same time? Isn't possible/achievable with SQL (and/or EF6)?

• Change tracking

  When you retrieve entities from the database, it’s possible that you will modify those objects and expect to be able to write them back to the database. Because Entity Framework doesn’t know your intentions, it has to assume that you will make modifications, so must set these objects up to track any changes you make. That adds extra overhead, and also significantly increases memory requirements. This is particularly problematic when retrieving larger data sets.

  If you know you only want to read data from a database (for example in an MVC Controller which is just fetching data to pass to a View) you can explicitly tell Entity Framework not to do this tracking:

  string city = "New York";
      List<School> schools = db.Schools
          .AsNoTracking()
          .Where(s => s.City == city)
          .Take(100)
          .ToList();
  

  As I said above, I already tried that, but it doesn't change anything at the moment in term of performance. Maybe it would change something when I will get way more data, but at the moment, it doesn't seems that the tracking is the the base of my slow performances issue.

• Missing Indexes

  If I fully understood this part, I don't think it would have any link with my problem because indexing would have a real impact only if I had a lot of data, but not in the case of only 12 users...

  We might want to find all Pupils who live in New York. Easy:

  string city = "New York";
  var pupils = db.Pupils
      .Where(p => p.City == city)
      .OrderBy(p => p.LastName)
      .Select(x => new { x.FirstName, x.LastName })
      .ToList();
  

• Overly-generic queries

  Even if I don't think the following is linked to my issue, I don't get why the request seems so long with entity, because I have a similar behavior as the following one:

  Often we want to do a search that is based on several criteria. For example, we might have a set of four search boxes for a user to complete, where empty boxes are ignored, so write something like:

  //Search data as input by user
  var searchModel = new Pupil
  {
      FirstName = "Ben",
      LastName = null,
      City = null,
      PostalZipCode = null
  };
  
  List<Pupil> pupils = db.Pupils.Where(p => searchModel.FirstName)
      && (searchModel.LastName == null || p.LastName == searchModel.LastName)
      && (searchModel.City == null || p.LastName == searchModel.City)
      && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode)
      )
  .Take(100)
  .ToList();
  

  It’s tempting to hope that the LastName, City, and PostalZipCode clauses, which all evaluate to true because in this case they are null, will be optimized away in .NET, leaving a query along the lines of …

  DECLARE @p__linq__0 NVARCHAR(20) = 'Ben'
  SELECT TOP 100
  PupilId ,
  FirstName ,
  LastName,
  etc...
  FROM dbo.Pupils
  WHERE FirstName = @p__linq__0
  

  We’ll be disappointed – this isn’t how EF query generation works. If we inspect the actual query executed, it looks like this:

  -- Generated by ANTS Performance Profiler
  -- Executed against .\SQL2014
  USE [EFSchoolSystem]
  DECLARE @p__linq__0 NVarChar(4000) SET @p__linq__0 = 'Ben'
  DECLARE @p__linq__1 NVarChar(4000) SET @p__linq__1 = 'Ben'
  DECLARE @p__linq__2 NVarChar(4000) SET @p__linq__2 = ''
  DECLARE @p__linq__3 NVarChar(4000) SET @p__linq__3 = ''
  DECLARE @p__linq__4 NVarChar(4000) SET @p__linq__4 = ''
  DECLARE @p__linq__5 NVarChar(4000) SET @p__linq__5 = ''
  DECLARE @p__linq__6 NVarChar(4000) SET @p__linq__6 = ''
  DECLARE @p__linq__7 NVarChar(4000) SET @p__linq__7 = ''
  -- Executed query
  SELECT TOP (100)
  [Extent1].[PupilId] AS [PupilId] ,
  [Extent1].[FirstName] AS [FirstName] ,
  [Extent1].[LastName] AS [LastName] ,
  [Extent1].[Address1] AS [Address1] ,
  [Extent1].[Adderss2] AS [Adderss2] ,
  [Extent1].[PostalZipCode] AS [PostalZipCode] ,
  [Extent1].[City] AS [City] ,
  [Extent1].[PhoneNumber] AS [PhoneNumber] ,
  [Extent1].[SchoolId] AS [SchoolId] ,
  [Extent1].[Picture] AS [Picture]
  FROM [dbo].[Pupils] AS [Extent1]
  WHERE (@p__linq__0 IS NULL OR [Extent1].[FirstName] = @p__linq__1)
  AND (@p__linq__2 IS NULL OR [Extent1].[LastName] = @p__linq__3)
  AND (@p__linq__4 IS NULL OR [Extent1].[LastName] = @p__linq__5)
  AND (@p__linq__6 IS NULL OR [Extent1].[PostalZipCode] = @p__linq__7)
  

  For any LINQ statement, a single SQL query is generated, and everything is handled by SQL Server. This query itself looks pretty messy, but since that’s hidden from you, why should it matter? After all, the query runs quickly. (see more from the blog saved as pdf)

  Based on the explanation above, I tried to split my request with multiple Where instead of just one with different condition and the result appears to be the same.. So I then felt in the next part, the missmatched data types.

• Mismatched data types

  Data types matter, and if not enough attention is paid to them, even disarmingly simple database queries can perform surprisingly poorly. Let’s take a look at an example that will demonstrate why. We want to search for Pupils with zip code 90210. Easy:

  string zipCode = "90210";
      var pupils = db.Pupils
      .Where(p => p.PostalZipCode == zipCode)
      .Select(x => new {x.FirstName, x.LastName})
      .ToList();
  

  Unfortunately it takes a very long time for the results to come back from the database. There are several million rows in the Pupils table, but there’s an index covering the PostalZipCode column which we’re searching against, so it should be quick to find the appropriate rows. Indeed the results are returned instantly if we directly query the database from SQL Server Management Studio using

  SELECT FirstName, LastName FROM Pupils p WHERE p.PostalZipCode = ‘90210’

  Let’s look at what the application’s doing.

  Type conversion: Seek Plan for CONVERT_IMPLICIT(nvarchar(20), [Extent1].[PostalZipCode],0)=[@p__linq__0]

  So [Extent1].[PostalZipCode] was implicitly converted to NVARCHAR(20). If we look back at the complete query which was run we can see why. Entity Framework has declared the variable as NVARCHAR, which seems sensible as strings in .NET are Unicode, and NVARCHAR is the SQL Server type which can represent Unicode strings.

  But looking at the Pupils table we can see that the PostalZipCode column is VARCHAR(20). Why is this a problem? Unfortunately, VARCHAR has a lower Data Type Precedence than NVARCHAR. That means that converting the wide NVARCHAR data type to the narrower VARCHAR can’t be done implicitly because it could result in data loss (as NVARCHAR can represent characters which VARCHAR can’t). So to compare the @p__linq_0 NVARCHAR parameter to the VARCHARcolumn in the table, SQL Server must convert every row in the index from VARCHAR to NVARCHAR. Thus it is having to scan the entire index.

  Once you’ve tracked this down, it’s easy to fix. You just need to edit the model to explicitly tell Entity Framework to use VARCHAR, using column annotation.

  public string Adderss2 { get; set; }
  [Column(TypeName = "varchar")]
  public string PostalZipCode { get; set; }
  

  I tried two things after this paragraph, first, the age of my user has a role when you wanna retrieve their connection. However, if a problem happens on the app side, it might happens that a user doesn't have a age yet, so I use a int? but, in my WHERE's clause, I compare it to a int value.. Also, I don't force my stringvars to be varchar or nvarchar explicitly, so I began to do it, I changed everything and, I now compare int?.Value to my int inside of my WHERE's clause.

  But.... Same amount of time...

---

Based on that, I began to think that maybe my database isn't the problem at the moment, even if my SQL server's is reaching 100% for even some easy actions and the actions seems to be paused if any concurrency happens..

So I checked what would be the issue on the server side and I don't know where to begin to be honest.. I already spent almost a week trying to understand everything (because I'm not the one that create the app from scratch). I saw some stuff about the Context (to have one by API request), the dependency injection, but I don't feel that it would be the problem, even if I am not sure...

Do anyone would have any idea in what direction I should look at? Do anyone knows where would I be able to find some data about what is causing such a slow behavior? Either from VS 2017 Enterprise of Azure Portal

I hope I put enough of details, maybe it's too much, I don't know, I am just trying to put you as much as I can in the current context/situation

I can put more details if you need, so feel free to ask for it, thank you so much for any help !

Best,