ASP.Net WebForms running mulitple queries at the same time

Question

This is for an enterprise web application.

We are building a front page/dashboard that queries our database for live statistics. The page has 3 update panels, each update panel has a user control that pulls data for it's box. Lets call the user controls UC_NotStarted, UC_Active and UC_Finished.

We have built the queries that return the data and all of them take a while to run (~7 seconds each). So if we let the first page run with one update panel the image spins for ~21 seconds and display everything. We have broken that code into 3 update panels and set the loading to conditional. When we do this we get one box to load every 7 seconds. That’s a step in the right direction but they load sequentially (UC_NotStarted is queried waits 7 seconds and displays, then UC_Active goes for 7 seconds, and finally UC_Finished runs for 7 seconds). We are still at 21 seconds but data is being shown every 7 seconds.

We would like all of the data to be shown in 7 seconds since all of the update panels should be fetching the data at the same time. Instead of using LinqToSQL to pull the data I am now leaning towards web services to get it but not sure if that will work.

Answer 1

Look at the ThreadPool, specifically the QueueUserWorkItem method. It's quite possible with this to run the 3 queries simultaneously and sit in a Thread.Sleep loop waiting for all three to finish executing so you can update all relevant parts of the page.

The following code will almost certainly require tweaking but would give you a rough idea of how to do this:

public class QueryResult
{
    public bool Completed;
    public DataTable Result;
    public int ResultId;
}

public void GetData()
{
    var QueryResults = new List<QueryResult>();
    queryResults.Add(new QueryResult() { ResultId = 1 });
    queryResults.Add(new QueryResult() { ResultId = 2 });
    queryResults.Add(new QueryResult() { ResultId = 3 });

    ThreadPool.QueueUserWorkItem(new WaitCallback(QueryRoutine), queryResults[0]);
    ThreadPool.QueueUserWorkItem(new WaitCallback(QueryRoutine), queryResults[1]);
    ThreadPool.QueueUserWorkItem(new WaitCallback(QueryRoutine), queryResults[2]);

    var completedResults = new List<QueryResult>();
    while(QueryResults.Count > 0)
    {
        for(int 1 = QueryResults.Count - 1; i >= 0; i--;)
        {
            if (queryResults[i].Completed)
            {
                completedResults.Add(queryResults[i]);
                queryResults.RemoveAt(i);
            }
        }
        Thread.Sleep(500);
    }

    // All background threads have completed, do something with the results,
    // return them to the front-end, etc,..
}

public void QueryRoutine(object qR)
{
    var queryResult = (QueryResult)qR;

    // perform long running query here on a distinct thread, as QueryRoutine 
    // is now being run on three separate threads by the calls to QueueUserWorkItem
    // based on queryResult.ResultId picking the relevant query for values 
    // of 1, 2 or 3

    queryResult.Completed = true;
}