У меня есть таблица в базе данных MSSQL, которая содержит более 100 миллионов записей, разбросанных по данным на 100 дней. Мне нужно очистить некоторые из этих данных на основе их даты, которая является индексированным полем в таблице. Я пробовал выполнить DELETE FROM для отдельной даты, но выполнение занимало много времени и вызывало падение производительности сервера. Есть ли лучший способ удалить такое большое количество записей? Некоторые из этих данных все еще требуются, поэтому, к сожалению, я не могу использовать усечение.
Большое спасибо Ник
Что ж, если бы вы использовали секционирование SQL Server, скажем, на основе столбца даты, вы, возможно, отключили бы разделы, которые больше не требуются. Возможно, стоит подумать о будущей реализации.
Я думаю, что ваш единственный вариант может заключаться в удалении данных небольшими партиями, а не одним обращением, чтобы избежать любых потенциальных проблем с блокировкой.
Наибольший успех у меня получился при выполнении аналогичных задач по следующему алгоритму:
Одним из основных преимуществ этого является то, что ваши индексы будут перестраиваться, когда вы вернете данные в исходную таблицу.
Вы можете УДАЛИТЬ все индексы в таблице, УДАЛИТЬ ИЗ таблицы, а затем заново СОЗДАТЬ индексы. Это могло бы ускорить процесс, но это зависит от процента записей, которые не удаляются.
Вы можете удалить более мелкие куски. Вместо того, чтобы сказать, стоит попробовать потратить несколько недель на один день. Если это слишком много, попробуйте заниматься по часу за раз.
Другой вариант - выбрать в другую таблицу только те данные, которые вам нужны. Таким образом вы можете настроить разбиение по дате.
Если бы индекс даты был кластеризованным индексом, удаления должны происходить быстрее, так как все они были бы близко друг к другу на диске.
Я не люблю и не поддерживаю упомянутое предложение временной таблицы. Если сервер упадет между этапом удаления и этапом вставки, вы потеряете данные.
Я бы с большей вероятностью выбрал одно из следующих:
BCP данные, которые вы хотите сохранить (BCP с использованием QUERYOUT), обрезать, повторно импортировать. Тот же эффект, что и временная таблица, с меньшим общим риском.
Скопируйте данные в другую постоянную таблицу либо в той же базе данных, либо в другой, а затем извлеките их обратно.
Удаляйте партиями, используя технику SET ROWCOUNT. Если вы осторожны и изобретательны, вы можете зациклить это так, чтобы это происходило за пределами цикла, поэтому удаления фиксируются независимо. Если ваш кластерный индекс не связан с тем, как вы удаляете данные, это приведет к значительной фрагментации таблицы.
Возможно, в некоторых OLTP-базах большого объема данные лучше вообще не удалять. Разработчики могут создать поле IsDeleted или что-то в этом роде. Но это вопрос на будущее.
Что касается ответа, вы согласились. Я не верю, что он будет работать быстрее, чем простой подход DELETE, если вы скопируете 100 МБ данных. Это будет очень большая нагрузка и большой рост журнала транзакций. Как правило, это зависит от того, какую часть этих данных вы хотите оставить нетронутой после завершения удаления.
Я бы порекомендовал
1) Если вы можете выполнить свой запрос в неактивные часы, вы должны выполнить монопольную блокировку таблицы, а затем удалить записи. это сэкономит время, которое SQL-сервер потратит на распространение блокировок на множество отдельных строк.
2) если 1-й подход невозможен, то удаляйте по частям, я соглашусь с Джоном Сансом. Проблемы начинаются, когда происходит очень большая транзакция, которая блокирует множество транзакций других активных пользователей ... Таким образом, вы должны выполнять удаление небольшими частями, каждая в своей транзакции ...
3) вы также можете временно отключить (или отбросить, а затем воссоздать) триггеры и ограничения до / после удаления (включая внешние ключи), однако существует риск целостности, и этот подход требует некоторых экспериментов.
AFAIK, отключение / включение индексов не улучшит ситуацию, потому что, когда вы удаляете записи, в деревьях индексов будут «дыры» ... Таким образом, это может повлиять на производительность следующих SQL-запросов для той же таблицы, и рано или поздно вы может захотеть перестроить индексы, однако я никогда не вижу никакого влияния на то, как индексы (даже если у вас тоже есть индексы) могут снизить скорость операции удаления
В большинстве случаев плохая производительность DELETE - это когда индексы не используются запросом DELETE (вы можете проверить план запроса) или когда у вас слишком много внешних ключей или сложная логика триггеров.
Удалить кусками.
Сделайте удаление на основе выбора в соответствии с вашими критериями, но выбор имеет ТОП 100000, поэтому при каждом вызове удаляются только 100000 строк. Звоните, пока больше не будет удалено.