Pages

29.12.10

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

29 comments:

  1. Heya і аm for tҺe first time hеre. І camе аcross tɦis
    board аnd I find It reallу սseful & it helped me out mucҺ.

    І hope tօ gikve somеthing bacҡ and help ߋthers like
    you aided me.

    Lߋok ɑt my homepage; mp3 volume enhancer ()

    ReplyDelete
  2. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  3. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me. Keep it up!!
    Machine Learning Course

    ReplyDelete
  4. It’s good to check this kind of website. I think I would so much from you. ExcelR Machine Learning Course Pune

    ReplyDelete
  5. I read this post two times, I like it so much, please try to keep posting & Let me introduce other material that may be good for our community.

    ReplyDelete
  6. I read this post two times, I like it so much, please try to keep posting & Let me introduce other material that may be good for our community. data science courses

    ReplyDelete
  7. Your post is very great.i read this post this is a very helpful. i will definitely go ahead and take advantage of this. You absolutely have wonderful stories.Cheers for sharing with us your blog. python training in noida

    angular js training in chennai

    angular js training in tambaram

    full stack training in chennai

    full stack training in tambaram

    php training in chennai

    php training in tambaram

    photoshop training in chennai

    photoshop training in tambaram

    ReplyDelete
  8. Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
    oracle training in chennai

    oracle training in omr

    oracle dba training in chennai

    oracle dba training in omr

    ccna training in chennai

    ccna training in omr

    seo training in chennai

    seo training in omr

    ReplyDelete
  9. Good Post! , it was so good to read and useful to improve my knowledge as an updated one, keep blogging.After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts likethis.
    https://www.3ritechnologies.com/course/selenium-online-training/

    ReplyDelete
  10. If you are trying to get ahead of the competition in
    the search engine results pages, you should try purchasing SEO group buy tools.

    ReplyDelete
  11. Group Buy SEO ToolsGroup Buy SEO Tools offer a huge load of uncommon features which produce outstanding results. These SEO devices are used by SEO trained professionals and progressed promoters to drive more traffic to destinations. our organizations are open to SEO beginners, digital advertisers and on a limited financial plan.

    ReplyDelete
  12. Wonderful blog post.
    Juan Francisco Estrada vs Roman “Chocolatito� Gonzalez Live
    estrada vs chocolatito 2 live stream free

    ReplyDelete
  13. This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the leisure here! Keep up the good work. I have been meaning to write something like this on my website and you have given me an idea.
    data scientists training

    ReplyDelete
  14. StockTrim is inventory planning and demand forecasting software available as a subscription. It helps SME's gain optimised inventory levels and preserves up to 40% of their working capital.Stock Control

    ReplyDelete
  15. on our movie website have compiled both old movies and new movies for you to watch Online movie theaters. ดูหนัง (Watch movies) online for free.

    ReplyDelete
  16. Really an awesome blog, with informative and knowledgeable content. Thanks for sharing this blog with us. Keep sharing more stuff again.
    AI Patasala Data Science Training

    ReplyDelete
  17. Discovering the power of making parallel database calls has been a game-changer. Set Up A Server For Web Hosting It significantly improves application performance and responsiveness.

    ReplyDelete

Promotional Code for Udemy ServiceNow CIS - HR Practice Tests

If you're planning to become ServiceNow Certified Implementation Specialist - Human Resources (CIS-HR), you can prepare for the exam usi...