Реально быстрая очистка документов через SQL

21.02.18

База данных - Чистка данных

Как делать нельзя, но если очень нужно, то можно!

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

У меня камнем преткновения были документы Заказ покупателя, которые имели 4 табличных части и вели свою историю аж с 2005 года. Накопилось там документов порядка полутора миллионов, казалось бы, не так уж много, но удаление их занимало адское количество времени и не давало провести свертку до утра, в связи с чем родился такой маленький  sql.

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

/****** Набор запросов для очистки документов заказы покупателей по дате  ******/

---Как реально быстро почистить документы от даты.... А то обработки на Инфостарте тормозят дружно, даже где написано SQL

--Не забудьте свернуть регистры по документам. Например обработкой свертка и ввод начальных остатков.
--Имена таблиц в SQL можно получить любой обработкой их полно, например SQL БАЗОМЕР (заодно посмотрите, с чего начать)
--После удаления больших таблиц документов и закрытия регистров, даже штатная свертка отрабатывает на ура и быстро.
--Только на тестовой базе сначала, и BACKUP BACKUP BACKUP!!!!!

-- _IDRRef Документа наименьший можно получить запросом 

SELECT TOP 1 FROM [sql_new1].[dbo].[_Document134]
WHERE _Date_Time < '2016-01-01 00:00:00.000'
ORDER BY _IDRRef

-- По дате первый IDRRef брать НЕЛЬЗЯ! Надо именно по значению. Иначе можете удалить нужные табличные части заказов.
-- Чистим табличные части:

DELETE
FROM [sql_new1].[dbo].[_Document134_VT2446]
WHERE _Document134_IDRRef < 0x8267002590D8EFB911E5891E7F179918

DELETE
FROM [sql_new1].[dbo].[_Document134_VT2468]
WHERE _Document134_IDRRef < 0x8267002590D8EFB911E5891E7F179918

DELETE
FROM [sql_new1].[dbo].[_Document134_VT2476]
WHERE _Document134_IDRRef < 0x8267002590D8EFB911E5891E7F179918

DELETE
FROM [sql_new1].[dbo].[_Document134_VT2489]
WHERE _Document134_IDRRef < 0x8267002590D8EFB911E5891E7F179918

-- И сам документ

DELETE
FROM [sql_new1].[dbo].[_Document134]
WHERE _IDRRef < 0x8267002590D8EFB911E5891E7F179918


-- если много, удаляем кусками, чтобы юзеры даже не почуяли...
DECLARE @RowsDeleted INTEGER 
DECLARE @RowsTo INTEGER 
SET @RowsDeleted = 1 
SET @RowsTo= 1 

WHILE (@RowsDeleted > 0 and @RowsTo < 16000000) 
BEGIN 
DELETE TOP (50000) FROM _Document134 WHERE _Date_Time < '2016-01-01 00:00:00.000' 
SET @RowsDeleted = @@ROWCOUNT 
SET @Rowsto = (@Rowsto+@RowsDeleted) 
-- WAITFOR DELAY '00:00:001' --чтоб можно было работать без блокировки таблицы, можно удалить
END 

свертка удаление sql

См. также

Удаление данных с отбором и построением дерева ссылок в базах 1С 8.1-8.3 УТ 10.3./11, БП 2/3, ЗУП 2.5/3, КА 1.1/2, УНФ 1.6/3.0

Чистка данных Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 1С:Управление торговлей 10 1С:Управление торговлей 11 Платные (руб)

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

9600 руб.

22.02.2013    135937    248    144    

418

Универсальное выборочное удаление данных из базы 1С (любые конфигурации на упр.формах: БП 3.0, УТ 11, КА 2, ERP, УНФ, ЗУП 3, Розница и т.д.)

Чистка данных Платформа 1С v8.3 Управляемые формы 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Зарплата и кадры государственного учреждения 3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Платные (руб)

Обработка позволяет удобно выборочно удалить данные из базы 1С. Это могут быть как неиспользуемые элементы справочников, так и неактуальные организации. При этом есть возможность провести анализ пересечений документов с другими организациями и таким образом уберечься от того, что при удалении обороты по другой организации изменятся.

3350 руб.

28.11.2019    24314    46    16    

58

SALE! 10%

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

Чистка данных Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Платные (руб)

Позволяет удалить организации из любых программ на управляемых формах (БП 3.0, УТ 11, КА 2, ERP 2, ЗУП 3.0, УНФ, Розница 2.0 и пр.). Главное требование - программа должна содержать справочник "Организации". Реализован самый быстрый алгоритм непосредственного удаления объектов. Работает даже на базах большого размера. Для ускорения работы алгоритма не запускается проверка контроля ссылочной целостности. Проверку учета можно запустить отдельно с помощью дополнительной обработки. Необходимо перед удалением самостоятельно проверить базу на наличие перекрестных ссылок разных организаций в одном документе. Эту дополнительную обработку проверки перекрестных ссылок по запросу предоставляем бесплатно нашим покупателям.

3980 3582 руб.

16.03.2015    113718    181    75    

217

Замена Номенклатуры+Характеристики

Чистка данных Логистика, склад и ТМЦ Платформа 1С v8.3 План видов характеристик 1С:Комплексная автоматизация 1.х 1С:Управление производственным предприятием 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Платные (руб)

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

3600 руб.

04.08.2015    41117    88    70    

49

Удаление битых ссылок 1С в базе без монопольного режима

Чистка данных Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Если в вашей информационной базе крутится очень много данных, или база должна быть доступна 24/7 (как в моем случае), или же вы боитесь запускать тестирование и исправление, НО существует потребность удалить битые ссылки, тогда эта обработка сможет Вам помочь. Обработка выявляет битые ссылки как в самих объектах метаданных, так и в их табличных частях(!), а так же может их удалить.

2400 руб.

23.08.2021    9261    15    3    

20

Очистка дублей в синхронизированных базах (УТ, БП, РТ, УНФ)

Чистка данных Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

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

3960 руб.

27.06.2018    18808    10    3    

15

Очистка регистров сведений от записей по помеченным на удаление элементам

Чистка данных Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

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

1200 руб.

21.01.2022    7129    5    6    

9

Очистка кэша 1С. Исполнитель

Чистка данных Инструментарий разработчика Платформа 1С v8.3 Абонемент ($m)

Очередная вариативная очистка кэша 1С с помощью Исполнителя 3.0.2.2.

1 стартмани

25.10.2023    4534    4    SerVer1C    25    

22
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Константин С. 666 21.02.18 14:29 Сейчас в теме
Реально быстрая очистка
format c:
Roman_SE; jif; корум; ice-net; chebser; Dmitri93; +6 Ответить
2. Gilev.Vyacheslav 1910 21.02.18 14:51 Сейчас в теме
изобретение велосипеда, серия 100500-я
зачем изучать что было сделано до тебя, когда можно самому изобрести велосипед... )))
нужное во временную таблицу, остальное транкейтим, потом из временной таблицы назад нужное...
Roman_SE; jaroslav.h; корум; nvv1970; Cерый; chebser; GreenDragon; Morkhe; kalyaka; +9 Ответить
11. Roman_SE 9 22.02.18 17:21 Сейчас в теме
(2)

Delete в цикле позволяет делать фоном.
3. vano-ekt 123 21.02.18 14:55 Сейчас в теме
битые ссылки в борьбе за 5 гигов места ... шел 2018 год
chebser; GreenDragon; +2 Ответить
5. GreenDragon 21.02.18 17:01 Сейчас в теме
(3) Ну, так-то в нашей базе на 500+ гиг таким варварским методом можно базу процентов на 10-20 уменьшить... Но метод ужасен, да
13. Roman_SE 9 22.02.18 17:24 Сейчас в теме
(5)

не про это было, а про дополнение к обработкам свертки, которые делетят намного медлннее. Выигрыш, не в объеме, а в производительности (у меня допустим индексы очень много занимают и я хочу чтобы они целиком в оперативке были) и в бекапах.
12. Roman_SE 9 22.02.18 17:22 Сейчас в теме
(3)

Не за 5 гигов места, а за производительность в бекапах, да и отрезать надо было начало учета, где вообще все сверхкриво велось.
4. Marik 76 21.02.18 15:57 Сейчас в теме
Delete - плохо
Truncate - рулит
14. Roman_SE 9 22.02.18 17:26 Сейчас в теме
(4)
Да кто спорит, но тут можно на работающей базе споконой чистить хвосты постепенно. Truncate замечательно конечно, но это надо и таблицы создавать и тд...А если таблица весит 50 гигов, а стереть надо в ней только первые 2-3 года...
16. nvv1970 25.02.18 11:04 Сейчас в теме
(14) да, если это не остатки, то можно и прямо во время работы.
А если остатки?
В теории тоже можно скулем самому очистить движения до даты, и остатки до даты, а первые остатки записать в движения на какой-либо регистратор (даже битый - пофиг). Если вдруг кто-то пересчитает итоги - все будет норм.
А если это хозрасчетный? А если нужно в остатках поправить пустые ссылки в валюте, неверные типы в субконто и прочие косяки древних кривых данных? Все становится не так просто. Без монопольного доступа к регистру - никак.
Ну и представьте, что из 500 млн строк нужно удалить 400 )))))) делете будет выполняться день как минимум. А если это full recovery ??? (Даже и представить не могу, что может быть у кого simple)
6. ksnik 578 21.02.18 20:25 Сейчас в теме
Еще до кучи -- SELECT -- остатки товаров
--DECLARE @XXX binary(16)
--SET @XXX = (SELECT _IDRref
--FROM _Reference55
--where _Code = 'ЦБ005')

--SELECT @XXX

--SELECT _AccumRg6465._Period AS Период, _AccumRg6465._Fld6469 AS Количество, _Reference55._Description AS Организация,
-- _Reference52._Description AS Номенклатура, _Reference70._Description AS СкладКомпании
--FROM _AccumRg6465

--LEFT OUTER JOIN
-- _Reference70 ON _AccumRg6465._Fld6467RRef = _Reference70._IDRRef LEFT OUTER JOIN
-- _Reference52 ON _AccumRg6465._Fld6466RRef = _Reference52._IDRRef LEFT OUTER JOIN
-- _Reference55 ON _AccumRg6465._Fld7882RRef = _Reference55._IDRRef
--WHERE (_AccumRg6465._Period BETWEEN CONVERT(DATETIME, '2010-12-31 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 23:59:59', 102))

SELECT COUNT(_AccumRg6465._Fld7882RRef)
FROM _AccumRg6465
7. ksnik 578 21.02.18 20:26 Сейчас в теме
-- UPDATE_ВзаиморасчетыКомпании
DECLARE @XXX binary(16)
SET @XXX = (SELECT _IDRref
FROM _Reference55
where _Code = 'ЦБ005')

--SELECT @XXX


update
_AccumRg6328
set
_Fld7886RRef = @XXX
FROM _AccumRg6328
--WHERE (_AccumRg6328._Period BETWEEN CONVERT(DATETIME, '2010-12-31 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 23:59:59', 102))
8. ksnik 578 21.02.18 20:27 Сейчас в теме
-- UPDATE_ДенежныеСредстваКомпании
-- DECLARE @XXX binary(16)
SET @XXX = (SELECT _IDRref
FROM _Reference55
where _Code = 'ЦБ005')

--SELECT @XXX


update
_AccumRg6351
set
_Fld7888RRef = @XXX
FROM _AccumRg6351
--WHERE (_AccumRg6328._Period BETWEEN CONVERT(DATETIME, '2010-12-31 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 23:59:59', 102))
15. Roman_SE 9 22.02.18 17:28 Сейчас в теме
(8)

Регистры неплохо сворачиваются автоматически обработками по свертке базы. Там нажал получил. А документы просто долго реально любыми, поэтому после свертки документы почистить можно либо delete либо как выше рекомендуют селектами в овременные, но там телодвижений больше, а тут запустил и забыл.
9. пользователь 21.02.18 20:28
Сообщение было скрыто модератором.
...
10. пользователь 21.02.18 20:29
Сообщение было скрыто модератором.
...
17. МихаилМ 25.02.18 12:05 Сейчас в теме
меня пугает

_IDRRef < 0x8267002590D8EFB911E5891E7F179918

тк обычно _IDRRef - случайное значение для разных сессий, а в пределах сессии - инкрементальное.
18. Roman_SE 9 26.02.18 01:12 Сейчас в теме
(17) Серьезно? оно сквозное на всю таблицу, как оно случайным может быть?
19. МихаилМ 26.02.18 01:53 Сейчас в теме
(18) тогда подумайте логически , сколько в базе должно быть документов , чтобы достичь значения 0x8267002590D8EFB911E5891E7F179918

для инкрементального счетчика 4 байт хватилобы вместо 16
20. Roman_SE 9 26.02.18 02:51 Сейчас в теме
(19) Ну в 1с много что сделано с запасом. А все же откуда уверенность в случайности? или случайное все же берется больше чем последнее?
23. Roman_SE 9 26.02.18 15:54 Сейчас в теме
(22) Убедили, но проверка на практике говорит, что все же он может и местами случайный, но инкрементальный. То что там много байт длина это не значит что значимые не последние 5-6 всего, а начало уникально для базы или справочника
24. Roman_SE 9 26.02.18 15:55 Сейчас в теме
(23) инкрементальный в том смысле, что возрастающий ,а не что a++
25. Roman_SE 9 27.02.18 11:25 Сейчас в теме
(22) ну напрочь рандомны он там только на справочниках , а на регистрах там он генерится из даты... другое дело что там в дате похоже разряды перемешаны, Поэтому прям от номера отрезать нельзя, можно зацепить полдня того же, вы правы, я еще раз перепроверю как оно вживую происходит.
26. avusachev 2 20.09.18 20:08 Сейчас в теме
(17) Тоже режу базу таким же макаром. У нас PostgreSQL. Ну и, чтобы, совсем не бояться _IDRRef решил вот так делать:

DELETE FROM _document397_vt9019 WHERE _document397_vt9019._document397_idrref IN (SELECT _idrref FROM _document397 WHERE _date_time < '2016-01-01 00:00:00');
DELETE FROM _document397 WHERE _date_time < '2016-01-01 00:00:00';
Roman_SE; +1 Ответить
21. fishca 1254 26.02.18 08:50 Сейчас в теме
1. Удалять нужно периодами
2. Про журналы документов нужно еще не забыть, т.к. это отдельная таблица.
3. Табличные части / движения документов, потом сами ссылки
Оставьте свое сообщение