Удаление данных напрямую в SQL

Администрирование - Сервисные утилиты

Порой так и хочется грохнуть что нить в скуле, но наталкиваешься на отсутствие понятных и функциональный инструментов. Вроде как что то и есть, но явно писанное для каких то конкретных простых целей. И так очередное творение в данной тематике. Особенности: удаление порциями, отображение готового текста запроса, отбор данных с помощью СКД (в отборе учитываются составные типы и обращения через точку).

Работу с обработкой можно поделить на 3 части:

1) Выбор объектов для удаления и настройка отборов

2) Формирование текстов запросов

3) Выполнение запросов

Объекты и их настройки

Объекты

Удалить можно следующие объекты:

  • Справочники (+ТЧ, Подчиненные с их ТЧ, Подчиненные подчиненных и тд)
  • Документы (+Движения, Журналы, Последовательности, ТЧ)
  • Регистры сведений (только основная таблица)
  • Регистры накопления (только основная таблица)
  • Регистры бухгалтерии (только основная таблица, +Субконто)
  • Бизнес процессы (+ТЧ, Задачи с их ТЧ - не учитываются вложенные БП)
  • Задачи (+ТЧ)

Регистры расчета пока что не включены в этот список, буду уже смотреть по количеству желающих.

То что указано в скобках тоже удаляется вместе с основным объектом.

Поясню ситуацию с вложенными БП при удалении основного: реализация подобного пока что мне кажется слишком трудоемкой, и возможно не нужной. Анализировать карту маршрута для выявления какой именно БП может быть вложенным - не предлагать, так как мой опыт говорит об использовании совсем не тривиальных схем с переопределением БП. Самый простой способ - это удалять БП с учетом порядка и правильного отбора.

Отборы

Для настройки отборов в таблице есть специальная колонка, проваливаемся туда и получаем полноценный редактор отборов СКД:

Отбор можно делать только по реквизитам (основным, стандартным, общим и тд), по ТЧ нельзя. Отбор можно делать по полям полей (через точку), количество уровней не ограничено. В отборе можно применять любые группировки условий (И, ИЛИ, НЕ).

Поддерживаются следующие виды сравнения:

  • Равно
  • Не равно
  • Больше
  • Больше или равно
  • Меньше
  • Меньше или равно
  • Соответствует шаблону
  • Не соответствует шаблону
  • В списке
  • Не в списке

Другие виды сравнения будут выдавать ошибку при формировании запросов. К сожалению я не нашел простого способа оставить только разрешенные виды, хитрый СКД все равно подставляет свое, поэтому тратить время на написание своего отборщика не стал.

Если кому то нужно сделать отборы "В группе", то для этого сделайте группу отборов "ИЛИ" и добавьте отборы вида Родитель.Родитель.Родитель и тд. Да, этот вариант будет не производительным как у 1С, да и я тут не стал особо заморачиваться с этим, но тем не менее для данной обработки в самый раз.

Составные типы

Отдельно стоит остановиться на составных типах. В обработке учтено практически все что с ними связано). Основная сложность это обработка обращений через точку, аля вида Регистратор.Дата. В данном случае к основной таблице будут соединятся все, которые только может содержать составной тип (собстно как и у 1С). Так же учтено что конечного реквизита может и не быть у некоторых таблиц и их в итоговом запросе не будет: как пример имеем отбор Регистратор.СкладОтправитель, и из 10 документов-регистраторов только 2 имеют конечный реквизит СкладОтправитель, вот именно эти 2 таблицы и будут присоединены к основной.

Можно делать отбор по значению НЕОПРЕДЕЛЕНО.

Сохранение и загрузка настроек

Все сделанные настройки можно сохранить для последующего повторного использования. Это может пригодиться для выполнения регламента подготовки базы для разработки или для удаления в нескольких базах.

Формирование запросов

Для того чтобы получить запросы нужно нажать на кнопку "Получить запросы БД". После этого по всем отмеченным объектам будут сформированы запросы на основании отборов. Сам текст будет размещен в соответствующей колонке. На данном этапе настройку подключения делать не нужно.

Текст запроса содержит в себе:

  • Объявление некоторых переменных, одна из которых это YearOffset, так как на момент формирования оффсет еще не известен, то он закоменчен.
  • Порции запросов на выполнение, разделенных строкой --GO. Чуть позже напишу зачем.
  • Цикл выполнения порции запроса с учетом значения в поле "Размер порции"

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

При построении отборов с применением даты, учитывается оффсет как равный 0, так и 2000. Пустые даты при этом соответственно подгоняются под него.

Соединения для элементов отборов формируются независимо. Как пример есть 2 элемента отбора Родитель.Родитель и Родитель.Родитель.Родитель, так вот в итоговом тексте запроса будет 2 соединения Родитель.Родитель. Да, одно соединение лишнее, но так было проще сделать. Будем надеется что великий скуль все заоптимизирует).

Если отборов нет, то будет TRUNCATE TABLE. Но так как с объектом удаляются еще и зависимые и они могут содержать в себе не только данные удаляемого, как пример регистр накопления, где может быть несколько регистраторов, то для них данная ситуация учитывается и будет не TRUNCATE зависимого, а удаление по типу объекта.

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

Если что не нравится в запросе, то можно его и отредактировать, провалившись в соответствующее поле.

Пример текста запроса:

--declare @YearOffset int = ?
declare @RowsDeleted int
--GO

--РегистрНакопления.Продажи
SET @RowsDeleted = 1
WHILE (@RowsDeleted > 0)
	BEGIN
		DELETE TOP (100000) AccumRg53 
		FROM  
			_AccumRg53 AS AccumRg53 with (tablockx)
		
		INNER JOIN --Документ.РеализацияТоваровУслуг
			_Document35 AS Document35 with (nolock)
		ON
			AccumRg53._RecorderRRef=Document35._IDRRef
			AND AccumRg53._RecorderTRef=CAST(35 AS binary(4))
		
		WHERE
			(--Номер
			Document35._Number = '111'
			)

		SET @RowsDeleted = @@ROWCOUNT
	END
--GO

--Документ.РеализацияТоваровУслуг.Товары
SET @RowsDeleted = 1
WHILE (@RowsDeleted > 0)
	BEGIN
		DELETE TOP (100000) Document35_VT45 
		FROM  
			_Document35_VT45 AS Document35_VT45 with (tablockx)
		
		INNER JOIN --Документ.РеализацияТоваровУслуг
			_Document35 AS Document35 with (nolock)
		ON
			Document35_VT45._Document35_IDRRef=Document35._IDRRef
		
		WHERE
			(--Номер
			Document35._Number = '111'
			)

		SET @RowsDeleted = @@ROWCOUNT
	END
--GO

--Документ.РеализацияТоваровУслуг
SET @RowsDeleted = 1
WHILE (@RowsDeleted > 0)
	BEGIN
		DELETE TOP (100000) Document35 
		FROM  
			_Document35 AS Document35 with (tablockx)
		
		WHERE
			(--Номер
			Document35._Number = '111'
			)

		SET @RowsDeleted = @@ROWCOUNT
	END
--GO

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

Посмотреть только отмеченные объекты для формирования/выполнения запросов можно по кнопке со стрелкой (рядом с кнопкой получения запросов). Это очень удобно когда их много и они разбросаны по разным коллекциям метаданных.

Выполнение запросов

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

Тут же есть кнопка проверки соединения.

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

При нажатии на кнопку выполнения производятся следующие действия:

  • Установка соединения
  • Построение запросов с учетом порядка
  • Получение YearOffset за место закоменченного
  • Разбитие текста запроса на порции по разделителю --GO, другими словами выполняется не весь запрос целиком, каждая порция отдельно, при этом первая порция объявления переменных присоединяется ко всем последующим
  • Вывод состояния выполнения с прогресс баром

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

По окончанию выводится информация о длительности и количестве удаленных.

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

Ну и шринкануть базу для получения эффекта)

PS

Хоть я и тестировал все при все, но ошибок в таких обработках не может не быть)) если что то не так работает как тут описано, или есть ошибки - пишите все исправлю поправлю.

При проверки на порцию в 100000 объектов мне хватало 700-800мб журнала транзакций, но конечно же все зависит от таблицы.

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

Нынешние правила не позволяют опубликовать бесплатно, поэтому 1sm.

Сравнение версий

v1.2 - добавил информацию о количестве удаляемых/удаленных, добавил сохранение и загрузку настроек

v1.1 - избавился от функций СтрНайти, СтрРазделить. Платформа могла ругаться в режиме совместимости с версией ниже 8.3.6

Скачать файлы

Наименование Файл Версия Размер
Удаление данных напрямую в SQL:
.epf 34,90Kb
02.07.17
20
.epf 1.2 34,90Kb 20 Скачать

См. также

Комментарии
1. Константин Соболевский (Константин С.) 489 10.07.17 12:59 Сейчас в теме
Вопросик
Если открыть в КА 1.1, запущенную УФ. Выдает ошибку

Ошибка инициализации модуля: ВнешняяОбработка.УдалениеДанныхБД.Форма.Форма.Форма
по причине:
{ВнешняяОбработка.УдалениеДанныхБД.Форма.Форма.Форма(1482,12)}: Процедура или функция с указанным именем не определена (СтрНайти)
Позиция = <<?>>СтрНайти(Строка, Разделитель);


1С:Предприятие 8.3 (8.3.9.2233)

также проверил на УТ 10, запущенной как УФ.

Как понимаю проблема "старых" конфигураций, ее можно решить?)
2. Евгений Заручейский (zarucheisky) 10.07.17 13:02 Сейчас в теме
(1)
В Форму обработки добавьте функцию:
Функция СтрНайти(ГдеИскать,ЧтоИскать)
возврат Найти(ГдеИскать,ЧтоИскать);
КонецФункции
3. Константин Соболевский (Константин С.) 489 10.07.17 13:16 Сейчас в теме
Это получилось.

Нарисовалось еще

МассивЧастейПоля=<<?>>СтрРазделить(ИмяПоля,"."); //Регистратор.Партнер.Код (Проверка: Толстый клиент (обычное приложение))
4. Константин Соболевский (Константин С.) 489 10.07.17 13:38 Сейчас в теме
Решил
МассивЧастейПоля=РазложитьСтрокуВМассивПодстрок(ИмяПоля,".");
5. Ildar Gabdrakhmanov (spezc) 298 10.07.17 13:41 Сейчас в теме
Опасная вещь. Для многих будет гранатой в руке.
6. Ildar Gabdrakhmanov (spezc) 298 10.07.17 13:41 Сейчас в теме
Но инструмент 100% нужный, спасибо.
7. Константин Соболевский (Константин С.) 489 10.07.17 13:53 Сейчас в теме
Нет счастья

ps:
Моя ощибка) нет к той базе подключался.
8. Константин Соболевский (Константин С.) 489 10.07.17 14:22 Сейчас в теме
Предложение.
выводить статистику по выбранным объекта, можно с учетом отборов. Сразу понимать объем удаляемых объектов (без подчиненных элементов типа движения у документов)
9. Михаил Чернышев (smilebringer) 10.07.17 18:57 Сейчас в теме
Как обстоят дела со ссылками на удаляемые объекты в таблицах последовательностей, таблицах регистрации обменов, служебных таблицах итогов регистров, таблицах агрегатов? На выходе будут битые ссылки?
10. Денис Sh (denacid) 43 10.07.17 19:18 Сейчас в теме
Старался избегать новомодных функций типа СтрНайти, СтрРазделить, но вижу что несколько упустил, исправлю
11. Денис Sh (denacid) 43 10.07.17 19:19 Сейчас в теме
(8) хм, записал в пожелания
12. Денис Sh (denacid) 43 10.07.17 19:24 Сейчас в теме
(9) Последовательности удаляются вместе с документами. Регистрацию обменов специально не учитывал, при нормальном использовании обмена там записей не должно быть много или вообще не должно быть. По регистрам только основные таблицы - в описании про это сказано.
13. Филипп Владимирович (spectator2z) 11.07.17 08:01 Сейчас в теме
В чем конечный замысел данного функционала? Даёт существенный прирост производительности операции удалёния по сравнению с платформенной? Есть замеры?
14. Станислав Патырило (wondermaker) 11.07.17 08:15 Сейчас в теме
Т.е. итоги по регистру обработка не чистит?

"Для уменьшения таблиц итогов регистров выполнить пересчет итогов, реструктуризацию "

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

Я бы такое рискнул делать только на тестово-разработческих базах
15. Алексей Роза (DoctorRoza) 11.07.17 16:57 Сейчас в теме
16. Денис Sh (denacid) 43 11.07.17 19:00 Сейчас в теме
(14) По формулировке с вами согласен, дополню.
17. Денис Sh (denacid) 43 11.07.17 19:02 Сейчас в теме
(13) Удалить 100 000 объектов по одному или разом? тут замеры не нужны)))
18. Денис Sh (denacid) 43 11.07.17 19:05 Сейчас в теме
(14)
Я бы такое рискнул делать только на тестово-разработческих базах


Изначально именно для этих целей обработка и создавалась).
19. Филипп Владимирович (spectator2z) 12.07.17 07:21 Сейчас в теме
(17) Ну можно удалять ни по одному а в транзакции сразу тысяч по 10. Интересно конечно сравнение по скорости.

Кстати есть ли возможность отбора по разделителю данных? У меня области данных в которых есть десятки миллионов записей справочников нси, нужно удалять все данные области.
20. Андрей Андрей (user791868) 12.07.17 07:40 Сейчас в теме
Если нужно удалить данные из таблицы БД то тут
DELETE FR OM <имя таблицы > WH ERE <предикат>;

Если нужно выполнить хранимую процедуру то тот нужно знать что за процедуру и какие она использует аргументы в запросе.
Как это делается можно тут по читать https://msdn.microsoft.com/ru-ru/library/ms188332%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
21. Антон Паскаль (platon_) 10 12.07.17 17:27 Сейчас в теме
Добрый день.
А нарушение лицензионного соглашения с 1С не смущает?

Лицензионное соглашение не позволяет использовать недокументированные фирмой "1С" средства для построения решений на платформе 1С:Предприятие. Это означает, что средства СУБД (или любые другие внесистемные средства) можно использовать только в том случае, если документация по продуктам линейки "1С:Предприятие" (включая ИТС) содержит явную рекомендацию использовать данное средство для решения данной задачи.

Во всех остальных случаях лицензионное соглашение позволяет использовать для построения решений только штатные средства платформы. В частности, можно обращаться к данным информационной базы только при помощи объектов "1С:Предприятия", специально предназначенных для работы с данными (запросы, справочники, документы и т.д.). Нельзя обращаться к данным информационной базы напрямую, минуя уровень объектов работы с данными "1С:Предприятия" - например при помощи средств СУБД или при помощи внешних компонент, которые реализуют прямой доступ к СУБД. Это ограничение распространяется на любые действия с данными, в том числе на изменение их структуры, а так же на чтение или изменение самих данных информационной базы или служебных данных "1С:Предприятия".

Данное ограничение необходимо для обеспечения стабильности работы механизмов системы, осуществления поддержки и возможности перехода на новые версии "1С:Предприятия".
22. Денис Sh (denacid) 43 12.07.17 19:34 Сейчас в теме
(21) Добрый день. Смущает конечно, но иногда по другому никак. Я не заставляю использовать эту обработку как панацею. Если есть возможность все сделать штатными средствами, то лучше так и сделать.
23. Денис Sh (denacid) 43 12.07.17 19:45 Сейчас в теме
(19)
Ну можно удалять ни по одному а в транзакции сразу тысяч по 10. Интересно конечно сравнение по скорости.

Ну возьмем к примеру справочник. Удалить мы можем элемент только вот примерно таким кодом:
Объект=Ссылка.ПолучитьОбъект();
Объект.Удалить();
Всего 2 строчки кода, но что стоит за ними: получение объекта - SELECT из SQL всего объекта, ну и DELETE напоследок. Итого имеем 2 запроса к SQL. А теперь мы хотим удалить 5 млн элементов и соответственно перемножаем цифры. И тут я не учитываю что платформа захочет сделать запись в журнал регистрации, может и SQL что нибудь у себя перестроит в этот момент, а так же будет не 2 запроса, а 2 обращения по одному запросу и тд.
24. Денис Sh (denacid) 43 12.07.17 19:50 Сейчас в теме
(19)
Кстати есть ли возможность отбора по разделителю данных?

Разделитель - это общий реквизит. Отбор и удаление по общим реквизитам предусмотрено, так что можно.
Оставьте свое сообщение