Azure SQL DB slow (connection/performance)

Question

I wrote the following code:

for (int i = 0; i < 100; i++)
{
    string connectionString = Configuration.GetConnectionString(DB_CONNECTION_STRING);
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand($"SELECT TOP {i} * FROM PortalObjects", con);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

I use 3 different connections:

Azure DB (West Europe) - free version, 5 DTUs;
Our staging server DB (low performance VM located in West Europe) - MS SQL Express;
Local computer DB (East Europe) - MS SQL Developer.

All 3 DBs are identical. The PortalObjects table has about 430 records. Running the code on my local computer shows the following timing for the loop execution:

Azure: 7031 ms;
Staging: 7145 ms;
Local: 67 ms.

When I publish the application to Azure (as App Service) and run the loop there:

Azure: 1668 ms;
Staging: 1670 ms;

The app service and DB are hosted in the same Azure portal, sharing the same subscription and resource group. I'd expect a timing I get for Azure DB hosting the app in Azure should be ~ equal to timing of local DB on local computer. But my results tell that there is no difference between Azure DB or really 3rd party DB for Azure app service - I found it completely unexpected. Or do I do something wrong?

I think that's expected from the free tier and a "low performance VM". What's your local capacity? You may be comparing apples and oranges here. Try running your test again with higher tiers. — CSharpRocks
Although the title of this question mentions connection performance, that is only incurred on the first connection (unless pooling is explicitly disabled via the connection string). I would expect most of the time to be consuming the progressively larger result set. — Dan Guzman
CSharpRocks, increasing to 20 DTUs and scaling up the app service plan to S3 made literally no effect. I'm almost sure it is a network latency problem. See my comment to usr's answer — Maksim Ramanovich

usr usr · Accepted Answer · 2018-01-27T13:22:37

Looks like network latency. Local to Azure seems to have ~70ms of latency for each cycle. This could be two times 35ms. 35ms is a common latency seen on the internet.

Azure to Azure latencies are lower. I'm not sure why they are so high, though (either 16ms or 2x8ms). Maybe the bigger result sets approaching 100 rows need multiple round trips. Why are you testing like that? Test SELECT NULL to measure single roundtrip latency (or double, I'm not sure if TDS requires two roundtrips for this or one).

Given that "Azure" and "Staging" have the same speed for you this means that you have not maxed out the DTUs that you have. Otherwise this test would have been slower.

The fact that "Local" is so fast means that the SQL load is next to nothing. You are merely measuring network.

Azure SQL DB slow (connection/performance)

1 Answers