Batching Techniques for SQL Database Applications in Azure

Batching operations can be very helpful in improving your performance and scalability for your in house application as well as cloud applications.

Here are some of the batching techniques available for you to consider:

Transactions

Client side code based transactions can be simple and useful. Here is an example:

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("My.ConnectionString")))

{

    conn.Open();

    SqlTransaction transaction = conn.BeginTransaction();

    foreach (string commandString in dbOperations)    {

        SqlCommand cmd = new SqlCommand(commandString, conn, transaction);

        cmd.ExecuteNonQuery();

    }

    transaction.Commit();

}

Table-Valued Parameters

In code, you create a DataTable with the exact same names and types of the table type. Pass this DataTable in a parameter in a text query or stored procedure call. The following example shows this technique:

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))

{

    connection.Open();

    DataTable table = new DataTable();

    // Add columns and rows. The following is a simple example.

    table.Columns.Add("mytext", typeof(string));

    table.Columns.Add("num", typeof(int));   

    for (var i = 0; i < 10; i++)

    {

        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);

    }

    SqlCommand cmd = new SqlCommand(

        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",

        connection);               

    cmd.Parameters.Add(

        new SqlParameter()

        {

            ParameterName = "@TestTvp",

            SqlDbType = SqlDbType.Structured,

            TypeName = "MyTableType",

            Value = table,

        });

    cmd.ExecuteNonQuery();

}

SQL Bulk Copy

SQL bulk copy is another way to insert large amounts of data into a target database. .NET applications can use the SqlBulkCopy class to perform bulk insert operations. SqlBulkCopy is similar in function to the command-line tool, Bcp.exe, or the Transact-SQL statement, BULK INSERT. The following code example shows how to bulk copy the rows in the source DataTable, table, to the destination table in SQL Server, MyTable.

Here is an example:

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString"))){
    connection.Open();
 
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "MyTable";
        bulkCopy.ColumnMappings.Add("mytext", "mytext");
        bulkCopy.ColumnMappings.Add("num", "num");
        bulkCopy.WriteToServer(table);
    }
}

 

Multiple-row Parameterized INSERT statements

One alternative for small batches is to construct a large parameterized INSERT statement that inserts multiple rows. The following code example demonstrates this technique.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open(); 
    string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
        "VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)"; 
    SqlCommand cmd = new SqlCommand(insertCommand, connection); 
    for (int i = 1; i <= 10; i += 2)
    {        
                cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
        cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
    } 
    cmd.ExecuteNonQuery();
}

 

DataAdapter

The DataAdapter class allows you to modify a DataSet object and then submit the changes as INSERT, UPDATE, and DELETE operations. If you are using the DataAdapter in this manner, it is important to note that separate calls are made for each distinct operation. To improve performance, use the UpdateBatchSize property to the number of operations that should be batched at a time.

 

XML

The use of XML has no advantages over other methods and several disadvantages. The approach is similar to table-valued parameters, but an XML file or string is passed to a stored procedure instead of a user-defined table. The stored procedure parses the commands in the stored procedure.

There are several disadvantages to this approach:

  1. Working with XML can be cumbersome and error prone.
  2. Parsing the XML on the database can be CPU-intensive.
  3. In most cases, this method is slower than table-valued parameters.

 

Reference:

Batching Techniques for SQL Database Applications in Azure