I am reading data from remote service via
HttpWebRequest. Received JSON message is temporarily stored as a
List(Of Customer) and subsequently inserted into a table in Azure SQL using Dapper. Application runs as an ASP.NET with .Net Framework 4.6.2 hosted in Azure App Service. So these are two steps:
A. Get data via HTTP request and store as
List(Of Customer) into Customer table in Azure SQL using Dapper.
This works fine until I try to read and insert another set of data in same procedure:
C. Get data via HTTP request and store as
List(Of Payment) into Payment table in Azure SQL using Dapper.
Problem: Second HTTP request (step C) always times out. I have done multiple tests. Here are my observations:
System.Net.ServicePointManager.DefaultConnectionLimithowever they have already been set to
This demonstrates some link between SQL inserts and subsequent HTTP request. However this seems hard to believe and I would point at some more general issue like memory leaks, etc.
Trying to investigate it further. Using the original scenario A+B+C+D, once number of rows returned by the first HTTP request was limited, it started to work. Originally remote service returned 18.000 rows. Seems like 10.000 is number when it starts to work (some executions still run with error). With 9.000 rows in step A code runs without any issues. Naturally SQL inserts in step B need less time.
HttpWebRequest.KeepAlive = False for web service requests in steps A and C solved the problem.
However still don't know why it helps and what is the reason of the underlying issue.