Executing Stored Procedures in Parallel using ADO.NET

In some cases, when every milisecond counts, you might need to execute several stored procedures at the same time. At the same time here means that when calling the first stored procedure, you don't want to wait for its result in order to call the second one.

In other words, if the two stored procedures are independent, it would be a brilliant idea to improve performance by executing them in parallel.

Say, for example, that you have two stored procedures: SP1 and SP2. Everyone of them takes 3 seconds. If you want to call them in sequence, this would mean you'll need to wait for 6 seconds. Calling them in parallel will let them both execute in around 3 seconds.

In this article, I will give an example of an ASP.NET web page that calls 2 parallel stored procedures in its load method.

Let's say that you have 2 stored procedure named SP1 and SP2. Everyone of them returns a result set. Every result set is independent from the other. For simplicity, I'll say that you are only interested in the first record of the result set and you want to display this value in a Literal control.

If you want to call them sequentially, or synchronously, this would typically be what you write:

protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection cnn = new SqlConnection("Your connection string");
    SqlCommand cmd1;
    SqlCommand cmd2;
    SqlDataReader reader1;
    SqlDataReader reader2;
 
    try
    {
        cnn.Open();
 
        cmd1 = new SqlCommand("SP1", cnn);
        cmd1.CommandType = System.Data.CommandType.StoredProcedure;
        reader1 = cmd1.ExecuteReader();
 
        if (reader1.Read())
        {
            Literal1.Text = reader1[0].ToString();
        }
 
        if (!reader1.IsClosed)
        {
            reader1.Close();
        }
 
        cmd2 = new SqlCommand("SP2", cnn);
        cmd2.CommandType = System.Data.CommandType.StoredProcedure;
        reader2 = cmd2.ExecuteReader();
 
        if (reader2.Read())
        {
            Literal2.Text = reader2[0].ToString();
        }
 
        if (!reader2.IsClosed)
        {
            reader2.Close();
        }
    }
    catch (Exception ex)
    {
        // raise an exception or do whatever logic you want
    }
    finally
    {
        if (cnn.State != System.Data.ConnectionState.Closed)
            cnn.Close();
    }
}

However, if you want to call them asynchronously, you need to create to different connections. They can both use the same connection string. In addition, you'll need to use BeginExecuteReader and EndExecuteReader instead of the usual ExecuteReader


Your code will look more like this:

protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection cnn1 = new SqlConnection("Your connection string");
    SqlConnection cnn2 = new SqlConnection("Your connection string");
    SqlCommand cmd1;
    SqlCommand cmd2;
    IAsyncResult result1;
    IAsyncResult result2;
    SqlDataReader reader1;
    SqlDataReader reader2;
 
    try
    {
        cnn1.Open();
 
        cmd1 = new SqlCommand("SP1", cnn1);
        cmd1.CommandType = System.Data.CommandType.StoredProcedure;
        result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);
 
        cnn2.Open();
        cmd2 = new SqlCommand("SP2", cnn2);
        cmd2.CommandType = System.Data.CommandType.StoredProcedure;
        result2 = cmd2.BeginExecuteReader(CommandBehavior.SingleRow);
 
        reader1 = cmd1.EndExecuteReader(result1);
 
        if (reader1.Read())
        {
            Literal1.Text = reader1[0].ToString();
        }
 
        reader1.Close();
 
        reader2 = cmd2.EndExecuteReader(result2);
 
        if (reader2.Read())
        {
            Literal2.Text = reader2[0].ToString();
        }
 
        reader2.Close();
    }
    catch (Exception ex)
    {
        // raise an exception or do whatever logic you want
    }
    finally
    {
        if (cnn1.State != System.Data.ConnectionState.Closed)
            cnn1.Close();
 
        if (cnn2.State != System.Data.ConnectionState.Closed)
            cnn2.Close();
    }
}