Как быстрее всего удалить много записей средствами MS SQL #803923

Платформа 1С 8.2, в базе есть примерно 68 млн. документов одного вида, без табличных частей, около 35 млн. из них надо удалить с отбором по дате меньше определенной, ссылочная целостность не важна. Время простоя базы ограничено. Понятно, что средствами 1С быстро это сделать нереально. Как сделать это максимально быстро? Удалять в одной транзакции или бить на несколько, если на несколько, то как определить оптимальный с точки зрения скорости размер порции?

Платформа 1С 8.2, в базе есть примерно 68 млн. документов одного вида, без табличных частей, около 35 млн. из них надо удалить с отбором по дате меньше определенной, ссылочная целостность не важна. Время простоя базы ограничено. Понятно, что средствами 1С быстро это сделать нереально. Как сделать это максимально быстро? Удалять в одной транзакции или бить на несколько, если на несколько, то как определить оптимальный с точки зрения скорости размер порции?

Если в скуле это одна таблица, то truncate. За секунду грохнет все записи

>>надо удалить с отбором

truncate для таблиц итогов хорошо, в этом случае не подойдет.

а тогда не подойдет, она всю таблицу грохнет. Тогда delete с условием

Один из вариантов, да. Но и тут можно либо одной транзакцией сделать, либо несколькими. Как быстрее, вот в чем вопрос.

А 1С за какое время у тебя их удаляет?

за бесконечное.

Самое сложное - это распровести

В контексте данной задачи это не важно.

Я попробовал удалить 100 тыс, около 30 минут заняло. Ну его нафик.

Значит уменьшайте до 50-30-20-10-5-1 тысяч и опытным путем находите подходящий размер порции. Индексы именно для такого удаления есть? Или просто "какие-то индексы" есть? План запроса на удаление смотрели?

Я имел в виду удаление средствами sql, а не 1с. Рисуете запрос на  удаление в цикле и вперед.

Это я про средствами 1С, такто через ADODB одной транзакцией за 2 часа удалилось, но хочется еще быстрее :) Индексы специальные для этого разового удаления делать както не очень хочется.

порция = 1 месяц

Точно быстрее будет, чем одной транзакцией? По месяцам это будет 15 транзакций, запустил на тестовой, сравню время с удалением одной транзакцией. Но интересно не только из опыта, но и теоретически понять как быстрее всего решить такую задачу.

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

когда в одной транзакции - не знаешь умер процесс или шевелится и ресурсов много надо. а когда квантами - сразу видно, что процесс идет:) а движения почему не удаляешь ?

Конфигурация нетиповая, движения только по одному регистру накопления с одним регистратором, почистил раньше уже.

база большая? в рабочей базе - truncate table, а потом из копии в нее bulk insert нужного

Быстрое   в ы б о р о ч н о е   удаление большого количества записей из раздутого регистра!? Нет сынок, это фантастика - намучились вволю уже. Если подскажете по-настоящему быстрый способ буду благодарен. точно не быстро.

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

Размер удаляемой таблицы 15 GB, с учетом включенного сжатия на SQL.Это много?

Спасибо, кэп.

Регистр пока не рассматриваем, только документы с отбором по дате меньше.

рой в сторону - это не журналируемые операции заточенные на большие разовые объемы данных

тем более - на объектные данные еще и ссылки могут быть

Вы уверены, что исключено?

delete - фигня по скорости, пробовал уже. Я бы попробовал - вот bulk insert может ускорить.

"а потом из копии в нее bulk insert нужного" А как сделать bulk insert из таблицы SQL? Это команда вроде только с файлами работает?

а так? 1) select * into [destinationTable] from [sourceTable] where search_condition 2) подменить sourceTable на destinationTable. 3) похерить sourceTable.

Индексы же потеряются

Уверен. Кроме регистра накопления, который уже очищен, ссылок на этот документ нет.

Ну в теории можно выгрузить в файл - из файла bulk insert, только не факт, что по времени это быстрее будет.

+ = пофиг.

ну можно обойтись insert into

можно через ssis, если он доступен

Я у себя так и делаю - медленно выходит :( Тоже большие таблицы

Вариант - ПодключитьОбработчикОжидания. Интервал вызова и количество документов на удаление обработка читает каждый раз из регистра сведений - можно подбирать нагрузку, чтоб и процесс шел, и пользователи работали. Иногда только так выкручиваемся, когда надо изменять большие объемы в фоновом режиме.

Поставь платформу 8.3 ))) попробуй как удаляет. (офигеешь от разницы в скорости) Попробуй грохнуть в файловом варианте. а. так то в сиквеле 2 варианта 1. Выгрузка данных в промежуточную таблицу (те что надо оставить) truncate потом таблицы. и те что оставил заливаешь обратно 2. delete from tab where tab.date < date

Фоновый режим не вариант, есть 3-4 часа допустимого простоя системы, надо в них уложиться. Пока укладываюсь, но на грани, что-то пойдет не так и проблемы обеспечены...

Не будет платформой быстрей, чем через SQL всеравно, даже пробовать не буду.

delete from можно делать пока все работают

смотря какие блокировки скуль наложит

Эскалацию можно отключить на время

Можно, но в этой задаче не нужно :) Да и потом статистики всеравно пересчитывать, можно словить тормоза на неактуальных статистиках.

На SSD и простой delete за час справится с такой таблицей.

Да, рабочий сервер на SSD да и памяти на скуле побольше, надеюсь раза в 2 быстрее, чем на тестовом будет. Но нет пределов совершенству :)

в данном случае статистика не решает. статистика решает при добавлении

если эти 68 млн Г копились столько лет  и никому не мешали  - ничего не мешает их так же потихоньку удалять. каждый день по 100 тыс и все ...

Кстати - да, работу останавливать совсем не обязательно при удалении частями.

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

вот мы и вернулись к только допишем теперь запускаем это периодично, когда нагрузка на систему ниже определенной психологической отметки.

А удалял командой "УдалитьОбъекты" ?

Это решение совсем другой задачи :)

вам шашечки или ехать?

Мне уточнить у автора

Так намного быстрее будет? Нет, по одному удалял.

Если пользователи не работают, то можно грохнуть перед удалением все лишние индексы ( сильно ускорит, так как в индексах так же проставляется пометка удаления ), оставить только нужный для отбора. Для MS SQL советуют использовать SET NO_BROWSETABLE ON Обсуждение

Да, попробуй

Я принял вашу информацию к сведению :)

А вот это интересно, попробую. Там еще хинт TABLOCK попробовать советуют. А SET NO_BROWSETABLE ON можно через ADODB сделать, или только в менеджмент студии?

Думаю можно. Там как бы на сессию это действует скорее всего

Выбери во временную таблицу, те документы, которые нужно оставить, сделай truncate основной, вставь из временной таблицы обратно.

Выборка может быть медленной ( если объём ), а потом ещё и вставка ( индекс так же будет вставляться ). Проще как я описал. И после удаления пересоздать удалённые индексы ( можно уже руками, предварительно сделав дефрагментацию ).

Не ускорили советы по ссылкам, к сожалению. 2 млн записей удаляется 8 минут.

WITH TABLOCK тоже не ускорил?

>2 млн записей удаляется 8 минут. нормальный результат

Ну, скажем так, приемлемый. Нормальный было бы все 35 млн за 8 минут. Но жить с этим можно, да.

Нет, а вот WITH (TABLOCKX) процентов на 10 ускорил.

Ты все индексы удалил кроме одного где Дата + Ссылка ( Дата в индексе должна быть первой )? Можно ещё статистику подсобрать после удаления индексов приблизительную. И сообщи какие у тебя условия в where. И зачем тебе транзакция в этом действии ( ты хочешь чтобы пользователи в этот момент работали? )

"2 млн записей удаляется 8 минут. нормальный результат 15 летней давности когда запись 20 МБ/сек была нормальной. сейчас ~30 сукунд.

ошибся . относится к

вопрос поставлен в некорректно тк существуют две ситуации  1) избыток вычислительных ресурсов 2 ) недостаток. большинство успешных решений для случая 1) будут не успешными для случая 2)

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

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

Нужно удалить индексы. Они удаляются пометкой удаления без физического удаления. Транзакция создаёт лишний объём работы, да и не нужна она в общем-то ( актуально для oracle с его undotablespace. Скорее всего для M$ так же хотя и не уверен, так как M$ всё равно в лог всё запишет и восстановление идёт из него если что. Транзакция влияет на блокировки ). DELETE работает без транзакции но в лог попадает разумеется, возможно режим логирования SIMPLE уменьшит объём логов ( не уверен. Надеюсь log файл на отдельном диске ). После удаления просто командой DML - CREATE INDEX создаёшь удалённые индексы. Время должно уменьшиться серьёзно, особенно если индексов много, да и индексные файлы сами по себе большие из-за большой таблицы. При относительно маленьком объёме оперативки может происходить много I/O.

Затупил CREATE INDEX это команда семейства DDL

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

OLTP а не OLAP

+ Приведи что в конструкции после WHERE. Если удаление диапазонами, то оставь 1 индекс, где поле Дата будет первым полем и количество полей в индексе минимально.

извините . написал коряво, НО правильно . читайте внимательней

если с таблицей работают люди, то удаление индексов - преступление.

в Время простоя базы ограничено в есть 3-4 часа допустимого простоя системы, надо в них уложиться.

OLAP это аналитика, для неё обычно данные переносятся в другое хранилище, да и нагрузка не такая как на OLTP, так как данные в OLAP обычно агрегируются.

И да после удаления такого объёма нужно как минимум перестраивать индексы данной таблицы + статистику для ускорения доступа ( запросы )

Да, удаление индексов помогло - оставил только кластерный и по периоду, и вместо 8 минут удаление прошло за минуту примерно. Скрипты на удаление и создание индексов очень просто делаются из менеджмент студии, создание индексов делается достаточно быстро.

Да, вы правы, эксплуатацией СУБД занимается отдельный человек, просто для себя интересно чуть глубже понимать механизмы. Главы пересказывать не надо, надо просто подтолкнуть в правильном направлении :)

Если удаление по периоду, то кластерный можно так же грохнуть и потом воссоздать

Можно, но и так вроде неплохо. Сейчас протестирую удаление одним запросом, замерю время создания индексов, и будет понятно стоит ли дальше оптимизировать.

Только что делать, если таблиц - десяток. Руками с ума сойдешь индексы удалять/пересоздавать :( А то мне предстоит скоро таблицы на сотни миллионов записей чистить.

Да скрипт готовишь используя SQL Server Management Studio. Можно ещё программно из 1С скрипт подготовить, используя ПолучитьСтруктуруХраненияБазыДанных

По итогу - 30 млн записей удалились за 16 минут, 28 минут на пересоздание индексов. Дальше оптимизировать смысла не вижу.

Ну да, для одного индекса получить скрипт на drop/create, дальше по аналогии для всех нужных индексов делаешь, и через ADODB запускаешь итоговый скрипт.

А не попробовал?

Пятница только завтра... или ты решил со среды начать народ подогревать?

Мне правда интересно. Я удалял много объектов этой одной командой.

Порциями по 1 млн? Оно не подохнет по памяти?