How to make two SQL queries really asynchronous

Question

My problem is based on a real project problem, but I have never used the System.Threading.Tasks library or performing any serious programming involving threads so my question may be a mix of lacking knowledge about the specific library and more general misunderstanding of what asynchronous really means in terms of programming.

So my real world case is this - I need to fetch data about an user. In my current scenario it's financial data so let say I need all Accounts, all Deposits and all Consignations for a certain user. In my case this means to query million of records for each property and each query is relatively slow itself, however to fetch the Accounts is several times slower than fetching the Deposits. So I have defined three classes for the three bank products I'm going to use and when I want to fetch the data for all the bank products of certain user I do something like this :

List<Account> accounts = GetAccountsForClient(int clientId);
List<Deposit> deposits = GetDepositsForClient(int clientId);
List<Consignation> consignations = GetConsignationsForClient(int clientId);

So the problem starts here I need to get all those three list at the same time, cause I'm going to pass them to the view where I display all users data. But as it is right now the execution is synchronous (If I'm using the term correctly here) so the total time for collecting the data for all three products is:

Total_Time = Time_To_Get_Accounts + Time_To_Get_Deposits + Time_To_Get_Consignations

This is not good because the each query is relatively slow so the total time is pretty big, but also, the accounts query takes much more time than the other two queries so the idea that get into my head today was - "What if I could execute this queries simultaneously". Maybe here comes my biggest misunderstanding on the topic but for me the closest to this idea is to make them asynchronous so maybe then Total_Time won't be the time of the slowest query but yet will be much faster than the sum of all three queries.

Since my code is complicated I created a simple use case which I think, reflect what I'm trying to do pretty well. I have two methods :

public static async Task<int> GetAccounts()
{
    int total1 = 0;
    using (SqlConnection connection = new SqlConnection(connString))
    {
        string query1 = "SELECT COUNT(*) FROM [MyDb].[dbo].[Accounts]";
        SqlCommand command = new SqlCommand(query1, connection);
        connection.Open();
        for (int i = 0; i < 19000000; i++)
        {
            string s = i.ToString();
        }
        total1 = (int) await command.ExecuteScalarAsync();
        Console.WriteLine(total1.ToString());
    }
    return total1;
}

and the second method :

public static async Task<int> GetDeposits()
{
    int total2 = 0;
    using (SqlConnection connection = new SqlConnection(connString))
    {
        string query2 = "SELECT COUNT(*) FROM [MyDb].[dbo].[Deposits]";
        SqlCommand command = new SqlCommand(query2, connection);
        connection.Open();
        total2 = (int) await command.ExecuteScalarAsync();
        Console.WriteLine(total2.ToString());
    }
    return total2;
}

which I call like this:

static void Main(string[] args)
{
    Console.WriteLine(GetAccounts().Result.ToString());

    Console.WriteLine(GetDeposits().Result.ToString());
}

As you can see I call GetAccounts() first and I slow the execution down on purpose so I give a chance the execution to continue to the next method. However I'm not getting any result for a certain period of time and then I get all printed on the console at the same time.

So the problem - how to make so that I don't wait for the first method to finish, in order to go to the next method. In general the code structure is not that important, what I really want to figure out is if there's any way to make both queries to execute at the same time. The sample here is the result of my research which maybe could be extended to the point where I'll get the desired result.

P.S I'm using ExecuteScalarAsync(); just because I started with a method which was using it. In reality I'm gonna use Scalar and Reader.

Answer 1

When you use the Result property on a task that hasn't completed yet the calling thread will block until the operation completes. That means in your case that the GetAccounts operation need to complete before the call to GetDeposits starts.

If you want to make sure these method are parallel (including the synchronous CPU-intensive parts) you need to offload that work to another thread. The simplest way to do so would be to use Task.Run:

static async Task Main()
{
    var accountTask = Task.Run(async () => Console.WriteLine(await GetAccounts()));
    var depositsTask = Task.Run(async () => Console.WriteLine(await GetDeposits()));

    await Task.WhenAll(accountTask, depositsTask);
}

Answer 2

Async is great if the process takes a long time. Another option would be to have one stored procedure that returns all three record sets.

        adp = New SqlDataAdapter(cmd)
        dst = New DataSet
        adp.Fill(dst)

In the code behind of the page, reference them as dst.Tables(0), dst.Tables(1), and dst.Tables(2). The tables will be in the same order as the select statements in the stored procedure.

Answer 3

If it's ASP.NET use AJAX to fetch after the page is rendered and put the data in a store. Each AJAX fetch is asynchronous. If you want to create simultaneous SQL queries on the server?

Usage:

 // Add some queries ie. ThreadedQuery.NamedQuery([Name], [SQL])
 var namedQueries= new ThreadedQuery.NamedQuery[]{ ... };

 System.Data.DataSet ds = ThreadedQuery.RunThreadedQuery(
 "Server=foo;Database=bar;Trusted_Connection=True;", 
 namedQueries).Result;


 string msg = string.Empty;
 foreach (System.Data.DataTable tt in ds.Tables)
 msg += string.Format("{0}: {1}\r\n", tt.TableName, tt.Rows.Count);

Source:

public class ThreadedQuery
{

    public class NamedQuery
    {
        public NamedQuery(string TableName, string SQL)
        {
            this.TableName = TableName;
            this.SQL = SQL;
        }
        public string TableName { get; set; }
        public string SQL { get; set; }
    }
    public static async System.Threading.Tasks.Task<System.Data.DataSet> RunThreadedQuery(string ConnectionString, params NamedQuery[] queries)
    {

        System.Data.DataSet dss = new System.Data.DataSet();
        List<System.Threading.Tasks.Task<System.Data.DataTable>> asyncQryList = new List<System.Threading.Tasks.Task<System.Data.DataTable>>();

        foreach (var qq in queries)
            asyncQryList.Add(fetchDataTable(qq, ConnectionString));

        foreach (var tsk in asyncQryList)
        {
            System.Data.DataTable tmp = await tsk.ConfigureAwait(false);
            dss.Tables.Add(tmp);
        }

        return dss;

    }

    private static async System.Threading.Tasks.Task<System.Data.DataTable> fetchDataTable(NamedQuery qry, string ConnectionString)
    {
        // Create a connection, open it and create a command on the connection
        try
        {

            System.Data.DataTable dt = new System.Data.DataTable(qry.TableName);
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync().ConfigureAwait(false);
                System.Diagnostics.Debug.WriteLine("Connection Opened ... " + qry.TableName);
                using (SqlCommand command = new SqlCommand(qry.SQL, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        System.Diagnostics.Debug.WriteLine("Query Executed ... " + qry.TableName);

                        dt.Load(reader);

                        System.Diagnostics.Debug.WriteLine(string.Format("Record Count '{0}' ... {1}", dt.Rows.Count, qry.TableName));

                        return dt;
                    }
                }
            }
        }
        catch(Exception ex)
        {

            System.Diagnostics.Debug.WriteLine("Exception Raised ... " + qry.TableName);
            System.Diagnostics.Debug.WriteLine(ex.Message);

            return new System.Data.DataTable(qry.TableName);
        }

    }
}

Answer 4

Here's a way to to perform two tasks asynchronously and in parallel:

Task<int> accountTask = GetAccounts();
Task<int> depositsTask = GetDeposits();

int[] results = await Task.WhenAll(accountTask, depositsTask);

int accounts = results[0];
int deposits = results[1];

Answer 5

I generally prefer to use Task.WaitAll. To setup for this code segment, I changed the GetAccounts/GetDeposits signatures just to return int (public static int GetAccounts())

I placed the Console.WriteLine in the same thread as assigning the return to validate that one GetDeposits returns before GetAccounts has, but this is unnecessary and probably best to move it after the Task.WaitAll

     private static void Main(string[] args) {

        int getAccountsTask = 0;
        int getDepositsTask = 0;
        List<Task> tasks = new List<Task>() {
            Task.Factory.StartNew(() => {
                getAccountsTask = GetAccounts();
                Console.WriteLine(getAccountsTask);
            }),
            Task.Factory.StartNew(() => {
                getDepositsTask = GetDeposits();
                Console.WriteLine(getDepositsTask);

            })

        };
        Task.WaitAll(tasks.ToArray());



    }