Медленная объемная вставка для таблицы со многими индексами

Я пытаюсь вставить миллионы записей в таблицу с более чем 20 индексами.

В последнем прогоне потребовалось более 4 часов на 100 000 строк, и запрос был отменен через 3½ дня...

Есть ли у вас какие-либо предложения о том, как ускорить это.

(Я подозреваю, что причиной являются многие индексы. Если вы так думаете, как я могу автоматически отбрасывать индексы перед операцией, а затем снова создавать те же индексы?)

Дополнительная информация:

  • Пространство, используемое индексами, примерно в 4 раза больше пространства, используемого только этими данными
  • Вставки заключены в транзакцию на 100 000 строк.

Обновление статуса:

Принятый ответ помог мне сделать это намного быстрее.

Ответы

Ответ 1

Вы можете отключить и включить индексы. Обратите внимание, что отключение их может иметь нежелательные побочные эффекты (например, наличие повторяющихся первичных ключей или уникальных индексов и т.д.), Которые будут найдены только при повторном включении индексов.

--Disable Index
ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO

--Enable Index
ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO

Ответ 2

Это похоже на операцию хранилища данных. Было бы нормально отбрасывать индексы перед вставкой и восстанавливать их впоследствии.

Когда вы перестраиваете индексы, сначала создайте кластерный индекс и, наоборот, оставьте его последним. Все они должны заполнить 100%.

Код должен быть чем-то вроде этого

if object_id('Index') is not null drop table IndexList
select name into Index from dbo.sysindexes where id = object_id('Fact')

if exists (select name from Index where name = 'id1') drop index Fact.id1
if exists (select name from Index where name = 'id2') drop index Fact.id2        
if exists (select name from Index where name = 'id3') drop index Fact.id3
.
.
BIG INSERT

RECREATE THE INDEXES

Ответ 3

Как отметил другой ответ, отключение индексов будет очень хорошим началом.

4 часа на 100 000 строк [...] Вставки завернуты в транзакцию на 100 000 строк.

Вы должны посмотреть на сокращение числа, сервер должен поддерживать огромное количество состояний во время транзакции (поэтому его можно отбросить назад), это (наряду с индексами) означает, что добавление данных - это очень тяжелая работа.

Почему бы не обернуть каждый оператор insert в свою транзакцию?

Также посмотрите на характер SQL, который вы используете, добавляете ли вы одну строку для каждого оператора (и кругооборот в сети) или добавляете много?

Ответ 4

В этих случаях часто предлагается отключение и повторное включение индексов. У меня есть сомнения в этом подходе, потому что:

(1) Пользователь БД приложения нуждается в привилегиях изменения схемы, которых он обычно не должен обладать. (2) Выбранный подход вставки и/или индексная схема могут быть вначале менее оптимальными, в противном случае восстановление полных деревьев индексов не должно быть быстрее, чем некоторая приличная вставка пакета (например, клиент, выдающий один оператор вставки за раз, вызывая тысячи серверных обращений или плохой выбор в кластерном индексе, приводящий к постоянному индексу node).

Вот почему мои предложения выглядят несколько иначе:

  • Увеличить пакет ADO.NET BatchSize
  • Измените целевой кластеризованный индекс таблицы так, чтобы вставки не приводили к разделению кластеризованного индекса node. Обычно столбец идентичности является хорошим выбором.
  • Сначала клиент вставляется во временную таблицу кучи (таблицы кучи не имеют кластеризованного индекса); затем выведите один большой оператор "insert-into-select", чтобы вытолкнуть все данные промежуточной таблицы в фактическую целевую таблицу.
  • Применить SqlBulkCopy
  • Уменьшить регистрацию транзакций, выбрав модель восстановления с резервным копированием

Вы можете найти более подробную информацию в этой статье.