Самый быстрый способ обновить 120 миллионов записей

Ответ 1

Единственный нормальный способ обновления таблицы из записей 120M - это оператор SELECT, который заполняет таблицу второй. Вы должны позаботиться об этом. Инструкции ниже.

Простой случай

Для таблицы без кластеризованного индекса за время без параллельного DML:

• SELECT *, new_col = 1 INTO clone.BaseTable FROM dbo.BaseTable
• воссоздать индексы, ограничения и т.д. в новой таблице
• переключиться на старый и новый w/ALTER SCHEMA... TRANSFER.
• удалить старую таблицу

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

Несложный случай

Сначала заново создайте BaseTable с тем же именем под другой схемой, например clone.BaseTable. Использование отдельной схемы упростит процесс переименования позже.

• Включить кластеризованный индекс, если это применимо. Помните, что первичные ключи и уникальные ограничения могут быть сгруппированы, но необязательно.
• Включить столбцы идентификации и вычисленные столбцы, если применимо.
• Включите новый столбец INT, где бы он ни находился.
• Не включать любое из следующего:
  • Триггеры
  • ограничения внешних ключей
  • некластеризованные индексы/первичные ключи/уникальные ограничения
  • проверить ограничения или ограничения по умолчанию. Значения по умолчанию не имеют большого значения, но мы пытаемся сохранить вещи минимальные.

Затем проверьте свою вставку с 1000 строк:

-- assuming an IDENTITY column in BaseTable
SET IDENTITY_INSERT clone.BaseTable ON
GO
INSERT clone.BaseTable WITH (TABLOCK) (Col1, Col2, Col3)
SELECT TOP 1000 Col1, Col2, Col3 = -1
FROM dbo.BaseTable
GO
SET IDENTITY_INSERT clone.BaseTable OFF

Изучите результаты. Если все выглядит по порядку:

• обрезать таблицу клонов
• убедитесь, что база данных находится в массивной или простой модели восстановления
• выполнить полную вставку.

Это займет некоторое время, но не до тех пор, пока обновление. Как только он завершится, проверьте данные в таблице клонирования, чтобы убедиться, что все правильно.

Затем заново создайте все некластеризованные первичные ключи/уникальные ограничения/индексы и ограничения внешнего ключа (в этом порядке). Восстановите ограничения по умолчанию и проверки, если это применимо. Восстановите все триггеры. Восстановите каждое ограничение, индекс или триггер в отдельной партии. например:

ALTER TABLE clone.BaseTable ADD CONSTRAINT UQ_BaseTable UNIQUE (Col2)
GO
-- next constraint/index/trigger definition here

Наконец, переместите dbo.BaseTable в схему резервного копирования и clone.BaseTable в схему dbo (или там, где должна находиться ваша таблица).

-- -- perform first true-up operation here, if necessary
-- EXEC clone.BaseTable_TrueUp
-- GO
-- -- create a backup schema, if necessary
-- CREATE SCHEMA backup_20100914
-- GO
BEGIN TRY
  BEGIN TRANSACTION
  ALTER SCHEMA backup_20100914 TRANSFER dbo.BaseTable
  -- -- perform second true-up operation here, if necessary
  -- EXEC clone.BaseTable_TrueUp
  ALTER SCHEMA dbo TRANSFER clone.BaseTable
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  SELECT ERROR_MESSAGE() -- add more info here if necessary
  ROLLBACK TRANSACTION
END CATCH
GO

Если вам нужно освободить место на диске, вы можете сбросить свою исходную таблицу в это время, хотя может быть разумным сохранить ее еще дольше.

Излишне говорить, что это идеальная операция офлайн. Если у вас есть люди, которые изменяют данные во время выполнения этой операции, вам придется выполнить операцию с использованием переключателя схемы. Я рекомендую создать триггер на dbo.BaseTable, чтобы записать все DML в отдельную таблицу. Включите этот триггер перед началом вставки. Затем в той же транзакции, которую вы выполняете для передачи схемы, используйте таблицу журналов для выполнения проверки подлинности. Сначала проверьте это на подмножестве данных! Дельты легко завинчиваются.

Ответ 2

Если у вас есть дисковое пространство, вы можете использовать SELECT INTO и создать новую таблицу. Он был минимально зарегистрирован, поэтому он будет намного быстрее

select t.*, int_field = CAST(-1 as int)
into mytable_new 
from mytable t

-- create your indexes and constraints

GO

exec sp_rename mytable, mytable_old
exec sp_rename mytable_new, mytable

drop table mytable_old

Ответ 3

Я разбиваю задачу на более мелкие единицы. Протестируйте с различными интервалами размера партии для своей таблицы, пока не найдете оптимальный интервал. Вот пример, который я использовал в прошлом.

declare @counter int 
declare @numOfRecords int
declare @batchsize int

set @numOfRecords = (SELECT COUNT(*) AS NumberOfRecords FROM <TABLE> with(nolock))
set @counter = 0 
set @batchsize = 2500

set rowcount @batchsize
while @counter < (@numOfRecords/@batchsize) +1
begin 
set @counter = @counter + 1 
Update table set int_field = -1 where int_field <> -1;
end 
set rowcount 0

Ответ 4

Если ваше int_field индексируется, удалите индекс перед запуском обновления. Затем снова создайте свой индекс...

5 часов кажутся много за 120 миллионов рек.

Ответ 5

set rowcount 1000000
Update table set int_field = -1 where int_field<>-1

посмотреть, как быстро это происходит, корректировать и повторять по мере необходимости

Ответ 6

declare @cnt bigint
set @cnt = 1

while @cnt*100<10000000 
 begin

UPDATE top(100) [Imp].[dbo].[tablename]
   SET [col1] = xxxx 
 WHERE[col1] is null  

  print '@cnt: '+convert(varchar,@cnt)
  set @[email protected]+1
  end

Ответ 7

Что бы я попробовал в первую очередь:
сначала удалить все ограничения, индексы, триггеры и полные текстовые индексы.

Если выше не было достаточно высокой производительности, мой следующий шаг был бы
для создания CSV файла с 12 миллионами записей и массового импорта с помощью bcp.

Наконец, я бы создал новую таблицу кучи (имея в виду таблицу без первичного ключа) без индексов в другой файловой группе, заполнив ее -1. Разделите старую таблицу и добавьте новый раздел с помощью "switch".

Ответ 8

Когда добавляет новый столбец ( "инициализировать новое поле" ) и задает одно значение для каждой существующей строки, я использую следующую тактику:

ALTER TABLE MyTable
 add NewColumn  int  not null
  constraint MyTable_TemporaryDefault
   default -1

ALTER TABLE MyTable
 drop constraint MyTable_TemporaryDefault

Если столбец имеет значение NULL, и вы не включаете "объявленное" ограничение, столбец будет установлен равным null для всех строк.

Ответ 9

Похоже на проблему индексации, как упоминал Пабла Санта-Крус. Поскольку ваше обновление не является условным, вы можете DROP столбца и RE-ADD его с значением DEFAULT.

Ответ 10

В целом рекомендации следующие:

• Удалить или просто отключить все INDEXES, TRIGGERS, CONSTRAINTS на столе;
• Выполнять COMMIT чаще (например, после каждого 1000 записей, которые были обновлены);
• Используйте select... в.

Но в конкретном случае вы должны выбрать наиболее подходящее решение или их комбинацию.

Также помните, что когда-нибудь индекс мог бы быть полезен, например. когда вы выполняете обновление неиндексированного столбца некоторым условием.

Ответ 11

Если таблица имеет индекс, который вы можете повторить, я бы поставил оператор update top(10000) в цикл while, перемещающийся по данным. Это позволит сохранить журнал транзакций тонким и не окажет такого огромного влияния на дисковую систему. Кроме того, я бы рекомендовал сыграть с опцией maxdop (установив ее ближе к 1).