Lesson 2: Querying with Multiple Active Result Sets

Lesson 2: Querying with Multiple Active Result Sets

Estimated lesson time: 45 minutes

Multiple Active Result Sets is a new feature introduced with SQL Server 2005 that enables you to execute multiple statements through a single connection. MARS uses a connection attribute to indicate whether the connection can support this type of processing. Prior to this capability, you had to complete one batch before another one could be started. MARS enables you to execute one or more statements as you loop through the results of a separate result set-all within the same connection.

You might be thinking that MARS enables you to perform asynchronous or parallel processing, but this is not true. MARS enables you to interleave multiple result sets while the default result set remains open. MARS addresses a specific concern among developers that would receive transaction isolation level locks in their applications. MARS avoids this locking by enabling you to have multiple SqlDataReaders open on a single connection. In situations where transactions are used and multiple result sets must be processed, MARS can avoid the locking that might occur.

MARS is ideal for a situation where you need to perform some operation as a set of results being read. For example, consider the following pseudo code, which is code represented as real English:

 Execute SELECT statement and return results to a DataReader Loop through the DataReader    Perform UPDATE to data from the table in the SELECT statement End Loop 

MARS is not a good idea when you have results that will be processed slowly. This can happen when the processing is dependent on user interaction or the processing of some external process. It might take a long time for the processing to occur, and this could cause an unnecessary performance penalty when using MARS. In these cases, using a server-side cursor would be a better alternative.

Upon learning about MARS, do not be tempted to convert all existing server-side cursors to use MARS. It might be possible that the server-side cursor is still the faster alternative. The lab in this lesson will demonstrate that a server-side cursor is several times faster than the MARS alternative. MARS was not intended to provide a performance advantage; it simply provides another option for developers and enables them to avoid problems with locking.

Using MARS

MARS can be utilized with the SQL Native Client Provider or with the SqlClient for the .NET Provider. For more information about the SQL Native Client Provider, refer to Lesson 1, "Designing Data Access Technologies" in Chapter 1, "Designing a Data Access Strategy." By default, MARS is not enabled, so you will have to enable it specifically. The following lists how it is enabled with each provider:

• SQL Native Client using the OLEDB Provider MARS is enabled through the SSPROP_INIT_MARSCONNECTION property from the DBPROPSET_SQLSERVERDBINIT property set. Alternatively, you can include the MarsConn=yes/no keyword in the connection string if you are using the SQL Native Client through ADO. The code that specifies the connection string would look similar to the following:

   //C# ADODB.Connection conn = new ADODB.Connection(); String connStr = "Provider=SQLNCLI;" +             @"Server=.\SQL2005STD;" +             "Database=AdventureWorks;" +             "Integrated Security=SSPI;" +             "DataTypeCompatibility=80;" +             "MarsConn=yes;"; conn.Open(connStr, null, null, 0); //Do some processing conn.Close(); 'VB Dim conn As New ADODB.Connection Dim connStr As String = "Provider=SQLNCLI;" + _             "Server=.\SQL2005STD;" + _             "Database=AdventureWorks;" + _             "Integrated Security=SSPI;" + _             "DataTypeCompatibility=80;" + _             "MarsConn=Yes;" conn.ConnectionString = connStr conn.Open() 'Do some processing conn.Close() 

• SQL Native Client using the ODBC Provider MARS is enabled through the SQLSetConnectAttr and SqlGetConnectAttr functions. Alternatively, you can set include the MARSCONNECTION=true/false keyword in the connection string if you are using the SQL Native Client through ADO. The code that specifies the connection string would look similar to the following:

   //C# ADODB.Connection conn = new ADODB.Connection(); String connStr = "Provider=SQLNCLI;" +             @"Server=.\SQL2005STD;" +             "Database=AdventureWorks;" +             "Integrated Security=SSPI;" +             "DataTypeCompatibility=80;" +             "MARS Connection=True;"; conn.Open(connStr, null, null, 0); //Do some processing conn.Close(); 'VB Dim conn As New ADODB.Connection Dim connStr As String = "Provider=SQLNCLI;" + _             "Server=.\SQL2005STD;" + _             "Database=AdventureWorks;" + _             "Integrated Security=SSPI;" + _             "DataTypeCompatibility=80;" + _             "MARS Connection=True;" conn.ConnectionString = connStr conn.Open() 'Do some processing conn.Close() 

• SqlClient .NET Provider You can use the System.Data.SqlClient namespace with the .NET Framework to specify whether MARS is enabled. To use this feature, you include the MultipleActiveResultSets = True/False keyword in the connection string. The following code could be used to enable MARS for the SqlClient:

   //C# SqlConnection conn = new SqlConnection(); String connStr = @"Data Source=.\SQL2005STD;" +        "Initial Catalog=AdventureWorks;" +        "Integrated Security=SSPI;" +        "MultipleActiveResultSets=True;"; conn.ConnectionString = connStr; conn.Open(); //Do some processing conn.Close(); 'VB    Dim conn As New SqlConnection    Dim connStr As String = "Data Source=.\SQL2005STD;" + _        "Initial Catalog=AdventureWorks;" + _        "Integrated Security=SSPI;" + _        "MultipleActiveResultSets=True;"    conn.ConnectionString = connStr    conn.Open()    'Do some processing    conn.Close() 

Using Transactions

The .NET Framework 2.0 provides a TransactionScope class, which is part of the System.Transactions namespace. This class enables you to easily wrap code that should execute within a single transaction inside a using statement. The code block will implicitly enlist all the commands within the block as part of a single transaction. You just have to call the Complete method at the end of the code block to signify that the transaction is finished.

For example, the following code can be used to wrap two commands within a single transaction:

 //C# using (TransactionScope ts = new TransactionScope()) {       //Connect to the Database and enable MARS       SqlConnection conn = new SqlConnection();       String connStr = @"Data Source=.\SQL2005STD;" +         "Initial Catalog=AdventureWorks;" +         "Integrated Security=SSPI;" +         "MultipleActiveResultSets=True;";       conn.ConnectionString = connStr;       conn.Open();       //Define our UPDATE statements       SqlCommand cmd1 = new SqlCommand();       SqlCommand cmd2 = new SqlCommand();       cmd1.CommandText = "UPDATE Person.Contact SET MiddleName = 'G'" +           " where contactid = 1";       cmd1.Connection = conn;       //This command will use the same connection       cmd2.CommandText = "UPDATE Person.Contact SET MiddleName = 'F'" +           " where contactid = 2";       cmd2.Connection = conn;       try       {           //Execute the commands           cmd1.ExecuteNonQuery();           cmd2.ExecuteNonQuery();       }       catch       {           //Do something with the exception       }       finally       {           conn.Close();           ts.Complete();           cmd1 = null;           cmd2 = null;           conn = null;       }    } 'VB Using ts As New TransactionScope()       'Connect to the Database and enable MARS      Dim conn As New SqlConnection()      Dim connStr As String = "Data Source=.\SQL2005STD;" + _            "Initial Catalog=AdventureWorks;" + _            "Integrated Security=SSPI;" + _            "MultipleActiveResultSets=True;"      conn.ConnectionString = connStr      conn.Open()      'Define our UPDATE statements      Dim cmd1 As New SqlCommand()      Dim cmd2 As New SqlCommand()      cmd1.CommandText = "UPDATE Person.Contact SET MiddleName = 'G'" + _          " where contactid = 1"      cmd1.Connection = conn      'This command will use the same connection       cmd2.CommandText = "UPDATE Person.Contact SET MiddleName = 'F'" + _          " where contactid = 2"       cmd2.Connection = conn      Try         'Execute the commands         cmd1.ExecuteNonQuery()         cmd2.ExecuteNonQuery()      Catch ex As Exception         'Do something with the exception      Finally          conn.Close()          ts.Complete()          cmd1 = Nothing          cmd2 = Nothing          conn = Nothing     End Try End Using 

Important 

Hotfix available

When working with transactions in SQL Server 2005 and the SqlClient class, it is possible you could receive the error "New request is not allowed to start because it should come with valid transaction descriptor." The error occurs randomly, but there is a hotfix available to resolve this issue. The hotfix is available at http://support.microsoft.com/kb/916002.

Lab: Creating MARS Connections

Lab 2 contains one exercise in which you create a Windows-based application that is used to query and perform updates on a SQL Server 2005 database. The application will enable you to compare the results of using a MARS connection and a single connection versus two connections.

The completed code examples, in both Visual Basic and C#, are available in the \Labs\Chapter 04 folder on the companion CD.

Important 

Lab requirements

You need to have SQL Server 2005 installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Compare Execution of Queries With and Without MARS

In this exercise, you create a simple Windows application that has two buttons. The first button will perform a query of all contacts and then loop through the results as it updates the e-mail address using the same connection. It is able to do this by using a MARS connection. The second button will perform the same function but will use two connections. You can then compare the execution time of both methods.

The code example for this exercise was borrowed from the cursor example used in Lesson 3 of Chapter 3. In that lesson, it was determined that the cursor was not the most efficient alternative. The example used in this exercise is also not the most efficient method of updating the records, but it demonstrates how to use a MARS connection and enables you to compare the execution times of using MARS and not using MARS.

1. Open Microsoft Visual Studio 2005.

2. If you completed the practice from Lesson 1, skip to step 5.

3. Click File, New, Project.

4. In the New Project dialog box, expand the Other Project Types node, and select Visual Studio Solutions. Type TK442Chapter4 for the name of your blank solution, and place it in a directory of your choosing. A new solution file will be created, and you can add multiple projects to this solution. You will add one project for each lab included in this chapter.

5. Select File, Add, New Project. Select Windows Application as the template, and type Lab2 as the project name. Set the language by selecting Visual Basic, Visual C#, or Visual J# from the language drop-down list box. By default, Visual Studio selects the language specified when it was first configured.

6. From the Toolbox, drag one label control onto the Default design surface. Use the following property value for this control:

    Name = lblInstance Text = "Server Instance: " 

7. From the Toolbox, drag one textbox control onto the Default design surface. Use the following property value for this control:

    Name = txtInstance 

8. From the Toolbox, drag two button controls onto the Default design surface. Use the following property values for these controls:

    Control 1: Name = btnWithMARS Text = "Run Query with MARS" Control 2: Name = btnWithoutMARS Text = "Run Query without MARS" 

9. Right-click the Form1 file from Solution Explorer, and select View Code. Paste the following code at the top of the file:

    //C# using System.Data.SqlClient; 'VB Imports System.Data.SqlClient 

10. From the same code window, paste the following code beneath the Form1_Load method:

     //C# private void btnWithMARS_Click(object sender, EventArgs e)    {             //Change the cursor to a wait cursor and get start time             this.Cursor = Cursors.WaitCursor;             DateTime startTime = DateTime.Now;             //Connect to the Database and enable MARS             SqlConnection conn = new SqlConnection();             String connStr = @"Data Source=" + txtInstance.Text + ";" +                 "Initial Catalog=AdventureWorks;" +                 "Integrated Security=SSPI;" +                 "MultipleActiveResultSets=True;";             conn.ConnectionString = connStr;             conn.Open();             //Get a SqlDataReader that contains contact records             SqlCommand cmd1 = new SqlCommand("SELECT ContactID, " + "EmailAddress, EmailPromotion " +                    "FROM Person.Contact", conn);             SqlDataReader dr = cmd1.ExecuteReader();          //Define our UPDATE statements          SqlCommand cmd2 = new SqlCommand();          cmd2.Connection = conn;          try          {            while (dr.Read())            {             if (dr.GetInt32(2) == 0)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.net')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }             if (dr.GetInt32(2) == 1)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.biz')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }             if (dr.GetInt32(2) == 2)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.org')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }              //Execute the command              cmd2.ExecuteNonQuery();          }        }        catch (Exception ex)        {          MessageBox.Show("The following message was encountered: " +  ex.Message);        }        finally        {               conn.Close();               cmd1 = null;               cmd2 = null;               conn = null;        }        //Change the cursor back to default and get end time        this.Cursor = Cursors.Default;       DateTime endTime = DateTime.Now;       TimeSpan ts = endTime.Subtract(startTime);       MessageBox.Show("The queries took " +            ts.Minutes + ":" + ts.Seconds + " to complete");  }  private void btnWithoutMARS_Click(object sender, EventArgs e)  {       //Change the cursor to a wait cursor and get start time       this.Cursor = Cursors.WaitCursor;       DateTime startTime = DateTime.Now;       //Connect to the Database with the first connection       SqlConnection conn1 = new SqlConnection();       String connStr = @"Data Source=" + txtInstance.Text + ";" +         "Initial Catalog=AdventureWorks;" +         "Integrated Security=SSPI;";       conn1.ConnectionString = connStr;       conn1.Open();          //Get a SqlDataReader that contains contact records          SqlCommand cmd1 = new SqlCommand("SELECT ContactID, " + "EmailAddress, EmailPromotion " +                    "FROM Person.Contact", conn1);       SqlDataReader dr = cmd1.ExecuteReader();       //Define our UPDATE statements and create a second connection       SqlConnection conn2 = new SqlConnection(connStr);       conn2.Open();       SqlCommand cmd2 = new SqlCommand();       cmd2.Connection = conn2;       try       {         while (dr.Read())           {             if (dr.GetInt32(2) == 0)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.net')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }             if (dr.GetInt32(2) == 1)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.biz')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }             if (dr.GetInt32(2) == 2)             {               cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " +                  "REPLACE('" + dr.GetString(1) + "','adventure-works.com', " +   " +'adventure-works.org')" +                  "WHERE ContactID = " + dr.GetInt32(0);              }             //Execute the command             cmd2.ExecuteNonQuery();        }      }      catch (Exception ex)      {          MessageBox.Show("The following message was encountered: " +  ex.Message);      }      finally      {          conn1.Close();          conn2.Close();          cmd1 = null;          cmd2 = null;          conn1 = null;          conn2 = null;      }      //Change the cursor back to default and get end time      this.Cursor = Cursors.Default;      DateTime endTime = DateTime.Now;      TimeSpan ts = endTime.Subtract(startTime);      MessageBox.Show("The queries took " +            ts.Minutes + ":" + ts.Seconds + " to complete");  } 'VB Private Sub btnWithMars_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWithMars.Click         'Change the cursor to a wait cursor and get start time         Me.Cursor = Cursors.WaitCursor         Dim startTime As System.DateTime         startTime = Now()         'Connect to the Database and enable MARS         Dim conn As New SqlConnection         Dim connStr As String = "Data Source=" + txtInstance.Text + ";" + _             "Initial Catalog=AdventureWorks;" + _             "Integrated Security=SSPI;" + _             "MultipleActiveResultSets=True;"         conn.ConnectionString = connStr         conn.Open()         'Get a SqlDataReader that contains contact records         Dim cmd1 As New SqlCommand("SELECT ContactID, " + _             "EmailAddress, EmailPromotion " + _             " FROM Person.Contact", conn)         Dim dr As SqlDataReader = cmd1.ExecuteReader         'Define our UPDATE statements         Dim cmd2 As New SqlCommand         cmd2.Connection = conn         Try          While dr.Read            If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.net')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If            If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.biz')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If           If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.org')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If           End While          'Execute the command          cmd2.ExecuteNonQuery()       Catch ex As Exception          MessageBox.Show("The following message was encountered: " + _                ex.Message)       Finally           conn.Close()           cmd1 = Nothing           cmd2 = Nothing           conn = Nothing       End Try         'Change the cursor back to default and get end time         Me.Cursor = Cursors.Default         Dim endTime As DateTime         endTime = Now()         Dim ts As TimeSpan = endTime.Subtract(startTime)         MessageBox.Show("The queries took " + _             ts.Minutes + ":" + ts.Seconds + " to complete")     End Sub Private Sub btnWithoutMARS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWithoutMARS.Click         'Change the cursor to a wait cursor and get start time         Me.Cursor = Cursors.WaitCursor         Dim startTime As System.DateTime         startTime = Now()         'Connect to the Database with the first connection         Dim conn1 As New SqlConnection         Dim connStr As String = "Data Source=" + txtInstance.Text + ";" + _             "Initial Catalog=AdventureWorks;" + _             "Integrated Security=SSPI;"         conn1.ConnectionString = connStr         conn1.Open()         'Get a SqlDataReader that contains contact records         Dim cmd1 As New SqlCommand("SELECT ContactID, " + _             "EmailAddress, EmailPromotion " + _             " FROM Person.Contact", conn1)         Dim dr As SqlDataReader = cmd1.ExecuteReader         'Define our UPDATE statements and create a second connection         Dim conn2 As New SqlConnection(connStr)         conn2.Open()         Dim cmd2 As New SqlCommand         cmd2.Connection = conn2         Try             While dr.Read            If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.net')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If            If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.biz')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If           If dr.GetInt32(2) = 0 Then             cmd2.CommandText = "UPDATE Person.contact SET emailaddress = " + _               "REPLACE('" + dr.GetString(1) + "', 'adventure-works.com', " + _               "'adventure-works.org')" + _               "WHERE ContactID = " + dr.GetInt32(0)            End If           End While          'Execute the command          cmd2.ExecuteNonQuery()      Catch ex As Exception         MessageBox.Show("The following message was encountered: " + _               ex.Message)      Finally          conn1.Close()          conn2.Close()          cmd1 = Nothing          cmd2 = Nothing          conn1 = Nothing          conn2 = Nothing      End Try         'Change the cursor back to default and get end time         Me.Cursor = Cursors.Default         Dim endTime As DateTime         endTime = Now()         Dim ts As TimeSpan = endTime.Subtract(startTime)         MessageBox.Show("The queries took " + _             ts.Minutes + ":" + ts.Seconds + " to complete")     End Sub 

11. Save the Lab2 project by going to File and clicking Save All.

12. Right-click the Lab2 project from Solution Explorer, and select Set As Startup Project.

13. Press Ctrl+F5 to build the project without debugging. Ensure that the project builds successfully. Form1 should appear after the project compiles and executes. You should see two buttons and a text box. Type the name of the instance of SQL Server 2005 in which you have the AdventureWorks database installed.

14. Click the Run Query With MARS button. The cursor should change to an hourglass, and the process might take over a minute to run. When complete, a message box displays the amount of time it took to execute. Make a note of this time. Keep in mind that the code for this test performs the same function that the inefficient cursor from Lab 3 of Chapter 3 used. In that lab, the cursor took approximately 18 seconds to complete. In one test, this code took over a minute to complete. In this case, the server-side cursor performed better than the MARS queries.

15. You can now click the Run Query Without MARS button. This set of code accomplishes the same task, but it uses two connections instead of one. The code should take approximately the same amount of time as the MARS alternative to execute. No performance advantage is achieved by using one connection versus two.