We have built a web api with .NET Core 3.1, Dapper and Azure SQL which is hosted on an Azure web app. Using loader.io we ran some performance tests, which were disappointing (~10 RPS) for an S1 instance. Each API call triggers a couple of SQL queries using Dapper to retrieve the data from an Azure SQL database (100 DTU).
The Application Insights profiler has made traces, which says that a lot of time is spent in the database:
Travelling the hot path:
The database itself doesn't appear stressed (DTU or other metrics wise):
The web app has some peaks on CPU, the threads look OK:
The code is injected using standard .NET Core IServiceCollection DI and Scrutor:
// Injection of all SqlServer repositories.
services.Scan(scan => scan
.FromAssemblyOf<SomeRepository>()
.AddClasses(classes => classes.Where(type => type.Name.EndsWith("Repository")))
.AsImplementedInterfaces()
.WithTransientLifetime());
Dapper is used in the SQL Server repositories to retrieve the information:
public async Task<IReadOnlyCollection<DocumentRevisionOverview>> GetDocumentRevisionsAsync()
{
Logger.LogInformation("Starting {Method} on {Class}.", nameof(GetDocumentRevisionsAsync), ClassName);
var sql = "select statement";
// databaseParameters.ConnectionString (type is string) is injected via the constructor.
using (var connection = new SqlConnection(databaseParameters.ConnectionString))
{
var documentRevisions = await connection.QueryAsync<DocumentRevisionOverview>(sql);
Logger.LogInformation("Finished {Method} on {Class}.", nameof(GetDocumentRevisionsAsync), ClassName);
return documentRevisions.ToList();
}
}
We don't have varchars in our database tables, so the varchar to nvarchar conversion is not applicable.
- All resources are located in the same resource group and location.
- The tables contain only a couple of hundred records.
- The JSON that is returned from the web API is 88 KB.
- Dapper should re-use the ADO.NET connection pooling, which is based on the connection string which doesn't vary.
- The connectionstring format is
"Server={server};Initial Catalog={database};Persist Security Info=False;User ID={user};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" - Individual calls (from Postman for example) are quick, ~150 ms:
What can I do to further investigate the cause of the waiting time?
Nice PS: I was surprised to see a 5x improvement when deploying to a Linux web app (with the P1V2, 2x size of an S1).
