Я загружаю большие объемы данных из текстового файла в SQL Server. В настоящее время каждая запись вставляется (или обновляется) в отдельной транзакции, но это оставляет БД в плохом состоянии, если запись не удалась.
Я хотел бы поместить все это в одну большую транзакцию. В моем случае я просматриваю ~ 250 000 вставок или обновлений и, возможно, ~ 1 000 000 запросов. Текстовый файл составляет примерно 60 МБ.
Неразумно ли перевести всю операцию в одну транзакцию? Какой ограничивающий фактор?
Это не только не так уж и необоснованно, но это необходимо, если вы хотите сохранить целостность, если какая-либо запись не удалась, поэтому вы получаете импорт "все или ничего", как вы заметили. 250000 вставок или обновлений не будет проблемой для SQL, но я бы посмотрел, что это за миллионы запросов. Если они не нужны для выполнения модификации данных, я бы вывел их из транзакции, поэтому они не замедляют весь процесс.
Вы должны учитывать, что когда у вас есть открытая транзакция (независимо от размера), внешний вид будет происходить в таблицах, к которым он прикасается, а длительные транзакции, подобные вашей, могут вызвать блокировку у других пользователей, которые пытаются их прочитать одновременно, Если вы ожидаете, что импорт будет большим и трудоемким, а система будет находиться под нагрузкой, подумайте о том, чтобы весь процесс в течение ночи (или любых непиковых часов) уменьшал эффект.
О размере, в SQL Server нет определенного ограничения по размеру, они могут теоретически модифицировать любой объем данных без проблем. Практический предел - это действительно размер файла журнала транзакций целевой базы данных. Механизм БД хранит все временные и измененные данные в этом файле во время выполнения транзакции (поэтому он может использовать его для отката, если потребуется), поэтому этот файл будет расти в размере. Он должен иметь достаточное свободное пространство в свойствах БД и достаточно пространства HD для файла для роста. Кроме того, строка или таблица блокирует, что движок будет помещать в затронутые таблицы, потребляет память, поэтому сервер должен иметь достаточно свободной памяти для всей этой сантехники. Во всяком случае, размер 60 МБ часто слишком мал, чтобы беспокоиться об общем. 250 000 строк значительны, но не так уж и много, поэтому любой сервер с приличным размером сможет справиться с этим.
SQL Server может обрабатывать транзакции по размеру. Мы используем одну транзакцию для массовой загрузки нескольких миллионов записей.
Самая дорогая часть операции с базой данных - это, как правило, подключение и трафик клиентского сервера. Для вставки/обновления индексирование и протоколирование также являются дорогостоящими, но вы можете уменьшить эти затраты, используя правильные методы загрузки (см. Ниже). Вы действительно хотите ограничить количество подключений и данных, передаваемых между клиентом и сервером.
С этой целью вы должны рассмотреть возможность массовой загрузки данных с помощью SSIS или С# с помощью SqlBulkCopy. После того, как вы загрузите всю нагрузку, вы можете использовать операции с множеством на сервере, чтобы обновить или проверить свои данные.
Взгляните на этот вопрос, чтобы узнать больше о оптимизации загрузки данных. Вопрос связан с С#, но большая часть информации полезна для SSIS или других методов загрузки. Каков самый быстрый способ массового ввода большого количества данных в SQL Server (клиент С#).
Хорошо лично, я не загружаю импортированные данные непосредственно в мои таблицы prod, и я отсеял все записи, которые не пройдут, прежде чем я когда-нибудь доберусь до места загрузки. Некоторые виды ошибок полностью уничтожают импорт, а другие могут просто отправить запись в таблицу исключений, которая будет отправлена обратно поставщику и исправлена для следующей загрузки. Обычно у меня есть логика, которая определяет, есть ли слишком много исключений и убивает пакет.
Например, предположим, что город является повторно используемым полем в вашей базе данных и в файле, состоящем из 1 000 000 записей, у вас десять, у которых нет города. Вероятно, лучше всего отправить их в таблицу исключений и загрузить остальные. Но предположим, у вас есть 357 894 записей без города. Тогда вам может потребоваться провести беседу с поставщиком данных, чтобы данные были зафиксированы перед загрузкой. Это, безусловно, повлияет на prod less, если вы сможете определить, что файл не используется, прежде чем пытаться повлиять на производственные таблицы.
Кроме того, почему вы делаете эту запись за раз? Вы можете часто выполнять намного быстрее при обработке на основе набора, особенно если вам уже удалось очистить данные заранее. Теперь вам все равно придется делать партиями, но одна запись за раз может быть очень медленной.
Если вы действительно хотите отбросить все это, если какие-либо ошибки детали, да, вам нужно использовать транзакции. Если вы сделаете это в SSIS, вы можете поместить транзакции только в ту часть пакета, где вы воздействуете на таблицы prod, и не беспокоиться о них при размещении данных и очистке частей.
Нет проблем с выполнением операции "все или ничего", если полный откат не является проблематичным для вашей компании. Фактически, одна транзакция является поведением по умолчанию для множества встроенных утилит для вставки.
Я бы настоятельно советовал против одной операции за строку. Если вы хотите избавиться от плохих данных, вы можете сначала загрузить данные в промежуточную таблицу и про грамматически определить "плохие данные" и пропустить эти строки.