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();
    }
}

Implement Sequence in Sql Server

Problem

In many cases, a database developer would rely on the DBRMS to generate and auto-increment a primary key in a table. In Oracle, for instance, the "Sequence" feature provides an encapsulated functionality to handle incrementing any particular variable. A sequence is not necessarily attached to a particular table, but rather, it's considered an independent entity that can be used amongst several tables.

In Sql Server, however, the sequence feature is replaced by the Identity column feature. One and only one column in any table can be defined as identity, and then the Sql Server will take over the job of incrementing this column with the certain value specified by the developer.

In some cases, a developer might need to make a column auto-incremented while it's not the table's primary key. Since Sql Server allows only one identity in a table, which might sound logical because a table wouldn't and shouldn't have more than one identity, a developer should handle incrementing this column manually.

One solution to this problem can be reading the last value of the field and then incrementing it and inserting the new record with the incremented value. However, this has integrity issue. Suppose two users consume the table simultaneously, both of them will read the same value and increment it with the same amount resulting in the same field for both records.

Solution

Another solution that proved its efficiency is the following:

1. Create a table with the column that you want to be incremented

CREATE TABLE [MyTable](
 
[SomeInfo] [nvarchar](50) NULL,
 
[IncrementedColumn] [int] NULL
 
) ON [PRIMARY]

2. Create a table and call it sequence

CREATE TABLE [Sequence](
 
 [SequenceColumn] [int] NULL
 
) ON [PRIMARY]

3. Insert a value into sequence table to start incrementing from it.

INSERT INTO Sequence (SequenceColumn)
 
VALUES (5)

4. Whenever you want to insert a new record into 'MyTable', update the sequence using the following:

DECLARE @NewSequence INT
 
UPDATE Sequence
SET @NewSequence = SequenceColumn = SequenceColumn + 1

and then you can use @NewSequence to insert a record into 'MyTable'