I am trying to perform multiple database calls using async and await features available in .NET Framework 4.5. This is the first time I am implementing this feature.
if each query is taking 7 seconds, it used to take 35 seconds(5 queries * 7 seconds). With below implementation, I was expecting it should fetch and populate controls in asp page in close to 7-9 seconds. However, it is still taking 35 seconds, proving me the synchronous behavior.
can someone please help me where I am going wrong with this below asynchronous implementation.
I appreciate on any inputs, I have been breaking my head around this since couple of days
protected void Page_Load(object sender, System.EventArgs e)
{
RegisterAsyncTask(new PageAsyncTask(FillControlsAsync));
}
public async Task FillControlsAsync()
{
Task[] tasks = new Task[]{
PopulateControlTask(query1, "controlID1"),
PopulateControlTask(query2, "controlID2"),
PopulateControlTask(query3, "controlID3"),
PopulateControlTask(query4, "controlID4"),
PopulateControlTask(query5, "controlID5")
});
await Task.WhenAll(tasks);
}
public async Task PopulateControlTask(string query, string control)
{
await Task.Run(() =>
{
DataSet ds;
OracleCommand cmd;
OracleDataAdapter da;
try
{
if (!Page.IsPostBack)
{
cmd = new OracleCommand(query, cn);
da = new OracleDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
switch (control)
{
case "controlID1":
//some custom code for control 1
// like attaching the datasource to control.
break;
case "controlID2":
//some custom code for control 2
break;
case "controlID2":
//some custom code for control 3
break;
case "controlID3":
//some custom code for control 4
break;
case "controlID4":
//some custom code for control 5
break;
}
}
}
catch(Exception e)
{
//some error handling here
}
});
}
async and await are for asynchronous code. Normally, if you had a scalable database, you could make your db calls asynchronous and thus scale your server. Note that the primary benefit of async on ASP.NET is scalability, not response time.
However, as others have noted, Oracle doesn't support asynchronous code.
But that is immaterial, since the code you posted isn't actually asynchronous to begin with! It's what I call "fake asynchronous", because it's just pushing synchronous work off to a background thread using Task.Run instead of using naturally-asynchronous APIs. (But as already noted, in this case (that is, Oracle), you don't have any naturally-asynchronous APIs to work with).
So, what you end up with is parallelism, not asynchrony. In particular, the code is spreading itself over 5 thread pool threads to do its work.
Now, the first thing you need to do is ask yourself if you really want parallelism on the server. Your requests will take 5 threads instead of 1 (or 0). This can greatly impact the scalability of your web server (in a bad way). Also, take into consideration the capabilities of your backend. If it's a single server and these queries are all hitting a single database on a single hard drive, will parallelizing 5 of them actually produce any benefit or will it actually be just as bad if not worse due to disk contention? (You should be able to whip up a quick console app to test how your db responds with serial vs parallel requests, when idle and when under load).
I find that the vast majority of the time, the answer is "no, I do not want to bring my entire db server to its knees for this one request" - in other words, avoid parallelism on the server.
But if you have weighed the options and decided that yes, yours is one of the rare cases where parallelism is appropriate on ASP.NET, then you should ask the question that you've posted here: why are these running sequentially and not concurrently? (side note: it's sequential vs concurrent here, not synchronous vs asynchronous)
Answer: I don't know.
But I have a guess: if the database connection (cn in your code snippet) is shared, then it's likely that the db connection itself is limited to one query at a time. Other database connection systems have similar restrictions. The first thing I'd try is giving each query its own connection.
That is, if you want to parallelize your web server. Which is a big "if".