CODEWITHSUNDEEP
c#azureazure-sql-databaseazure-web-app-serviceazure-sql
Ogglas
Ogglas

Reputation: 70224

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

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

enter image description here

Azure Standard database with 50 DTUs (S2).

enter image description here

Localhost request takes 170 ms

enter image description here

App Service request takes 14485 ms

enter image description here

Database call localhost:

enter image description here

Database call Azure Database:

enter image description here

Upvotes: 2

Views: 698

Answers (2)

Ogglas
Ogglas

Reputation: 70224

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.

enter image description here

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; }
}

enter image description here

Upvotes: 1

Mike Ubezzi
Mike Ubezzi

Reputation: 1027

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

Upvotes: 0

Related Questions