. NET execute SQL performance optimization I: execute SQL statements in batches for SQL Server

This paper introduces several techniques for executing multiple SQL statements with one SqlCommand.

introduce

One of the functions that are often ignored in data storage of SQL Server using ADO.NET is that it can execute multiple SQL statements SqlCommand with a single statement. In general, programs execute statements and / or call stored procedures to execute larger statements, respectively. Of course, using stored procedures is a preferred method, but in some cases, it's useful to execute multiple statements at one call. This can be done using batch processing, which basically means a set of SQL or T-SQL statements together.

Set up

To test the functionality, let's have a database table.

  • Create test table
  • CREATE TABLE MultiStatementTest (
    id        int not null identity(1,1),
    somevalue int not null
    );

    And fill it with a few lines.

  • Add a few lines
  • DECLARE @counter int = 1
    BEGIN
    WHILE (@counter <= 5) BEGIN
      INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 1000);
      SET @counter = @counter + 1;
    END;
    END;

    Now the data looks like:

  • Query initial data
  • SELECT * FROM MultiStatementTest;
    id somevalue

    1 854
    2 73
    3 732
    4 546
    5 267

    Test program

    The test program is easy to use. Just define the correct connection string for the database that created the test table and you can start running the test.

Execute multiple SQL statements

The first variant is used by SqlCommand.ExecuteNonQuery to execute two separate SQL statements on the test table. The first one updates some value one, and the second one updates the field. The method is as follows:

/// <summary>
/// Executes two separate updates against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
public static bool ExecuteMultipleUpdates(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 int rowsAffected;

 connection.ConnectionString = connectionString;
 command.CommandText = @"
     UPDATE MultiStatementTest SET somevalue = somevalue + 1;
     UPDATE MultiStatementTest SET" + (generateError ? "WONTWORK" : "") + 
                   " somevalue = somevalue + 2;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    rowsAffected = command.ExecuteNonQuery();
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format("{0} rows updated", 
    rowsAffected, "Operation succesful"));

 return true;
}

Therefore, the CommandText property contains all statements that will be executed in this batch. Statements are separated by semicolons.

After the batch, the rows have been updated twice, so the contents of the table are similar to:

id somevalue

1857
2 76
3735
4549
5270
An important thing to note is that the number of affected rows returned, ExecuteNonQuery, is 10. There are five rows in the table, each of which is updated twice, so the total number of updates is 10. Therefore, even if there are batches, you can check that no matter which statement is updated, the correct number of rows will be updated.

Use Data Reader to execute two SELECT statements

The next test is to execute two different SELECT statements and read the results using a SqlDataReader class. The method is:

/// <summary>
/// Executes two separate select statements against the the connection using data reader
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
public static bool ExecuteReader(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 System.Data.SqlClient.SqlDataReader dataReader;
 System.Text.StringBuilder stringBuilder;
 bool loopResult = true;

 connection.ConnectionString = connectionString;
 command = new System.Data.SqlClient.SqlCommand();
 command.CommandText = @"
    SELECT somevalue FROM MultiStatementTest WHERE somevalue%2 = 1;
    SELECT somevalue FROM MultiStatementTest " + (generateError ? "WONTWORK" : "WHERE") + 
               " somevalue%2 = 0;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    dataReader = command.ExecuteReader();
    while (loopResult) {
       stringBuilder = new System.Text.StringBuilder();
       while (dataReader.Read()) {
          stringBuilder.AppendLine(dataReader.GetInt32(0).ToString());
       }
       System.Windows.MessageBox.Show(stringBuilder.ToString(), "Data from the result set");
       loopResult = dataReader.NextResult();
    }
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }

 return true;
}

The idea in batch processing is the same, with semicolons separating the two statements. In this example, the rows are divided into two result sets, depending on whether the numbers are odd or even. When ExecuteReader is called, the first result set is automatically available. This method traverses each row and displays the result:

857
735
549
In order to get the next result, you must instruct the reader to use the NextResult method to advance to the next result set. After that, the second set of values can be cycled through again. The second group of results:

76
270

Using SqlDataAdapter for multiple SELECT statements

SqlDataReader usually uses a DataSet if you want to store results in. For the next test, let's use the SqlDataAdapter to populate the DataSet. The code is as follows:

/// <summary>
/// Executes two separate select statements against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
public static bool ExecuteMultipleSelects(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
 System.Data.DataSet dataset = new System.Data.DataSet();

 connection.ConnectionString = connectionString;
 command = new System.Data.SqlClient.SqlCommand();
 command.CommandText = @"
     SELECT * FROM MultiStatementTest WHERE somevalue%2 = 1;
     SELECT " + (generateError ? "WONTWORK" : "*") + 
       " FROM MultiStatementTest WHERE somevalue%2 = 0;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    adapter.SelectCommand = command;
    adapter.Fill(dataset);
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format(
    "Dataset contains {0} tables, {1} rows in table 1 and {2} rows in table 2", 
    dataset.Tables.Count, 
    dataset.Tables[0].Rows.Count, 
    dataset.Tables[1].Rows.Count, 
    "Operation succesful"));

 return true;
}

Now, it's very easy to get data in this way. This code calls only the method of the Fill adapter and passes the DataSet parameter as a parameter. The adapter automatically creates two separate objects in the DataSet and populates them. In my test scenario, the first table contains three rows, and the second table contains two rows.

Because in this example, the table is created on the fly, Table1 is automatically named for it, and Table2 makes it wise to change the name to a more descriptive name if you use the name to refer to the table.

Execute anonymous T-SQL block

While stored procedures are excellent, sometimes T-SQL code can be very dynamic in nature. In this case, it may be difficult to create a stored procedure. Batch processing can also be used to execute a bunch of T-SQL statements. In this method, there are no named objects in the database, but batch processing is performed in the same way as it would be, for example, from SQL Server Management Studio.

The test code is as follows:

/// <summary>
/// Executes an anonymous T-SQL batch against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
public static bool ExecuteAnonymousTSql(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 int rowsAffected;

 connection.ConnectionString = connectionString;
 command.CommandText = @"
    DECLARE @counter int = 1
    BEGIN
     WHILE (@counter <= 5) BEGIN
     INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 100000);
     SET @counter = @counter + 1;
     " + (generateError ? "WONTWORK" : "") + @"
     END;
    END;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    rowsAffected = command.ExecuteNonQuery();
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format("{0} rows inserted", 
    rowsAffected, 
    "Operation succesful"));

 return true;
}

Now, in this example, you use the same script as you started to create several test lines. As you can see, variable declarations, loops, and so on are fully valid statements contained in a batch.

When you run the command, five additional rows are added to the table. Also note that because NOCOUNT is off by default, the ExecuteNonQuery method inserts the correct number of rows back into the batch. If NOCOUNT is set to on, the number of rows affected is - 1.

error handling

What if an error occurs while executing a single statement in a batch? I used some syntax errors to test this. The batch is analyzed as a whole, so it won't work even if later statements contain syntax errors. For example, if an UPDATE batch contains incorrect statements, the state of the table does not change.

If the error is not a syntax error but occurs during execution, the situation is different. Consider, for example, foreign key errors detected when an error value occurs. In this case, the previous statement may have changed the database state (depending on the statement), so it is recommended to use the appropriate transaction as usual.

conclusion

While batch processing does not (and should not) replace good old stored procedures, etc., they can be useful if used properly. As long as no client logic is required between calls, they can be used to create very dynamic operations without multiple round trips.

This article is translated from: Executing multiple SQL statements as one against SQL Server

Tags: ASP.NET SQL Windows Database Stored Procedure

Posted on Sun, 29 Mar 2020 10:12:46 -0700 by csueiras