Execute parallel database access in ADO.Net C#

Question

I'm working on asp.net application to display multiple widgets on dashboard. So I'm sending an ajax call to get all the required data to the dashboard widgets.

Inside the server method there are multiple database calls to get relevant data for each widget from the database via stored procedures. Method content as follows;

List<DashboardItem> items = new List<DashboardItem>();
items.Add(GetUserList()); 
items.Add(GetNewsList()); 
items.Add(GetRecentlyViewedList()); 
items.Add(GetSentEmailsList()); 
.....
return items;

It seems this process is very slow due to the no of records in the database.

I'm trying to execute above code with Async database calls. It seems async database operations can improve this process. Can anyone help me to write async database operations? or any other suggestions to improve performance on database method calls?

on your GetXXX use the Async versions of the database connector with the await keyword, then instead of returning DashboardItem[] return Task<DashboardItem[]>, then you can create a List<Task<DashboardItem[]>>, add the returnted Tasks from these functions, then do await Task.WaitAll(theListOfTasks) and finally use the .Result of each task stored in the list. — Gusman
– Gusman, Commented May 13, 2016 at 11:43
Instead of trying to execute queries in parallel, simply batch them all together. NHibernate allows batching, and EF can batch queries using extensions. Parallel execution helps only when you have to move a lot (think GB) of data data over multiple network cards. Otherwise you simply introduce more concurrency overhead and use way more connections than needed — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2016 at 11:44
@Gusman using blocking methods like WaitAll is a bad idea. The whole point is to reduce blocking. await Task.WhenAll() won't block. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2016 at 11:46
@Aruna no, that doesn't result in delays, unless the SPs are not well written. BTW there is no performance difference between a raw SQL query vs a stored procedure. What does result in delays is establishing multiple connections and the unnecessary roundtrips. You could batch all calls together and pay only for a single round trip. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2016 at 12:01

David Pine · Accepted Answer · 2016-05-13 13:37:40Z

You need to have your entry point marked as Task returning and you can then use async and await keywords. Also, the GetUserList, GetNewsList, GetRecentlyViewedList and GetSentEmailsList methods will also have to be Task returning such that you go async all the way -- (notice the naming convention of adding Async to the methods to indicate their nature).

public async Task GetDashboardItemsAsync()
{
    var getUserTask = GetUserListAsync();
    var getNewsTask = GetNewsListAsync();
    var getRecentlyViewedTask = GetRecentlyViewedListAsync();
    var getSentEmailsTask = GetSentEmailsListAsync();

    await Task.WhenAll(getUserTask,
                       getNewsTask,
                       getRecentlyViewedTask,
                       getSentEmailsTask);

    return new List<DashboardItem>
    {
        getUserTask.Result,
        getNewsTask.Result,
        getRecentlyViewedTask.Result,
        getSentEmailsTask.Result,
    };
}

Storing the tasks in a variable will allow you to access the .Result after they have all been materialized. The Task.WhenAll method takes an array of Task and it will continue once all of them have been completed.

With this approach you're basically saying start each task to fetch the dashboard data, once all asynchronous operations have ran to completion, then continue to returning the list of DashboardItem to the caller.

This is primarily addressing the calling code and how to handle the multiple tasks in a parallel manner. In order to start the fetch operations at the ADO.NET level that should be a different question. But there are a lot of async APIs on ADO.NET and Entity Framework too.

Collectives™ on Stack Overflow

Execute parallel database access in ADO.Net C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related