Slow Database Connection from Azure Web Application

Question

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>

Answer 1

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).

Answer 2

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

Answer 3

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.