Каков наиболее эффективный способ обновления тысяч записей
У нас есть приложение C #, которое анализирует данные из текстовых файлов. Затем мы должны обновлять записи в нашей базе данных sql на основе информации в текстовых файлах. Каков наиболее эффективный способ передачи данных из приложения на SQL-сервер?
В настоящее время мы используем строку с разделителями и затем перебираем строку в хранимой процедуре для обновления записей. Я также тестирую с использованием TVP (параметр таблицы). Есть ли другие варианты?
Наши файлы содержат тысячи записей, и мы хотели бы, чтобы решение занимало наименьшее количество времени.
Пожалуйста, не используйте DataTable поскольку это просто тратит процессор и память на отсутствие выгоды (кроме, возможно, знакомства). Я подробно описал очень быстрый и гибкий подход в своем ответе на следующие вопросы, которые очень похожи на этот:
Как я могу вставить 10 миллионов записей в кратчайшие сроки?
Пример, показанный в этом ответе, предназначен только для INSERT, но его можно легко адаптировать для включения UPDATE. Кроме того, он загружает все строки в один снимок, но также может быть легко адаптирован для установки счетчика для количества записей X и выхода из метода IEnumerable после того, как многие записи были переданы, а затем закройте файл, когда есть больше записей. Это потребовало бы хранения указателя File (т.е. потока) в статической переменной, чтобы продолжить переход к методу IEnumerable, чтобы он мог быть расширен и поднят в самой последней позиции в следующий раз. У меня есть рабочий пример этого метода, показанный в следующем ответе, хотя он использовал SqlDataReader качестве входных данных, но метод тот же и требует очень небольшой модификации:
Как разбить одну большую таблицу с 100 миллионами данных на несколько таблиц?
И для некоторой перспективы, 50 тыс. Записей даже не близки к «огромным». Я загружал / объединял / синхронизирул данные, используя метод, который я показываю здесь, в 4 миллионах файлов строк и попадаю в несколько таблиц с 10 миллионами (или более) строк.
Нечего делать:
- Используйте
DataTable: как я уже сказал, если вы просто заполняете его для использования с TVP, это пустая трата процессора, памяти и времени. - Сделайте 1 обновление одновременно параллельно (как предлагается в комментарии к вопросу): это просто сумасшествие. Двигатели реляционных баз данных в значительной степени настроены для работы наиболее эффективно с наборами, а не с одноточечными операциями. Нет никакого способа, чтобы вставки 50k были более эффективными, чем даже 500 вставок по 100 строк. Выполнение этого индивидуально просто гарантирует больше конфликтов на столе, даже если просто блокировки строк (это 100k блокировка + разблокировка операций). Может быть быстрее, чем одна транзакция на 50 тыс. Строк, которая перерастает в блокировку таблицы (как упоминал Аарон), но именно поэтому вы делаете это небольшими партиями, так как малый не означает 1 строку;).
- Задайте размер партии произвольно. Пребывание ниже 5000 рядов полезно, чтобы уменьшить вероятность эскалации блокировки, но не просто выбрать 200. Экспериментируйте с несколькими размерами партии (100, 200, 500, 700, 1000) и попробуйте каждый раз несколько раз. Вы увидите, что лучше всего подходит для вашей системы. Просто убедитесь, что размер партии настраивается, хотя файл app.config или некоторые другие средства (таблица в БД, параметр реестра и т. Д.), Чтобы его можно было изменить без повторного развертывания кода.
- SSIS (мощный, но очень громоздкий и не забавный для отладки)
Вещи, которые работают, но не так гибки, как правильно сделанный TVP (т.е. передача в метод, который возвращает IEnumerable<SqlDataRecord> ). Это нормально, но зачем записывать записи в временную таблицу только для того, чтобы анализировать их в пункте назначения, когда вы можете сделать все это встроенным?
- BCP / OPENROWSET (BULK …) / BULK INSERT
- .NET
SqlBulkCopy
Лучший способ сделать это, на мой взгляд, – создать временную таблицу, а затем использовать SqlBulkCopy для вставки в эту временную таблицу ( https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v = vs.110% 29.aspx ), а затем просто обновить таблицу на основе таблицы temp.
Основываясь на моих тестах (используя Dapper, а также LINQ), обновление в виде массива или с партиями занимает слишком много времени, чем просто создание временной таблицы и отправка команды на сервер для обновления данных на основе таблицы temp. Процесс выполняется быстрее, потому что SqlBulkCopy быстро заполняет данные изначально, а остальная часть выполняется на стороне сервера SQL, которая проходит меньше шагов вычисления, а данные в этой точке находятся на сервере.