Performance Issue in SqlBulkCopy while updating Multiple Table

Question

Hello Team,

Performance Issue in SqlBulkCopy c#

await BulkHelper.PerAsync(Datas, connection, transaction);
await BulkHelper.PerAsync(Limits, connection, transaction);          
await BulkHelper.PerAsync(variables, connection, transaction);           
await BulkHelper.PerAsync(rValues, connection, transaction);           
await BulkHelper.PerAsync(traResults, connection, transaction);           
await BulkHelper.PerAsync(resLimits, connection, transaction);

"Datas" table is chained to "Limits" table and so on : Based on "Datas" table "Limits" Table will be updated.

 public static async Task PerAsync<T>(IEnumerable<T> items, SqlConnection destinationConnection, IDbTransaction transaction)
        {
            ReflectionHelper.PrepareForType<T>();

            string tableName = ReflectionHelper.GetTableNameForType<T>();

            string tempTableName = $"dbo.#{tableName}";

            DataTable dt = DataTableHelper.CreateDataTable<T>();

            ConvertToDatatable(items, dt);

            if (destinationConnection.State == ConnectionState.Closed)
            {
                destinationConnection.Open();
            }

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction as SqlTransaction))
            {
                var command = new SqlCommand
                {
                    Connection = destinationConnection,
                    CommandType = CommandType.Text,
                    CommandText = $"SELECT TOP(0) * INTO {tempTableName} FROM {tableName};",
                    Transaction = transaction as SqlTransaction
                };

                await command.ExecuteNonQueryAsync();

                bulkCopy.DestinationTableName = tempTableName;

                foreach (DataColumn col in dt.Columns)
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
                }

                await bulkCopy.WriteToServerAsync(dt);

                var updateQuery = DataTableHelper.GetUpdateQuery(typeof(T), tempTableName, tableName);
                var insertQuery = DataTableHelper.GetInsertQuery(typeof(T), tempTableName, tableName);
                var dropQuery = $"DROP TABLE {tempTableName}";
                var updateCommand = transaction != null ? new SqlCommand(updateQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(updateQuery, destinationConnection);
                updateCommand.CommandType = CommandType.Text;

                var insertCommand = transaction != null ? new SqlCommand(insertQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(insertQuery, destinationConnection);
                insertCommand.CommandType = CommandType.Text;

                var dropCommand = transaction != null ? new SqlCommand(dropQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(dropQuery, destinationConnection);
                dropCommand.CommandType = CommandType.Text;

                await insertCommand.ExecuteNonQueryAsync();
                await updateCommand.ExecuteNonQueryAsync();
                await dropCommand.ExecuteNonQueryAsync();
            }
        }

        private static void ConvertToDatatable<T>(IEnumerable<T> data, DataTable dt)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            foreach (var item in data)
            {
                var dr = dt.NewRow();
                foreach (DataColumn dataColumn in dt.Columns)
                {
                    var colData = props[dataColumn.Caption].GetValue(item);
                    dr[dataColumn] = colData ?? DBNull.Value;
                }

                dt.Rows.Add(dr);
            }
        }
    }

Requirement : Need to better solution to update, delete, Create without Concurrency.
and Also need in Parallel Excecution.

Answer 1

@Indudhar Gowda ，Based on my research, you could try to use Task.WhenAll to solve the performance issue in SqlBulkCopy.
The link Using async/await for multiple tasks describes that it will perform your operations asynchronously in Parallel.

Therefore, we could try the following code to solve the problem.

await Task.WhenAll(BulkHelper.PerAsync(Datas, connection, transaction),BulkHelper.PerAsync(Limits, connection, transaction),.....);     

Hope the above solution could help you.

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.