Могу ли я увеличить скорость выполнения заявлений?

Моя функция: удаляет единицу в определенной строке данной таблицы

private static void removeUnits(String connectionString, String tableName, String columnID, String columnToFix)
{
    List<String> rowsToEdit = new List<String>();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "SELECT " + columnID + "," + columnToFix + " FROM " + tableName;
            connection.Open();
            using (var reader = command.ExecuteReader())
            {

                var indexOfR_MEASUREDVALUEID = reader.GetOrdinal(columnID);
                var indexOfT_VALUE = reader.GetOrdinal(columnToFix);
                while (reader.Read())
                {
                    var t_value = reader.GetValue(indexOfT_VALUE);
                    var t_id = reader.GetValue(indexOfR_MEASUREDVALUEID);

                    String newValue = getWithoutUnit(t_value.ToString());
                    if (newValue != null)
                    {
                        String sql = "UPDATE " + tableName + " SET " + columnToFix + "='" +
                            newValue + "' WHERE " + columnID + "='" + t_id + "';";
                        rowsToEdit.Add(sql);
                    }
                }
                connection.Close();
            }
        }

        Console.WriteLine("start writing " + rowsToEdit.Count + " entries?");
        Console.ReadLine();

        SqlCommand sqlCmd;
        sqlCmd = new SqlCommand("", connection);
        sqlCmd.Connection.Open();

        foreach (String command in rowsToEdit)
        {
            sqlCmd.CommandText = command;
            sqlCmd.ExecuteNonQuery();
        }
    }
    Console.WriteLine(rowsToEdit.Count + " commands executed");
}

Я использую C# в Visual Studio 2010 и SQL-Server 2012. Он работает нормально, но выполнение 200000 строк занимает очень много времени. Возможно ли сделать это быстрее?

2 ответа

Решение

Сначала попробуйте использовать параметры, как уже упоминалось в комментариях. Во-вторых, используйте оператор SqlCommand.Prepare(). Фактическая скорость также зависит от производительности вашей машины (HD Write Speed ​​/RAM). Вот пример (вам, возможно, придется редактировать SqlDbTypes в соответствии с вашими типами Colum)

    private static void RemoveUnits(string connectionString, string tableName, string columnID, string columnToFix)
    {
        Dictionary<int, string> rowsToEdit = new Dictionary<int, string>();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT " + columnID + "," + columnToFix + " FROM " + tableName;

                using (var reader = command.ExecuteReader())
                {
                    var indexOfR_MEASUREDVALUEID = reader.GetOrdinal(columnID);
                    var indexOfT_VALUE = reader.GetOrdinal(columnToFix);

                    while (reader.Read())
                    {
                        string t_value = reader.GetString(indexOfT_VALUE);
                        int t_id = reader.GetInt32(indexOfR_MEASUREDVALUEID);

                        string newValue = getWithoutUnit(t_value);
                        if (newValue != null)
                        {
                            // save values in dictionary
                            rowsToEdit[t_id] = newValue;
                        }
                    }
                }
            }

            Console.WriteLine("start writing " + rowsToEdit.Count + " entries?");
            Console.ReadLine();

            string updateCmd = "UPDATE " + tableName + " SET " + columnToFix + "= @Value WHERE " + columnID + "= @Key;";

            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                SqlCommand sqlCmd = connection.CreateCommand();
                sqlCmd.CommandText = updateCmd;
                sqlCmd.Parameters.Add("@Value", System.Data.SqlDbType.NVarChar, -1);
                sqlCmd.Parameters.Add("@Key", System.Data.SqlDbType.Int);

                // important for performance
                sqlCmd.Prepare();

                foreach (var update in rowsToEdit)
                {
                    // change values of parameters
                    sqlCmd.Parameters["@Key"].Value = update.Key;
                    sqlCmd.Parameters["@Value"].Value = update.Value;

                    // and execute it
                    sqlCmd.ExecuteNonQuery();
                }

                transaction.Commit();
            }
        }
        Console.WriteLine(rowsToEdit.Count + " commands executed");
    }

Редактировать: если getWithoutUnit(string) делает что-то не очень сложное, вы, вероятно, можете выполнить свою операцию с помощью одного оператора, используя TSQL, который поставляется с различными возможностями для работы со строками. подобно UPDATE TableName SET StringColumn = <string manipulation TSQL here>см. http://msdn.microsoft.com/en-us/library/ms181984.aspx

Edit2: добавлена ​​транзакция из подсказки к комментарию (также должна быть быстрее), строго типизированные столбцы

Мое предложение предполагает изучение параллелизма:

Примечание: код не тестировался, просто набрал его в Notepad ++

private static void removeUnits(String connectionString, String tableName, String  columnID, String columnToFix)
{
List<new Tuple<object, object>> rowsToEdit = new List<new Tuple<object, object>>();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT " + columnID + "," + columnToFix + " FROM " + tableName;
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            var indexOfR_MEASUREDVALUEID = reader.GetOrdinal(columnID);
            var indexOfT_VALUE = reader.GetOrdinal(columnToFix);

            while (reader.Read())
            {
                rowsToEdit.Add(new Tuple<object, object>(reader.GetValue(indexOfT_VALUE),reader.GetValue(indexOfR_MEASUREDVALUEID)));
            }
            connection.Close();
        }
    }
}           

// Use parallelism here
Parallel.Foreach(rowsToEdit, currentRow =>
{
    String newValue = getWithoutUnit(currentRow.Value1.ToString());
    if (newValue != null)
    {
        // reopen connection
        // use parameters here, and call SP
    }
}

Console.WriteLine("start writing " + rowsToEdit.Count + " entries?");
Console.ReadLine();

}

Хранимый процесс:

Create StoredProcedure MySP
(
@tablename varchar(50),
@columnToFix varchar(50),
@newValue varchar(50),
@columnID varchar(50),
@tID varhcar(varchar(50)
)
As
Begin
Declare @MySQL varchar(500)
Set @MySQL = 'Update ' + @tableName + ' Set ' + @columnToFix + ' = '' + @newValue + ''' + ' Where ' + @columnID + ' = '' + @tID + '''
sp_executesql @MySQL

End

Важно отметить, что динамический sql обычно не одобряется. Вот ссылка на обсуждение динамического SQL (плюсы / минусы): http://www.sommarskog.se/dynamic_sql.html

Также возможно, что вы можете столкнуться с некоторыми проблемами блокировки / конфликта, потому что вы попали в одну таблицу (на основе входных параметров функции).

Другие вопросы по тегам