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

Публикация № 787754 21.02.18

Администрирование - Администрирование данных 1С - Чистка базы

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

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

Я никого не призываю и не рекомендую так делать, но если время дорого, а свернуть базу хочется, то без этих запросов никак. Обработки по свертке даже с 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 

Специальные предложения

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Константин С. 703 21.02.18 14:29 Сейчас в теме
Реально быстрая очистка
format c:
Roman_SE; jif; корум; ice-net; chebser; Dmitri93; +6 Ответить
2. Gilev.Vyacheslav 1883 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 854 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 72 21.02.18 15:57 Сейчас в теме
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 537 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 537 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 537 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 1208 26.02.18 08:50 Сейчас в теме
1. Удалять нужно периодами
2. Про журналы документов нужно еще не забыть, т.к. это отдельная таблица.
3. Табличные части / движения документов, потом сами ссылки
Оставьте свое сообщение

См. также

Хранилище версий объектов в условиях массовых изменений

Чистка базы Конфигурирование 1С v8 1cv8.cf Бесплатно (free)

Проблема хранения версий объектов при огромном количестве изменений.

08.11.2020    1243    Punisher_1C    4    

Быстрый способ удаления записей из журнала регистрации 1С

Журнал регистрации Чистка базы v8 Бесплатно (free)

Возникают ситуации, когда необходимо выборочно удалить из журнала регистрации определенные записи. Например, размер файлов журнала неконтролируемо вырос из-за неправильно сконфигурированного регламентного задания. С помощью Perl журнал можно очистить от лишних записей всего одной строкой.

06.08.2020    4596    DataReducer    7    

Подсистема учета характеристик. Стремление к совершенству

Чистка базы Практика программирования v8 v8::ОУ v8::ПВХ УТ10 УУ Бесплатно (free)

Цель - почистить задвоенность характеристик и предотвратить дальнейшее ее появление.

16.12.2019    4546    Rustig    0    

Хранение файлов - как уменьшить размер базы данных

Чистка базы Производительность и оптимизация (HighLoad) Практика программирования Разработка v8 Россия Бесплатно (free)

Хранение файлов в базе 1С можно оптимизировать для уменьшения размера хранимых данных.

09.09.2019    9945    2tvad    17    

Удаление записей регистров и пересчет итогов в условиях нехватки места на диске

Чистка базы v8 1cv8.cf Россия Бесплатно (free)

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

17.04.2019    4766    semensemenbi4    9    

Как быстро "удалить все документы и движения" в базе (Альтернатива)

Чистка базы v8 Бесплатно (free)

Очень часто задают вопрос - как удалить все документы из базы, оставить только номенклатуру и настройки. Есть много различных вариантов (групповая обработка документов и справочников - да, работает, но медленно, у клиента в базе 460 000 документов проведенных - неделю будет удалять). Перенос данных - через ВыгрузкаЗагрузкаДанныхXML82.epf - можно, но тоже долго и требует определенных навыков.

06.03.2018    28421    Tatitutu    24    

Если при рассылке закончилось место

Чистка базы v8 Россия Бесплатно (free)

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

23.03.2017    8926    hydro2588_2015    19    

Удаление лишних данных

Чистка базы v8 Бесплатно (free)

Настало то время, когда на SSD винчестерах размером 120 Гб начало заканчиваться место.  В связи с этим, было принято решение проанализиривать возможность уменьшения занимаемого места БД. Без веского обоснования у нас никакие работы не производятся, просто так оптимизацией никто заниматься не даст. 1. Выявление таблиц БД, занимающих много места. 2. Очистка записей регистров накопления. 3. Тестирование.

25.11.2016    15073    HAMMER_59    10    

Версионирование объектов в 1С УПП 1.3. Фикс дублей

Чистка базы Производительность и оптимизация (HighLoad) v8 УПП1 Россия Бесплатно (free)

В 1С УПП 1.3 есть такой замечательный механизм "версионирование объектов", который позволяет узнать: кто, когда и что изменил в документах и справочниках. Но есть недочеты в этой системе, программа не проверяет, является ли текущая записываемая версия объекта дублем (аналогична предыдущей версии), что в итоге захламляет базу и существенно увеличивает её объем.

1 стартмани

16.11.2015    13873    tehas    4    

БГУ 1.0. Чистим отчётность

Чистка базы v8 БГУ Государственные, бюджетные структуры Россия БУ Бесплатно (free)

Временами встречается проблема с заполнением бюджетной (бухгалтерской) отчётности в БГУ 1.0. По разным причинам отчётность может заполняться неправильно или не заполняться совсем. В данной статье приведена методика быстрой «чистки» отчётности.

29.09.2015    17360    Sergey123321    8    

Задвоение элементов плана счетов в базе на SQL.

Чистка базы v8 Бесплатно (free)

Иногда приходится разбираться с "грехами отцов", когда приходишь к клиенту, а до тебя наделали делов. Вот, собственно, проблема: Настраивали когда-то план обмена, все работало штатно. Потом обмен пошел не как надо и в справочниках появились задвоенные предопределенные элементы. В общем, на Инфостарте нашел кучу обработок, которые помогут. Но тут оказалось, что если работать с элементом "ПланСчетов", эти обработки бесполезны.

27.08.2015    8934    vasyalisk88    17    

Фоновое Задание "Удаление помеченных объектов" (не монопольно)

Чистка базы v8 1cv8.cf Россия Бесплатно (free)

Подробное описание немонопольного удаления помеченных объектов, реализованное как Фоновое Задание.

02.08.2013    22338    shvo65    10    

Как свертывать большую базу 1C 8 в максимально короткие сроки (1С 8.1, 8.2)

Производительность и оптимизация (HighLoad) Чистка базы Свертка базы v8 УТ10 Бесплатно (free)

Как свертывать большую базу 1C 8 в максимально короткие сроки (1С 8.1, 8.2). Любая база данных со временем разрастается до огромных размеров, что делает его более медлительным и работать в нем порой бывает очень тяжело. По этому периодически базу свертывают, т.е. до определенной даты все документы и движения удаляются и вводятся начальные остатки. Соответственно всегда есть архивная база за прошлые периоды. Пример свертки базы 1С на SQL приведен здесь.

14.06.2013    65050    setrak    21    

Шринк лога транзакций MS SQL 2008/2012 в экстренном случае или боремся с ошибкой HRESULT=80040E14

Чистка базы v8 Бесплатно (free)

Пошаговая инструкция по уменьшению лога транзакций (*.ldf) MS SQL 2008/2012.

15.01.2013    329086    Kserken    50