Добрый день.
Хочу посоветоваться с уважаемыми коллегами по следующему вопросу.
Некоторое время назад передо мной была поставлена задача написать программу,
осуществляющую пакетное преобразование файлов из внутреннего
формата некоторого приложения X в БД MSSQL Server. Обстоятельства
функционирования таковы, что программа должна будет ежедневно "доливать"
в готовую базу примерно по 1 млн. новых записей.
Также было предложено реализовать все это на платформе .NET.
Поскольку до этого я программировал в основном под Win32 на C++, то и писать стал
на С++. Но вскоре, испытав отвращение ко всем наворотам, введенным в VC++ для поддержки
.NET, я решил, что лучше, проще и элегантнее написать это на C#.
Но так как многих деталей C# я еще не знаю, есть некоторые сложности.
Самый неясный момент для меня сейчас: как грамотно организовать обработку исключений
(и запись их в журнал!) при работе с SQL server и при этом освободить ресурсы (connection)
в случае ошибки?
Указанная процедура будет вызываться многократно и не должна выпускать исключение вовне.
Вот фрагмент кода, но что-то подсказывает мне, что он еще далек от идеала
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
//
// Записать все записи из памяти в БД одной транзакцией!
//
// В случае успеха все записи добавляются в таблицу cdr_records,
// а имя файла добавляется в таблицу cdr_files (COMMIT)
//
// В случае неудачи - ничего не происходит и делается запись в журнал (ROLLBACK)
//static void WriteRecordsToDB (
string shortFilename, // in: имя CDR-файла
List<CdrRecord> cdrRecordsList // in: массив записей, прочитанных из CDR-файла
)
{
try {
// вариант с using(...) из примера MSDNusing (SqlConnection connect = new SqlConnection (DBCONNECTION_STRING)) {
// установить соединение с SQL сервером - может бросить исключение!
connect.Open ();
SqlTransaction transaction = connect.BeginTransaction ("MyTransaction");
try {
SqlCommand cmd = connect.CreateCommand ();
cmd.Connection = connect;
cmd.Transaction = transaction;
cmd.CommandType = System.Data.CommandType.Text;
// записать все записи из массива в БДfor (int i = 0; i < cdrRecordsList.Count; ++i) {
CdrRecord rec = cdrRecordsList[ i ];
StringWriter strWriter = new StringWriter ();
strWriter.Write ("'{0}', '{1}', '{2}', '{3}', '{4}'",
rec.data_type, rec.field_a, rec.field_b, , rec.field_c, rec.field_d
);
cmd.CommandText = "INSERT INTO cdr_records " +
"(data_type, rec.field_a, rec.field_b, , rec.field_c, rec.field_d) " +
"VALUES (" + strWriter.ToString () + ")";
cmd.ExecuteNonQuery ();
}
// записать имя обработанного файла в БД
cmd.CommandText = "INSERT INTO cdr_files (fname) VALUES ('" + shortFilename + "')";
cmd.ExecuteNonQuery ();
// попытаться завершить транзакцию
transaction.Commit ();
}
catch (Exception ex) {
LogMessage ("WriteRecordsToDB(): " + shortFilename + " is not processed.", ex);
// попытать откатить транзакциюtry {
transaction.Rollback ();
}
catch (Exception ex2) {
LogMessage ("WriteRecordsToDB(): transaction.Rollback() failed.", ex2);
}
}
}
}
catch (Exception ex) {
// перехват connect.Open(), BeginTransaction()
LogMessage ("WriteRecordsToDB(): " + shortFilename + " is not processed.", ex);
}
}
Re: Грамотно обработать исключения при работе c SQL Server
Ну и нормально, ИМХО.
Только transaction.Rollback () не имело смысла в try брать.
Свалться тут теперь можно только в LogMessage() но это исключение надо перехватывать на более высоком уровне.
> Только transaction.Rollback () не имело смысла в try брать.
Кстати тут вопрос спорный если слишком большая транзакция(много
изменений) то выпадает по таймауту. Как с этим бороться ?
В команды есть таймаут я его в 0 ставлю когда слишком длинные запросы а
чт оделать с ролбеком ?
Posted via RSDN NNTP Server 2.0
Re[3]: Грамотно обработать исключения при работе c SQL Serve
>> Только transaction.Rollback () не имело смысла в try брать.
A>Кстати тут вопрос спорный если слишком большая транзакция(много A>изменений) то выпадает по таймауту. Как с этим бороться ? A>В команды есть таймаут я его в 0 ставлю когда слишком длинные запросы а A>чт оделать с ролбеком ?
Rollback нет смысла брать в try потому что он и так вылавливается на вышестоящем уровне.
ИМХО вместо явного использования метода SqlTransaction.Rollback, гораздо удобнее оборачивать транзакцию в блок using. По выходе из блока using, если транзакция не была commited она откатывается автоматически. По моему такой подход гораздо менее громоздок.
Если вы не сочтете это покушением на святое, я бы зделал еще следующие изменения:
1. Я бы использовал вместо динамческой генерации sql, параметры. Это спасает от всяких бяк типа sql injection + код в таком случае выглядит нагляднее + теоретическая прибавка к performance (ADO.NET исполняет все запросы через sp_executesql, которая умеет кешировать план запроса)
2. Все IDisposable переменные метода теоритически должны быть засунуты в using. Правда это не всегда имеет практический смысл, но хуже точно не будет .
И чуть-чуть по мелочи:
1. В C# для комментирования существуют уже готовые конструкции <summary>, <remarks>, <param>, <exception> и т.д. По-моему они удобнее + среда потом по ним еще и подсказки показывает.
2. Вместо for в данном случае, по-моему удобнее использовать foreach.
3. SqlCommand.CommandType property всегда по умолчанию CommandType.Text.
Короче у меня примерно так получилось:
/// <summary>
/// Записать все записи из памяти в БД одной транзакцией!
///
/// В случае успеха все записи добавляются в таблицу cdr_records,
/// а имя файла добавляется в таблицу cdr_files (COMMIT)
///
/// В случае неудачи - ничего не происходит и делается запись в журнал (ROLLBACK)
/// </summary>
/// <param name="shortFilename">имя CDR-файла</param>
/// <param name="cdrRecordsList">массив записей, прочитанных из CDR-файла</param>static void WriteRecordsToDB(string shortFilename, List<CdrRecord> cdrRecordsList)
{
try
{
// вариант с using(...) из примера MSDNusing (SqlConnection connect = new SqlConnection(DBCONNECTION_STRING))
using (SqlCommand cmd = new SqlCommand("INSERT INTO cdr_records (data_type, field_a, field_b, field_c, field_d) values (@data_type, @field_a, @field_b, @field_c, @field_d)", connect))
using (SqlCommand cmd2 = new SqlCommand("INSERT INTO cdr_files (fname) VALUES (@fname)"))
{
cmd.Parameters.Add("@data_type", SqlDbType.VarChar, 666);
cmd.Parameters.Add("@field_a", SqlDbType.VarChar, 666);
cmd.Parameters.Add("@field_b", SqlDbType.VarChar, 666);
cmd.Parameters.Add("@field_c", SqlDbType.VarChar, 666);
cmd.Parameters.Add("@field_d", SqlDbType.VarChar, 666);
cmd2.Parameters.Add("@fname", SqlDbType.VarChar, 666).Value = shortFilename;
// установить соединение с SQL сервером - может бросить исключение!
connect.Open();
using (SqlTransaction transaction = connect.BeginTransaction("MyTransaction"))
{
cmd.Transaction = transaction;
cmd2.Transaction = transaction;
// записать все записи из массива в БДforeach(CdrRecord rec in cdrRecordsList)
{
cmd.Parameters["data_type"].Value = rec.data_type;
cmd.Parameters["@field_a"].Value = rec.field_a;
cmd.Parameters["@field_b"].Value = rec.field_b;
cmd.Parameters["@field_c"].Value = rec.field_c;
cmd.Parameters["@field_d"].Value = rec.field_d;
cmd.ExecuteNonQuery();
}
cmd2.ExecuteNonQuery();
// попытаться завершить транзакцию
transaction.Commit();
}
}
}
catch (Exception ex)
{
// перехват connect.Open(), BeginTransaction()
LogMessage("WriteRecordsToDB(): " + shortFilename + " is not processed.", ex);
}
}
Re: Грамотно обработать исключения при работе c SQL Server
А сколько обычно записей заливается за один такой вызов процедуры? От ответа на этот вопрос многое зависит.
R_X>Вот фрагмент кода, но что-то подсказывает мне, что он еще далек от идеала
...
Это очень неэффективный код. Массовые операции рулят.
R_X> // записать все записи из массива в БД
R_X> for (int i = 0; i < .Count; ++i) {
R_X> CdrRecord rec = cdrRecordsList[ i ];
R_X> StringWriter strWriter = new StringWriter ();
R_X> strWriter.Write ("'{0}', '{1}', '{2}', '{3}', '{4}'",
R_X> rec.data_type, rec.field_a, rec.field_b, , rec.field_c, rec.field_d
R_X> );
R_X> cmd.CommandText = "INSERT INTO cdr_records " +
R_X> "(data_type, rec.field_a, rec.field_b, , rec.field_c, rec.field_d) " +
R_X> "VALUES (" + strWriter.ToString () + ")";
R_X> cmd.ExecuteNonQuery ();
R_X> }
1. Если много — лучше воспользоваться классом SqlBulkCopy. Только надо реализовать свой класс с интерфейсом IDataReader и использовать его заместо List<T> — cdrRecordsList.
2. StringBuilder-ом формировать XML файл и передавать параметром хранимой процедуре. А в ней insert-select-openxml
И для него уже вызывать ExecuteNonQuery. Список полей в инсерте таблицы cdr_records можно не указывать, хотя это и не рекомендуется (мы начинаем зависить от порядка столбцов таблицы), но для нас здесь важен размер полученного батча. Максимальный размер батча равен размер сетевого пакета * 8. Размер сетевого пакета устанавливается в строке соединения параметром Packet size. По умолчанию он равен 8192 — 8 Кило. Максимальный размер пакета (если мне изменяет склероз) 64К. Но третий вариант обладает существенным недостатком (впрочем, как и вариант предложенный тобой) — триггеры на таблице будут вызываться для каждого инсерта.
Так что вариант 1 или 2. Выбрать самый производительный способ помогут проведенные тобой экспиременты. Выбирай, вопросы сюда или лучше в форум db — я там пасусь регулярно.
Re[2]: Грамотно обработать исключения при работе c SQL Serve
___>И для него уже вызывать ExecuteNonQuery. Список полей в инсерте таблицы cdr_records можно не указывать, хотя это и не рекомендуется (мы начинаем зависить от порядка столбцов таблицы), но для нас здесь важен размер полученного батча. Максимальный размер батча равен размер сетевого пакета * 8. Размер сетевого пакета устанавливается в строке соединения параметром Packet size. По умолчанию он равен 8192 — 8 Кило. Максимальный размер пакета (если мне изменяет склероз) 64К. Но третий вариант обладает существенным недостатком (впрочем, как и вариант предложенный тобой) — триггеры на таблице будут вызываться для каждого инсерта.
Да, кстати, вариант 3 можно улучшить, собирая StringBuilder-ом такой батч:
insert cdr_records (<список столбцов>)
select 1, 2, 3, "some value" union all
select 1, 4, 1, "aasdsa" union all
...
select 3, 46, 784, "arsadf"
Триггеры вызовуться один раз, но в любом случае, ИМХО варианты 1 или 2 лучше.
Re[2]: Грамотно обработать исключения при работе c SQL Serve
Спасибо — именно то, что нужно!
KM>ИМХО вместо явного использования метода SqlTransaction.Rollback, гораздо удобнее оборачивать транзакцию в блок using.
Вариант c try { rollback() } был взят из примера в MSDN, с using() все намного красивее.
KM>1. Я бы использовал вместо динамческой генерации sql, параметры. Это спасает от всяких бяк типа sql injection + код в таком случае выглядит нагляднее + теоретическая прибавка к performance (ADO.NET исполняет все запросы через sp_executesql, которая умеет кешировать план запроса)
SQL injection нам не грозит (данные берутся из надежного и правильного источника), а вот на счет кеширования вопрос:
писать до цикла:
Здравствуйте, _d_m_, Вы писали:
___>А сколько обычно записей заливается за один такой вызов процедуры? От ответа на этот вопрос многое зависит.
До 10.000 записей за 1 вызов.
В принципе, задача добиться максимальной производительности не ставится. Простота и легкость сопровождения важнее. Сейчас 1 млн. записей заливается меньше чем за 10 минут и это вполне устраивает.
___>1. Если много — лучше воспользоваться классом SqlBulkCopy. Только надо реализовать свой класс с интерфейсом IDataReader и использовать его заместо List<T> — cdrRecordsList.
А какой выигрыш это даст? Будет не проще — это точно.
___>2. StringBuilder-ом формировать XML файл и передавать параметром хранимой процедуре. А в ней insert-select-openxml
Как вариант на будущее. Но вряд ли потребуется (ибо сложнее).
___>3. StringBuilder-ом формировать батч вида: ___>
Здравствуйте, Ranger_XL, Вы писали:
R_X>Спасибо — именно то, что нужно!
Да незачто .
R_X>SQL injection нам не грозит (данные берутся из надежного и правильного источника), а вот на счет кеширования вопрос: R_X>писать до цикла: R_X>
R_X>в цикле даст такой же результат? (реальное число полей для сохранения ~20.)
Если использовать 2-й вариант, то надо будет в начале тела цикла ставить command.Parameters.Clear(); Ибо иначе вы добавите в комманду несколько параметров с одинаковыми именами. Что же касается performance — если команда исполняется >1 раза, имхо, предпочтительнее первый вариант, ибо во втором происходит при каждом проходе цикла больше действий, чем в первом, но думаю это не особо существенно.
R_X>У foreach() реально есть какие-то преимущества или только наглядность?
Незнаю, но как минимум его наглядность, имхо, это уже конкретный плюс. Посмотрите интерфейсы IEnumerator и IEnumerable. А в C# 2.0 появилась еще и такая штука как итераторы — так там, наглядность вообще зашкаливает посравнению с любым другим способом организации прохода по коллекции.
Re[3]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, Ranger_XL, Вы писали:
R_X>Здравствуйте, _d_m_, Вы писали:
___>>А сколько обычно записей заливается за один такой вызов процедуры? От ответа на этот вопрос многое зависит.
R_X>До 10.000 записей за 1 вызов.
SqlBulkCopy ваше решение
R_X>В принципе, задача добиться максимальной производительности не ставится. Простота и легкость сопровождения важнее. Сейчас 1 млн. записей заливается меньше чем за 10 минут и это вполне устраивает.
___>>1. Если много — лучше воспользоваться классом SqlBulkCopy. Только надо реализовать свой класс с интерфейсом IDataReader и использовать его заместо List<T> — cdrRecordsList.
R_X>А какой выигрыш это даст? Будет не проще — это точно.
Скорость возрастет на порядки. Один вызов триггеров на один вызов (или триггеры можно отключить, см. SqlBulkCopy). Неужели настолько сложно реализовать IDataReader?
___>>2. StringBuilder-ом формировать XML файл и передавать параметром хранимой процедуре. А в ней insert-select-openxml
R_X>Как вариант на будущее. Но вряд ли потребуется (ибо сложнее).
Чем? Это еще проще чем твой цикл.
___>>3. StringBuilder-ом формировать батч вида: ___>>
Здравствуйте, _d_m_, Вы писали:
R_X>>А какой выигрыш это даст? Будет не проще — это точно.
___>Скорость возрастет на порядки. Один вызов триггеров на один вызов (или триггеры можно отключить, см. SqlBulkCopy). Неужели настолько сложно реализовать IDataReader?
Мне — не сложно. Но в моем отделе работают 7 человек, из которых сознательно используют ООП — 2. Так что с точки зрения понятности кода для всех преимущественно используется процедурный подход.
___> Чем? Это еще проще чем твой цикл.
Что может быть проще цикла!?
___>Как раз нетехнологична твоя конструкция с вызовом ExecuteNonQuery для каждой итерации цикла .
Мы немножко по-разному понимаем технологичность (см. выше). Лучше предложите решение для следующей проблемы, где производительность действительно важна.
Итак, у нас есть таблица БД, где хранятся имена уже обработанных файлов (десятки тысяч штук). Нам дан список новых файлов (около 100 штук). Надо исключить из списка те файлы, которые уже хранятся в таблице (таких может быть до 70%).
Пока видится 2 варианта:
1) 100 запросов к БД в цикле (для каждого нового файла)
2) прочитать всю таблицу из БД в память и сделать отбраковку локально
Re[2]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, Ranger_XL, Вы писали:
R_X>Мне — не сложно. Но в моем отделе работают 7 человек, из которых сознательно используют ООП — 2. Так что с точки зрения понятности кода для всех преимущественно используется процедурный подход.
Уволить или переподготовка.
___>> Чем? Это еще проще чем твой цикл.
R_X>Что может быть проще цикла!?
Другой цикл с вызовом StringBuilder.Append
___>>Как раз нетехнологична твоя конструкция с вызовом ExecuteNonQuery для каждой итерации цикла .
R_X>Мы немножко по-разному понимаем технологичность (см. выше). Лучше предложите решение для следующей проблемы, где производительность действительно важна.
Да все я прекрасно понимаю, просто всему есть предел. Программист не воспринимающий понятие интерфейса (имеется ввиду типа IDataReader или прочее) — безнадежно устарел. Как сказал Эйнштейн: "Все должно просто, просто как это возможно, но не более того"
По поводу "предложить решение": а почему ты не хочешь задать этот вопрос в форуме db?
R_X> R_X>Итак, у нас есть таблица БД, где хранятся имена уже обработанных файлов (десятки тысяч штук). Нам дан список новых файлов (около 100 штук). Надо исключить из списка те файлы, которые уже хранятся в таблице (таких может быть до 70%).
R_X>Пока видится 2 варианта: R_X>1) 100 запросов к БД в цикле (для каждого нового файла)
Фуфло. Массовые операции рулят. Все-таки реляционные технологии — это операции над множествами. Да и вызов RPC по сети довольно дорогая операция, а ты ее дергаешь каждую итерацию. R_X>2) прочитать всю таблицу из БД в память и сделать отбраковку локально R_X>
Тоже фуфло. DDL таблицы в студию, естественно, вместе ключами и индексами.
Вобщем-то, я вижу простое и элегантное решение: уникальный индекс на таблицу WITH IGNORE_DUP_KEY
Re[5]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, Ranger_XL, Вы писали:
R_X>Мы немножко по-разному понимаем технологичность (см. выше). Лучше предложите решение для следующей проблемы, где производительность действительно важна. R_X> R_X>Итак, у нас есть таблица БД, где хранятся имена уже обработанных файлов (десятки тысяч штук). Нам дан список новых файлов (около 100 штук). Надо исключить из списка те файлы, которые уже хранятся в таблице (таких может быть до 70%).
R_X>Пока видится 2 варианта: R_X>1) 100 запросов к БД в цикле (для каждого нового файла) R_X>2) прочитать всю таблицу из БД в память и сделать отбраковку локально R_X>
Скорее всего, самый быстрый способ — сделать 100 запросов к базе данных. Затраты на это будут пропорциональны log(N), где N — общее количество файлов, а затраты на подъем всей таблицы в память — O(N).
1.2.0 alpha rev. 655
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[4]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, kostya.misura, Вы писали:
KM>Здравствуйте, Ranger_XL, Вы писали:
А в C# 2.0 появилась еще и такая штука как итераторы — так там, наглядность вообще зашкаливает посравнению с любым другим способом организации прохода по коллекции.
что вы имеете в виду?
--------------------------
less think — do more
Re: Грамотно обработать исключения при работе c SQL Server
Здравствуйте, Ranger_XL, Вы писали:
R_X>Самый неясный момент для меня сейчас: как грамотно организовать обработку исключений R_X>(и запись их в журнал!) при работе с SQL server и при этом освободить ресурсы (connection) R_X>в случае ошибки?
R_X>
R_X>using System;
R_X>using System.Collections.Generic;
R_X>using System.Data.SqlClient;
R_X>static void WriteRecordsToDB (
R_X> string shortFilename, // in: имя CDR-файла
R_X> List<CdrRecord> cdrRecordsList // in: массив записей, прочитанных из CDR-файла
R_X>)
R_X>{
R_X> try {
R_X> // вариант с using(...) из примера MSDN
R_X> using (SqlConnection connect = new SqlConnection (DBCONNECTION_STRING)) {
R_X> // установить соединение с SQL сервером - может бросить исключение!
R_X> connect.Open ();
R_X> SqlTransaction transaction = connect.BeginTransaction ("MyTransaction");
R_X> }
R_X> }
R_X> catch (Exception ex) {
R_X> // перехват connect.Open(), BeginTransaction()
R_X> LogMessage ("WriteRecordsToDB(): " + shortFilename + " is not processed.", ex);
R_X> }
R_X> finaly
R_X> {
R_X> connect.Close();
R_X> }
R_X>}
R_X>
Re[2]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, kostya.misura, Вы писали:
KM>ИМХО вместо явного использования метода SqlTransaction.Rollback, гораздо удобнее оборачивать транзакцию в блок using. По выходе из блока using, если транзакция не была commited она откатывается автоматически. По моему такой подход гораздо менее громоздок.
Есть где-нибудь, какое-нибудь требование стандарта, по которому не закоммиченная транзация при Dispose делает Rollback?
Я когда-то тоже делал просто using, используя OleDBProvider для какой-то базы (точно не помню, кажется — DB2 была это)... А потом в том же проекте переключился на использование ее родного драйвера, который при диспозе незакоммиченной транзации делал — ха — коммит!
Я сперва не поверил, проверял Рефлектором Да, действительно делал коммит.
Это были острые впечатления. Пролистать весь код и вставить явные роллбэки.
С тех пор я всегда явно делаю и Commit() и Rollback().
Так что имхо, не стоит полагаться на реализацию провайдера. Dispose() должен освобождать занятые ресурсы. А будет он при это коммитить, отктатывать, блокировать транзакцию, или отсылать емейл в Пентагон — это детали реализации и дело автора... Если, конечно, у тебя нет ссылки на стандарт, который можно отправить разработчикам и потребовать исправления... Я подобного не нашел тогда.
З.Ы. при отсутствии стандарта то, что работает сегодня таким образом может работаьт совсем иначе в новой версии..
Re[5]: Грамотно обработать исключения при работе c SQL Serve
Простейший способ — это 100 запросов. Эффективный способ — с помощью stored procedure. Загнать имена в строку, на сервере распарсить и проверить каждый.
Если нет каких-то сверхтребований, советую выбрать простейший.
... << RSDN@Home 1.2.0 alpha rev. 0>>
Re[2]: Грамотно обработать исключения при работе c SQL Serve
Здравствуйте, Igore, Вы писали:
I>Здравствуйте, Ranger_XL, Вы писали:
1. твой код просто не скомпилируется
2. using для того и служит, чтобы вызвать Dispose() при любом раскладе. Суть диспоза — освобождение ресурсов. А оставление подвешенного со стороны открытого соединения нельзя назвать качественной очисткой Потому диспоз должен закрывать соединение (и в известных мне реализациях — закрывает таки ).