CODEWITHSUNDEEP
c#entity-frameworkazure
Tony Lodge
Tony Lodge

Reputation: 1

Entity framework connection timeout performance issues

Please forgive any obvious errors/misunderstandings! I'm new!

I'm creating a simple c# Winforms app connecting to a sql azure database using entity framework 6, code first. Dev platform is VS2015 community edition.

A form loads a combobox with record IDs/Names for selection.

Upon record selection via combobox I:

1) Create a new context (disposing prior context first)

2) Load the data graph

3) Populate form

4) Allow user changes

5) (upon user command) Save changes / Dispose of context

My issue is that if I use the combobox to select records the query to populate the data runs in about 30-40ms (according to debug info). This works while I am busy selecting records.

However, If I leave the form unattended for say, 30 seconds and then select a record, the exact same query takes in the region of 500ms. Same data, connection string, everything. There must be a connection issue I'm not aware of. Half a second is not a huge time but it's noticeable and I feel I should understand why.

I pushed up the connection and command timeout properties to large values but same result.

My Azure database is S1 tier.

My connection string is "Server=tcp:*****.windows.net,1433;Database=*****;User ID=*********;Password=******;Trusted_Connection=False;Encrypt=True;Connection Timeout=1200;multipleactiveresultsets=true" providerName="System.Data.SqlClient" />

I am using SqlAzureExecutionStrategy.

My client computer is an i7 4Ghz/16GB Ram/SSD running windows 10 pro.

I would really appreciate some help please. Thanks.

Code sample:
private void LoadRecord(int intID)
{
ResetContext();
var graph = _Context.CareCoordinators
.Where(c => c.CareCoordinatorId.Equals(intID))
.Include("DefaultAreas")
.Include("Branch")
.ToList();
_Subject = graph[0];
}

Upvotes: 0

Views: 638

Answers (1)

user1666620
user1666620

Reputation: 4808

Dispose your context after you have used it, not before you create a new one. That could be what is taking up your time.

Something like the below should help:

private void LoadRecord(int intID)
{
    using (Context context = new Context())
    {
        var graph = context.CareCoordinators
        .Where(c => c.CareCoordinatorId.Equals(intID))
        .Include("DefaultAreas")
        .Include("Branch")
        .ToList();
        _Subject = graph[0];
    }
}

Unfortunately I can't be more specific as you didn't provide code for the ResetContext() method.

Upvotes: 0

Related Questions