7

I have developed a Web Application a standard web application to allow users to display and update a set of data from an SQL database.

The Web Application uses a AngularJS client side which interacts with the Web Server via MVC Web API calls to retrieve and update data on the database. The Server side code is written in C# using .NET 4.5 and uses Entity Framework v6.0 to access the database.

The Web Application is hosted in an Azure Web App. The Database is the Azure SQL Database.

The issue is that when the Application has not been used for about 10-15 minutes, then it is used again, the first data retrieval often takes over 10 seconds to return to the browser. After that the performance is fine until the next time the application is left unused.

I've put trace in the application and we see that the delay is when the connection opens. The actual query on the database runs sub-second.

I've noticed though that with different hosting configurations I get different results. In particular hosting in house and pointing to the Azure database does not encounter anywhere near the same delays.

I've changed one of the routines to use ADO.NET instead of Entity Framework and changed the trace to try to narrow it down further.

What I see is this:

ConnectionStringSettings ADOcnxstring = ConfigurationManager.ConnectionStrings["DevFEConnectAdo"];
DbConnection ADOconnection = new SqlConnection(ADOcnxstring.ConnectionString);

The delay is here (before the SQL has even been defined!

and then I build the command and do the DataReader etc:

    DbCommand ADOcommand = ADOconnection.CreateCommand();
             :
etc

So the delay is on opening the Connection to the database.

My connection string is standard:

<add name="DevFEConnectAdo" connectionString="data   
source=feeunsqldevfeconnect.database.windows.net;initial 
catalog=feeunsqldbdevfeconnect;persist security info=True;user id=???
@???;password=???;multipleactiveresultsets=True"></add>
Artjom B.
  • 61,146
  • 24
  • 125
  • 222
TimBunting
  • 484
  • 5
  • 18
  • "the delay is when the connection opens" Is the delay *due to* the opening or is this just the delay from the start of the request to the opening succeeding? – usr Sep 04 '15 at 12:11
  • The delay is taken up by the Open statement itself. So trace includes a time when the service responds to the request, then the next statement is the open connection, which is the one that takes the time. – TimBunting Sep 07 '15 at 07:37

3 Answers3

3

15 minutes is too short for your app to be recycled (as suggested by CSharpRocks). I dont think its the issue here.

The delay is because a new Db connection is established upon first call after idle timeout. Typically if a connection is inactive for 4-10 minutes it will be closed. If a minimum pool size is specified, those connections will be kept alive even after idle timout expires.

Try using this connection string (adjust min pool size as per your needs)

<add name="DevFEConnectAdo" connectionString="data   
source=feeunsqldevfeconnect.database.windows.net;initial 
catalog=feeunsqldbdevfeconnect;persist security info=True;user id=???
@???;password=???;multipleactiveresultsets=True;Min Pool Size=3;Load Balance Timeout=180;"></add>

Further details Why do we need to set Min pool size in ConnectionString

List of SQL Connection Properties - documentation

Community
  • 1
  • 1
Jp Vinjamoori
  • 1,173
  • 5
  • 16
  • This answer certainly makes sense. However making this change on its own didn't provide the change in performance I was hoping for. However adding this in combination with a Load Balance TimeOut = 180 made an enormous difference. Thanks! – TimBunting Sep 11 '15 at 12:05
  • Load Balance Timeout has default value of '0' which means maximum connection timeout as per this [article](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx). So should have worked even without it. I edited the above solution to reflect @TimBunting suggestion – Jp Vinjamoori Sep 12 '15 at 18:07
  • @TimBunting if this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – Jp Vinjamoori Sep 15 '15 at 10:04
3

After some time, this eventually got resolved with some help from Microsoft Azure support.

The detail that I left out was that my Web App was actually pointing to 2 databases - 1 the Application Azure SQL database, I was having the delay problem with - A 'Data Warehouse' we had on an Azure Virtual Machine

Because of replication between inhouse database servers and the 'Data Warehouse' the Virtual Machine and Web App were all in a Azure Virtual Network.

The problem was there can be network problems if a Web App inside a Virtual network wants to talk to Azure SQL Databases (which cannot be within a Virtual Network).

My solution was to

  • configure an Endpoint on the Data Warehouse Virtual Server,
  • take the Web App out of the Virtual network and make it point to the Virtual Server by means of the Endpoint

At this point all the delays went away and I could take off the MinPool Size settings (and Timeout which I later discovered did nothing anyway).

Artjom B.
  • 61,146
  • 24
  • 125
  • 222
TimBunting
  • 484
  • 5
  • 18
2

Web apps are recycled after a few minutes of inactivity. Try enabling the Always On setting located in Settings/Application Settings in the portal to see if this helps with your issue.

CSharpRocks
  • 6,791
  • 1
  • 21
  • 27