Более быстрый способ обновить 250k строки с SQL
Я должен обновить о 250k строках на таблице, и каждое поле для обновления будет иметь различное значение в зависимости от самой строки (не вычисленным на основе идентификатора строки или ключа, но внешне).
Я попробовал параметрическим запросом, но это оказывается медленным (я все еще могу попробовать параметром табличного значения, SqlDbType. Структурированный, в SQL Server 2008, но я хотел бы иметь общий способ сделать это на нескольких базах данных включая MySql, Oracle и Firebird).
Создание огромного concat отдельных обновлений является также медленным (НО приблизительно в 2 раза быстрее, чем создание тысяч отдельных вызовов (распространения в прямом и обратном направлениях!) использование параметрических запросов)
Что относительно того, чтобы составить временную таблицу и выполнить обновление, присоединяющееся к моей таблице и tmp один? Это будет работать быстрее?
6 ответов
Если обновленные столбцы являются частью индексов, вы можете
- отбросить эти индексы
- выполнить обновление
- повторно создать индексы.
Если вам нужны эти индексы для извлечения данных, что ж, это не поможет.
Вы должны использовать SqlBulkCopy с установленным флагом KeepIdentities.
В рамках SqlTransaction выполните запрос, чтобы ВЫБРАТЬ все записи, которые необходимо обновить, а затем УДАЛИТЬ ИХ, вернув выбранные (а теперь удаленные) записи. Прочтите их на C # одним пакетом. Обновите записи на стороне C # в памяти, теперь, когда вы сузили выбор, а затем SqlBulkCopy эти обновленные записи обратно, ключи и все остальное. И не забудьте зафиксировать транзакцию. Это больше работы, но очень быстро.
Насколько "медленный" "медленный"?
Основная проблема заключается в том, что это создаст огромную запись в базе данных файл журнала (в случае сбоя питания на полпути обновления, база данных должна регистрировать каждое действие, чтобы можно было выполнить откат в случае сбоя). Скорее всего, именно отсюда и "медленность", а не что-либо еще (хотя очевидно, что при таком большом количестве строк есть и другие способы сделать это неэффективным [например,выполнение одного обращения к БД за одно обновление было бы невыносимо медленным], я просто говорю, что как только вы устраните очевидные вещи, вы все равно обнаружите, что это довольно медленно).
Есть несколько способов сделать это более эффективно. Можно было бы выполнять обновление кусками, скажем, по 1000 строк за раз. Таким образом, база данных записывает множество небольших записей в журнал, а не одну действительно огромную.
Другой способ - отключить - или «выключить» - ведение журнала базы данных на время обновления. В SQL Server, например, вы можете установить модель восстановления на «простое» или «массовое обновление», что значительно ускорит ее (с оговоркой, что вы подвергаетесь большему риску, если произойдет сбой питания или кое-что во время обновления).
Правка Просто чтобы немного расширить, вероятно, наиболее эффективным способом выполнения запросов в первую очередь было бы выполнить BULK INSERT всех новых строк во временную таблицу, а затем выполнить одну ОБНОВЛЕНИЕ существующей таблицы из этого (или выполнить ОБНОВЛЕНИЕ кусками по 1000, как я сказал выше). Большая часть моего ответа касалась решения проблемы после того, как вы реализовали ее таким образом: вы все равно обнаружите, что она довольно медленная ...
Вот что я бы сделал:
- Извлеките всю таблицу, то есть столбцы, которые вам нужны для вычисления/получения/нахождения/произведения изменений извне
- Вычислите/произведите эти изменения
- Выполните массовую вставку во временную таблицу, загрузив информацию, которая вам нужна на стороне сервера, чтобы сделать изменения. Для этого потребуется ключевая информация + новые значения для всех строк, которые вы собираетесь изменить.
- Запустите SQL на сервере, чтобы скопировать новые значения из временной таблицы в рабочую таблицу.
Плюсы:
- Выполнение последнего шага на стороне сервера быстрее, чем выполнение тонн и тонн отдельных SQL, поэтому вы заблокируете таблицу на меньшее время
- Подобная массовая вставка выполняется быстро
Минусы:
- Требуется дополнительное место в вашей базе данных для временной таблицы
- Производит больше данных журнала, регистрируя и массовую вставку, и изменения в производственной таблице
Вот вещи, которые могут замедлить ваши обновления:
- выполнение обновлений одно за другим с помощью параметризованного запроса
- решение: выполнить обновление одним оператором
- большая транзакция создает большая запись в журнале
- см. кодека ответ
- обновление индексов (СУБД будет обновлять индекс после каждой строки. Если вы измените индексированный столбец, это может оказаться очень дорогостоящим на большой таблице)
- если можете, отбросьте индексы перед обновлением и воссоздайте их после
- обновления поля, имеющего ограничение внешнего ключа - для каждой вставленной записи РСУБД будет искать соответствующий ключ
- , если можете, отключите ограничения внешнего ключа перед обновлением и включите их после триггеров update
- и проверок на уровне строк
- по возможности отключите триггеры перед обновлением и включите их после