May 4, 2008

Multiple Active Result Sets - Yet another powerful feature of SQL Server 2005

MARS [Multiple Active Result Sets ] is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

string strConn = "Data Source=[DATASOURCE];Initial Catalog=[DATABASE];User ID=[UID];Password=[PWD];MultipleActiveResultSets=true";
string strSql = "select DoctorId, PatientId from [Patient] where DoctorId = {0}";
string strOutput = "<br/>DoctorId:{0} - PatientId{1}";

using (SqlConnection con = new SqlConnection(strConn))
//Opening Connection

//Creating two commands form current connection
SqlCommand cmd1 = con.CreateCommand();
SqlCommand cmd2 = con.CreateCommand();

//Set the comment type
cmd1.CommandType = CommandType.Text;
cmd2.CommandType = CommandType.Text;

//Setting the command text to first command
cmd1.CommandText = "select distinct DoctorId from [Doctor] where HospitalId = 8";

//Execute the first command
IDataReader idr1 = cmd1.ExecuteReader();

while (idr1.Read())
//Read the first doctor from data source
int intDoctorId = idr1.GetInt32(0);

//create another command, which get patients of doctor
cmd2.CommandText = string.Format(strSql, intDoctorId);

//Execute the reader
IDataReader idr2 = cmd2.ExecuteReader();

while (idr2.Read())
//Read the doctor and patient
Response.Write(string.Format(strOutput, idr2.GetInt32(0), idr2.GetInt32(1)));
//Dont forgot to close second reader, this will just close reader not connection

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.


ravi said...

good one, thanks

vbam said...

Thanks a lot imran. keep up the good work..

Prashant Ghantiwala said...

Thnkx imran..

D Carr said...

Glad to have found this via StackOverflow. Thx!