Могу ли я увеличить скорость выполнения заявлений?
Моя функция: удаляет единицу в определенной строке данной таблицы
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
Также возможно, что вы можете столкнуться с некоторыми проблемами блокировки / конфликта, потому что вы попали в одну таблицу (на основе входных параметров функции).