Using SqlDataReader NextResult to improve performance

John Avis by | July 31, 2014 | ASP.NET Web Forms Databases

On database-driven websites it is commonplace to execute multiple database queries to generate just one page. For example, on the home page of an ecommerce website you might get a list of categories, a list of banner advertisements to display, a list of featured products, and a list of top selling products. I investigate whether it was more efficient to put these multiple queries into one stored procedure and use the SqlDataReader's Next Result method to get the data all in one go.
On database-driven websites it is commonplace to execute multiple database queries to generate just one page. For example, on the home page of an ecommerce website you might get a list of categories, a list of banner advertisements to display, a list of featured products, and a list of top selling products.

Generally it is common practice in this situation to execute four queries to get this data for display.

I wanted to investigate whether it was more efficient to put these multiple queries into one stored procedure and use the SqlDataReader's Next Result method to get the data all in one go.

My test case was to populate five Repeaters with data.

Test 1 is five different stored procedures and SqlDataReaders.

using (SqlConnection conn = new SqlConnection(Config.ConnString))
{
conn.Open();

String sql = "stp_Test1";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater1.DataSource = reader;
Repeater1.DataBind();
}
}

sql = "stp_Test2";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater2.DataSource = reader;
Repeater2.DataBind();
}
}

sql = "stp_Test3";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater3.DataSource = reader;
Repeater3.DataBind();
}
}

sql = "stp_Test4";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater4.DataSource = reader;
Repeater4.DataBind();
}
}

sql = "stp_Test5";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater5.DataSource = reader;
Repeater5.DataBind();
}
}
}

Test 2 is one stored procedure with the five queries retrieved using the one SqlDataReader and the use of the NextResult method.

using (SqlConnection conn = new SqlConnection(Config.ConnString))
{
conn.Open();

String sql = "stp_Test";

using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = comm.ExecuteReader())
{
Repeater1.DataSource = reader;
Repeater1.DataBind();

reader.NextResult();
Repeater2.DataSource = reader;
Repeater2.DataBind();

reader.NextResult();
Repeater3.DataSource = reader;
Repeater3.DataBind();

reader.NextResult();
Repeater4.DataSource = reader;
Repeater4.DataBind();

reader.NextResult();
Repeater5.DataSource = reader;
Repeater5.DataBind();
}
}
}

What was the result? On average test 2 executed around 40% quicker.

So the conclusion appears to be that thus technique is the way to go, but my testing does not take into consideration all factors (such as any impact due to having the reader open for longer) or what might happen on a very high traffic website.

Have you tried this technique? Please leave a comment with your results.

Related Posts

Bootstrap ASP.NET Web Forms

ASP.NET Web Forms and Bootstrap 3 Modals

by John Avis | July 6, 2017

There are a few methods for hiding and showing Bootstrap 3.x modals in an ASP.NET Web Forms application. This technique is pure ASP.NET (requires no client script) and allows you to show and hide modals by changing the Visible property of a containing PlaceHolder or Panel.


ASP.NET Web Forms

Disable common UpdateProgress controls for an UpdatePanel with an associated UpdateProgress

by John Avis | April 12, 2017

If you have an ASP.NET Web Forms page with multiple UpdatePanels and multiple UpdateProgress controls, sometimes you may want one or more UpdateProgress controls associated with a specific UpdatePanel, and one or more UpdateProgress controls that have no association with an UpdatePanel so are shown when any UpdatePanel is updated.


Bootstrap ASP.NET Web Forms

Bootstrap checkbox-inline and radio-inline with ASP.NET CheckBoxList and RadioButtonList controls

by John Avis | June 20, 2016

Although you can easily create inline checkboxes and radio buttons using ASP.NET CheckBoxes and RadioButtons using Bootstrap's checkbox-inline and radio-inline classes, it's not so easy with ASP.NET CheckBoxList and RadioButtonList controls.

Comments

There are no comments yet. Be the first to leave a comment!

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.