johna's blog

Using SqlDataReader NextResult to improve performance

2014-07-31T12:00:00+10:00

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.

Linq to SQL - Order By Nulls Last

2011-04-07T12:00:00+10:00

Using Linq to SQL and need to order by NULLS last?

The following C# example orders by the field MyColumn ascending and keeps rows where MyColumn is NULL last.

var result = from a in dc.MyTable
             orderby (a.MyColumn == null) ascending, a.MyColumn ascending
             select a;

MySql: Data Types

2008-05-03T12:00:00+10:00

Type {storage}	Name	Range	Attributes	Default
Numeric {1 byte}	TINYINT[(M)]	-128 TO 127 [0 to 255 if UNSIGNED]	AUTO_INCREMENT UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE	NULL [0 if NOT NULL]
Numeric {2 bytes}	SMALLINT[(M)]	-32,768 to 32,767 [0 to 65,535]	AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE	NULL [0 if NOT NULL]
Numeric {3 bytes}	MEDIUMINT[(M)]	-8,388,608 to 8,388,607 [0 to 16,777,215]	AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE	NULL [0 if NOT NULL]
Numeric {4 bytes}	INT[(M)]	-/+2.147E+9 [0 to 4.294E+9]	AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE	NULL [0 if NOT NULL]
Numeric {8 bytes}	BIGINT[(M)]	-/+9.223E+18 [0 to 18.45E+18]	AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE	NULL [0 if NOT NULL]
Numeric {4 or 8}	FLOAT(p)	p=0-24 --> "FLOAT" p=25-53 --> "DOUBLE"	UNSIGNED, ZEROFILL	NULL [0 if NOT NULL]
Numeric {4 bytes}	FLOAT[(M,D)]	Min=+/-1.175E-38 Max=+/-3.403E+38	UNSIGNED, ZEROFILL	NULL [0 if NOT NULL]
Numeric {8 bytes}	DOUBLE[(M,D)]	Min=+/-2.225E-308 Max=+/-1.798E+308	UNSIGNED, ZEROFILL	NULL [0 if NOT NULL]
Numeric {M+2}	DECIMAL[(M,[D])] Stored as string	Max Range = DOUBLE range Fixed point vs. DOUBLE float	UNSIGNED, ZEROFILL	NULL [0 if NOT NULL]
Bit {8 bytes}	BIT[(M)]	Binary. Display by [add zero \| converting with BIN()]. M=1-64	Prior to 5.03 TINYINT(1) Synonym	NULL [0 if NOT NULL]
String {M char's}	CHAR[(M)]	M=0-255 Characters, FIXED. Right padded with spaces.	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {M char's¹}	VARCHAR(M)	M=0-65,535 Characters M=0-255 <v5.0.3	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	TINYTEXT²	0-255 Characters	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	TEXT²	0-65,535 Char's	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	MEDIUMTEXT²	0-16,777,215 Char's	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	LONGTEXT²	0-4,294,967,295 Char's	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {M bytes}	BINARY[(M)]	M=0-255 bytes, FIXED.	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {M bytes}	VARBINARY(M)	0-65,535 bytes M=0-255 <v5.0.3	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	TINYBLOB	0-255 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	BLOB	0-65,535 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	MEDIUMBLOB	0-16,777,215 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	LONGBLOB	0-4,294,967,295 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {1-2 bytes}	ENUM² ("A1","A2",...)	Column is exactly 1 of 1-65,535 values	CHARACTER SET	NULL [1st value if NOT NULL]
String {1-8 bytes}	SET² ("A1","A2",...)	Column is 0 or more values in list of 1-64 members	CHARACTER SET	NULL ["" if NOT NULL]
Date & Time {3 bytes}	DATE	"1000-01-01" - "9999-12-31"	Global Only (YYYY-MM-DD)	NULL ["0000-00-00" if NOT NULL]
Date & Time {8 bytes}	DATETIME	"1000-01-01 00:00:00" - "9999-12-31 23:59:59"	Global Only (YYYY-MM-DD hh:mm:ss)	NULL ["0000-00-00 00:00:00" if NOT NULL]
Date & Time {3 bytes}	TIME	"-838:59:59" - "838:59:59"	Global Only (hh:mm:ss)	NULL ["00:00:00" if NOT NULL]
Date & Time {4 bytes}	TIMESTAMP	19700101000000 - 2037+	Global Only (YYYYMMDDhhmmss)	Current Date & Time
Date & Time {1 bytes}	YEAR	1900 - 2155	Global Only (YYYY)	NULL ["0000" if NOT NULL]

MS-SQL: Data Types

2008-05-03T12:00:00+10:00

Data Types	Description
bigint	Integer data from -2^63 through 2^63-1
int	Integer data from -2^31 through 2^31 - 1
smallint	Integer data from -2^15 through 2^15 - 1
tinyint	Integer data from 0 through 255
bit	Integer data with either a 1 or 0 value
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money	Monetary data values from -2^63 through 2^63 - 1
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647
float	Floating precision number data from -1.79E + 308 through 1.79E + 308
real	Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
char	Fixed-length character data with a maximum length of 8,000 characters
varchar	Variable-length data with a maximum of 8,000 characters
text	Variable-length data with a maximum length of 2^31 - 1 characters
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary	Fixed-length binary data with a maximum length of 8,000 bytes
varbinary	Variable-length binary data with a maximum length of 8,000 bytes
image	Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor	A reference to a cursor
sql_variant	A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant
table	A special data type used to store a result set for later processing
timestamp	A database-wide unique number that gets updated every time a row gets updated
uniqueidentifier	A globally unique identifier