Using SqlDataReader NextResult to improve performance

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

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

Computers & Internet Web Development Website Hosting

500 Internal Server Error after migrating from IIS 7.5 to IIS 10

by John Avis | November 4, 2019

As support ends for Microsoft Windows Server 2008 I have recently gone through migrating some websites to a new server running Windows Server 2016 and IIS 10 but some of the websites did not work.


Jquery/Javascript Web Development

tagInput: A simple jQuery plugin for tag entry using Bootstrap 4

by John Avis | October 15, 2019

For a website project I needed a way to enter multiple tags. I just wanted something simple that I could easily modify to suit my own needs, so I wrote my own.


ASP.NET Web Forms Web Development

ASP.NET bug with RadioButton GroupName in Repeater

by John Avis | September 1, 2019

I rediscovered a bug in ASP.NET that affects RadioButtons inside repeaters. Here is my solution to the problem.

Comments

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

Leave a Comment

Tags

About me

...random postings about web development and programming, Internet, computers, electronics and automotive topics.

Subscribe

Get the latest posts delivered to your inbox.