Best practice for long SQL queries in ASP.Net MVC

Question

I have an action method that should complete 15 ~ 52 long SQL queries (all are similar, each takes more than 5 seconds) according to the dates selected by the user.

After a lot of research, it seems the best way to do this without blocking the ASP.Net thread is to use the async / wait task methods using SQL queries:

[HttpPost] public async Task<JsonResult> Action() { // initialization stuff // create tasks to run async SQL queries ConcurrentBag<Tuple<DateTime, List<long>>> weeklyObsIdBag = new ConcurrentBag<Tuple<DateTime, List<long>>>(); Task[] taskList = new Task[reportDates.Count()]; int idx = 0; foreach (var reportDate in reportDates) { //15 <= reportDates.Count() <= 52 var task = Task.Run(async () => { using (var sioDbContext = new SioDbContext()) { var historyEntryQueryable = sioDbContext.HistoryEntries .AsNoTracking() .AsQueryable<HistoryEntry>(); var obsIdList = await getObsIdListAsync( historyEntryQueryable, reportDate ); weeklyObsIdBag.Add(new Tuple<DateTime,List<long>>(reportDate, obsIdList)); } }); taskList[idx++] = task; } //await for all the tasks to complete await Task.WhenAll(taskList); // consume the results from long running SQL queries, // which is stored in weeklyObsIdBag } private async Task<List<long>> getObsIdListAsync( IQueryable<HistoryEntry> historyEntryQueryable, DateTime reportDate ) { //apply reportDate condition to historyEntryQueryable //run async query List<long> obsIdList = await historyEntryQueryable.Select(he => he.ObjectId) .Distinct() .ToListAsync() .ConfigureAwait(false); return obsIdList; } 

After making this change, the time taken to complete this action is significantly reduced, since now I can execute several (15 ~ 52) asynchronous SQL queries at the same time and wait for them to complete, and not run them sequentially. However, users begin to experience many time problems, for example:

 (from Elmah error log) "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." "The wait operation timed out" 

Is this a puzzle triggered? I had the feeling that I could use too many threads from the thread pool to achieve what I want, but I thought this should not be a problem because I used async / await to prevent all threads from being blocked.

If everything goes wrong, then what is the best practice for executing several long SQL queries?

Answer 1

Consider the limitation of the number of parallel tasks performed, for example:

 int concurrentTasksLimit = 5; List<Task> taskList = new List<Task>(); foreach (var reportDate in reportDates) { //15 <= reportDates.Count() <= 52 var task = Task.Run(async () => { using (var sioDbContext = new SioDbContext()) { var historyEntryQueryable = sioDbContext.HistoryEntries .AsNoTracking() .AsQueryable<HistoryEntry>(); var obsIdList = await getObsIdListAsync( historyEntryQueryable, reportDate ); weeklyObsIdBag.Add(new Tuple<DateTime,List<long>>(reportDate, obsIdList)); } }); taskList.Add(task); if (concurrentTasksLimit == taskList.Count) { await Task.WhenAll(taskList); // before clearing the list, you should get the results and store in memory (eg another list) for later usage... taskList.Clear(); } } //await for all the remaining tasks to complete if (taskList.Any()) await Task.WhenAll(taskList); 

Please note: I changed your taskList to the actual List<Task> , it just seems to be easier to use, since we need to add / remove tasks from the list.

In addition, you should get the results before cleaning the taskList , since you are going to use them later.