SqlDataAdapter.Fill - Asynchronous approach

Question

Using C# / .NET 3.5.

Currently I'm populating 2 DataTables one after the other using SqlDataAdapter.Fill().

I want to populate both of these DataTables in parallel, at the same time by doing each one asynchronously. However, there is no asynchronous version of the Fill() method - i.e. BeginFill() would be great!

One approach I've tried is (pseudo):

1. SqlCommand1.BeginExecuteReader // 1st query, for DataTable1
2. SqlCommand2.BeginExecuteReader // 2nd query, for DataTable2
3. SqlCommand1.EndExecuteReader
4. SqlCommand2.EndExecuteReader
5. DataTable1.Load(DataReader1)
6. DataTable2.Load(DataReader2)

However, DataTable.Load() takes a long time:
It takes 3 seconds to do step 1 to step 4.
Step 5 then takes 22 seconds.
Step 6 takes 17 seconds.
So, combined 39 seconds for steps 5 and 6.

The end result is, this gives me no benefit over just doing 2 SqlDataAdapter.Fills, one after the other. I want the net result to be that the entire process takes only as long as the longest query (or as close to that as possible).

Looking for recommended ways forward to end up with something that is truly an asynchronous approach to filling a DataTable.

Or do I just manage it myself and roll 2 separate threads, each one filling a DataTable?

Answer 1

I would suggest have a separate worker thread for each. You could use ThreadPool.QueueUserWorkItem.

List<AutoResetEvent> events = new List<AutoResetEvent>();

AutoResetEvent loadTable1 = new AutoResetEvent(false);
events.Add(loadTable1);
ThreadPool.QueueUserWorkItem(delegate 
{ 
     SqlCommand1.BeginExecuteReader;
     SqlCommand1.EndExecuteReader;
     DataTable1.Load(DataReader1);
     loadTable1.Set();
});

AutoResetEvent loadTable2 = new AutoResetEvent(false);
events.Add(loadTable2);
ThreadPool.QueueUserWorkItem(delegate 
{ 
     SqlCommand2.BeginExecuteReader;
     SqlCommand2.EndExecuteReader;
     DataTable2.Load(DataReader2);
     loadTable2.Set();
});

// wait until both tables have loaded.
WaitHandle.WaitAll(events.ToArray());

Answer 2

This is because the DataTable has a lot of objects to create (rows, values). You should have the execution of the adapter and population of a datatable all done in a different thread, and synchronise waiting for each operation to finish before you continue.

The following code was written in Notepad and probably doesn't even compile, but hopefully you get the idea...

// Setup state as a parameter object containing a table and adapter to use to populate that table here

void DoWork()
{
    List<AutoResetEvent> signals = GetNumberOfWaitHandles(2);

    var params1 = new DataWorkerParameters
        {
            Command = GetCommand1();
            Table = new DataTable();
        }

    var params2 = new DataWorkerParameters
        {
            Command = GetCommand2();
            Table = new DataTable();
        }

    ThreadPool.QueueUserWorkItem(state => 
        {
            var input = (DataWorkerParameters)state;
            PopulateTable(input);
            input.AutoResetEvent.Set(); // You can use AutoResetEvent.WaitAll() in the caller to wait for all threads to complete
        },
        params1
    );

    ThreadPool.QueueUserWorkItem(state => 
        {
            var input = (DataWorkerParameters)state;
            PopulateTable(input);
            input.AutoResetEvent.Set(); // You can use AutoResetEvent.WaitAll() in the caller to wait for all threads to complete
        },
        params2
    );

    WaitHandle.WaitAll(signals.ToArray());
}


void PopulateTable(DataWorkerParameters parameters)
{
    input.Command.ExecuteReader();
    input.Table.Load(input.Command);
}