Slow API call Azure App Service and Azure Standard database 50 (S2) with Entity Framework

Question

I'm hosting an app on Azure for testing purposes. However a lot of API calls are getting really slow even though performance does not seem to be maxed out at all. One API call that takes 170 ms locally with IIS Express and SQL Server Express takes a whooping 14485 ms on Azure. The testdata is exactly the same. There are a lot of includes going on but the data is needed and the query is even slower if the includes are not there.

Why is the query/API Call so much slower on Azure? I could understand it if performance was peeking but not a single parameter hits above 60%.

Code:

var results = db.ElectoralDistrictResults
    .AsNoTracking()
    .Where(x => x.ElectoralDistrict.Code == addressViewModel.ElectoralDistrictCode)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.CountyResult.Election.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.CountyResult.Election.ElectionTurnout)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.CountyResult.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.CountyResult.ElectionTurnout)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.CountyResult.County)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ConstituencyResult.ElectionTurnout)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.ElectionTurnout)
    .Include(x => x.MunicipalityElectoralDistrictResult.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.ElectionTurnout)
    .Include(x => x.ElectionTurnout)
    .Include(x => x.Votes.Select(y => y.Party))
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityResult.Municipality.County)
    .Include(x => x.MunicipalityElectoralDistrictResult.MunicipalityElectoralDistrict)
    .Include(x => x.ElectoralDistrict)
    .ToList();

Performance:

App Service:

Basic: 1 Medium
B2
2x cores
200 total ACU
3.5 GB memory
A-Series compute

Azure Standard database with 50 DTUs (S2).

Localhost request takes 170 ms

App Service request takes 14485 ms

Database call localhost:

Database call Azure Database:

Have you looked at the execution plans? are tables indexed? in general Azure SQL db offers moderate to low performance unless you pay through the nose, for anything but PK selects (i found) — zaitsman, Sep 09 '18 at 13:48
@zaitsman Table are indexed and it runs really well on SQL Server Express and IIS Express but extremely slow on Azure. — Ogglas, Sep 09 '18 at 13:59
Try profiling your application https://learn.microsoft.com/en-us/azure/cloud-services/cloud-services-performance-testing-visual-studio-profiler — Alberto Morillo, Sep 09 '18 at 19:37
More remote profiling https://blog.maartenballiauw.be/post/2013/03/13/remote-profiling-windows-azure-cloud-services-with-dottrace.html — Alberto Morillo, Sep 09 '18 at 19:37

Ogglas · Accepted Answer · 2018-11-17T22:37:22.473

Update:

TL;DR: Using indices for slow queries can fix the problem.

http://capesean.co.za/fixing-slow-performance-with-azure-sql-database/

How to create spatial index using EF 6.1:

https://stackoverflow.com/a/36460716/3850405

Original:

After spending a substantial amount of time with this it seems to have something to do with the DbGeography class. Saved with data type geography in Azure Sql Server and on SQL Server Express. When excluding this property the query was performing only a fraction slower than locally. I don't think the data type is that normal. Will ask Azure if they have a problem handling this data type.

public class Municipality
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Code { get; set; }

    public string Name { get; set; }

    public DbGeography Area { get; set; }

    [ForeignKey("County")]
    public string CountyCode { get; set; }

    public virtual County County { get; set; }
}

I have reached out to the Azure SQL Database team for more information. — Mike Ubezzi, Sep 26 '18 at 16:15

score 0 · Answer 2 · answered Sep 27 '18 at 15:25

One of the reasons for this slowdown could be due to bandwidth limits (if every declared column is being fetched), as spatial data blobs can be quite big, and if they are fetching multiple rows, things can pile up if their app is not collocated with DB. They can check the size of this data by doing something like the below on top of the returned rows:

SUM((2 + points.HasZ + points.HasM)*8*points.STNumPoints()) as [Approx Bytes]

There could be specific instances where Entity Framework/Entity Framework Core might handle this data type differently but, the answer provided here focuses on the Geography datatype itself, and the potential amount of data it might include and the performance impact this will have on an application.

Additional information: STNumPoints (geography Data Type) Applies to: SQL Server (starting with 2008), Azure SQL Database

Slow API call Azure App Service and Azure Standard database 50 (S2) with Entity Framework

2 Answers2