CODEWITHSUNDEEP
sql-serverasp.net-coreazure-sql-databaseasp.net-core-webapiazure-app-service-plans
Karim Alaa
Karim Alaa

Reputation: 344

Optimize select query EF Core + Azure SQL Server

I'm building an API using .Net Core 3.1 hosted in Azure App Service and Azure SQL Server

Azure App Service specs --> (Basic Tier 1.7 GB Ram)

Azure Sql Database specs --> (Basic Tier, 5 DTU and 2G Max Storage)

Note: App Service and Database are in the same region

My problem is I'm facing a low performence while run a queries to get data from database look like the following code:

dynamic employees = await _context.Employees
    .Where(e => e.CompanyForeignKey == Guid.Parse("d0f022df-498c-4948-9567-ddc16ed49fa0"))
    .Select(emp => new
    {
        //emp.Id,
        FullName_FL = emp.FirstName_FL + " " + emp.SecondName_FL + " " + emp.LastName_FL + " " + emp.FamilyName_FL,
        FullName_SL = emp.FirstName_SL + " " + emp.SecondName_SL + " " + emp.LastName_SL + " " + emp.FamilyName_SL,
        OldValues = emp.OldValues.Select(ov => new
        {
            ov.Value,
            ov.CalculatedValue,
            ov.Category.Name_FL,
            ov.Category.Name_SL,
            .Category.Type // just string
        }).ToList()
    })
    .AsNoTracking()
    .ToListAsync();

And I'm Always get the following error

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

My case is I have 500 employee each employee have 50 oldValues and each OldValue has Category becuase of each model have alot of fields, I'm using Select to selcet only needed fields, but when I debug I found that the generated SQL Query contain all fields in Category instead of just (Name_FL, Name_SL and Type)

So, is the above issue cause the low performence, may I need to optmize my query! so how I solve it!

or my server specs is the reason?

Thanks in advance.

Upvotes: 0

Views: 133

Answers (2)

Teja Rebb
Teja Rebb

Reputation: 17

Basic Tier 5 DTU is too low even for DEV/Test. Consider making it 10 - 30 DTU and retry this.

Upvotes: 0

Wim Ombelets
Wim Ombelets

Reputation: 5265

The server specs aren't the issue.

You're concretizing to list twice, which isn't necessary as far as I can see. Do that at the very last if and only if you actually need a list - this causes unnecessary overhead.

Create a class to hold your retrieved data, steer clear of dynamic unless you find yourself in a situation where you really don't know in advance what the data is going to look like - in this case you do.

Parse the Guid in advance.

Profile your query!

Upvotes: 1

Related Questions