Быстрая свертка базы 1С через MSSQL

Публикация № 1397528 05.03.21

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

Быстрая свертка базы 1С через MSSQL. Здесь предлагается обработка для свертки базы, использующая прямое редактирование таблиц в MSSQL и позволяющая в разы сократить время сей операции.

Быстрая свертка базы 1С через MSSQL.

 

Во встроенном языке 1С есть функция ПолучитьСтруктуруХраненияБазыДанных, которая дает нам дополнительные возможности по работе с базой данных 1С. Прямое обращение к таблицам позволяет в некоторых случаях значительно ускорить операции с БД.

Здесь предлагается обработка для свертки базы использующая прямое редактирование таблиц в MSSQL и позволяющая в разы сократить время сей операции.

Обработка предназначена для использования программистами 1С. Настройки определены в модуле формы в процедуре ПриСозданииНаСервере. Из требований, это собственно сам MSSQL сервер, а также сервер и клиент 1С предприятия на виндус платформе. В виндузе, где запускается сервер 1С, должен быть установлен ODBC драйвер SQL Server с разрядностью соответствующей платформе 1С.

 

 

Как это работает.

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

 

    

 

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

 

  или  

 

Отключите на время использование Регламентированных заданий во избежание, так сказать. Только не галочкой в консоли администрирования сервера, которая отключает все, т.к. мы будем использовать фоновые задания, они без нее тоже не работают. В конфигурации должен существовать документ КорректировкаЗаписейРегистров, в движениях которого должны быть выбраны регистры, по которым будут формироваться итоги на дату свертки. Это регистры накопления с остатками и зависимые от регистратора периодические регистры сведений. Обычно они и так выбраны в исходной конфигурации, но добавленные позже при доработках – нет. Если такого документа нет, то подгрузите его откуда-нибудь.

 

 

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

По ходу экспериментов выяснилось, что платформа помнит только 1000 завершенных заданий. Ну действительно, я пытался разыскать в инете, что происходит с завершенными заданиями, как минимум их время жизни. Не нашел. И вопрос остался. С одной стороны, если задание завершено, то кто его знает через сколько времени я полезу посмотреть из основного потока чем там все закончилось. С другой стороны, сервер может работать много лет (смайлик) без перезапуска, нельзя все время только накапливать завершенные задания. Так что, если у вас больше 1000 регистров, то кто его знает, как все это будет работать, я не проверял.

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

Далее нужно запустить кнопку Подготовить. Здесь также запускаются отдельные задания для каждого регистра и это уже достаточно длительная операция. Основная идея в том, чтобы передать всю работу MSSQL серверу, он неплох в параллельных вычислениях, а конкурирующие запросы не конкурируют, т.к. идут к разным таблицам и не блокируют друг друга. Конечно здесь все упирается в скорость дисковой подсистемы. Но если база помещается, или почти помещается в отведенную для MSSQL оперативку, то все работает весьма шустро. На этом этапе подготавливаются инструкции SQL и вычисляются итоги на дату свертки. При этом используется параметр обработки КоличествоЗаписейУдаляемыхОдновременно предназначенный для ограничения роста журнала транзакций в дальнейших операциях. Инструкции DELETE будут формироваться с учетом этого параметра. Результаты работы можно посмотреть, выбрав строку и нажав ПоказатьИнструкцииMSSQL, или ПоказатьИтоги. Также заполняются колонки Свернутые и Остающиеся, соответственно для количества записей итогов на дату свертки и количества записей основной таблицы, которые позже даты свертки и останутся нетронутыми. Их сумма и будет количеством записей после окончания свертки в основной таблице (без учета виртуальных и пр.).

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

Теперь кнопка УдалитьДвижения снова запускает набор фоновых заданий. Тоже длительная операция. Здесь уже нарушается согласованность таблиц базы. С этого момента и до окончания свертки Тестированием и Исправлением с пересчетом итогов, любые прочитанные данные другими клиентами или регламентными заданиями нужно считать неверными. На этом этапе MSSQL может выдавать ошибку для отдельных потоков о взаимной блокировке или прочие ошибки, как вариант, о таймауте соединения. Это не страшно, просто ошибочные потоки надо запустить на выполнение повторно после окончания остальных (сняв с завершившихся успешно пометки). Думаю, это происходит из-за попыток одновременно установить пометки удаления на разные документы в одних и тех же журналах. Как мне кажется проблема кроется в захвате участков индекса по которому отбираются записи для пометки удаления. Может быть можно будет организовать ожидание освобождения ресурса, или, как вариант, объединить обработку разных документов в одном журнале в одну инструкцию SQL. Возможно когда-нибудь я займусь этим, но в целом пока это не мешает. Вообще надо обращать внимание на содержимое поля Состояние, при нормальном завершении там всегда остается цифра, время выполнения потока, даже и ноль.

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

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

Теперь база может быть доступна для работы пользователей. Помеченные на удаление документы могут удаляются одновременно с работающими пользователями. Большое их количество делает нежелательным, из-за продолжительности, использование стандартной процедуры удаления помеченных объектов. Но это уже на ваше усмотрение. Процедура удаления в обработке также проводит контроль наличия ссылок на документ перед его удалением. Это может занимать очень много времени, но так как не особо мешает работе пользователей, то вроде и нет проблемы. Функция НайтиПоСсылкам не зря в качестве аргумента принимает массив, т.к. процедура очень долгая и ее эффективность тем больше, чем больше объектов одновременно обрабатываются. Например, база на которой я готовил эту публикацию тратила 10 мин на поиск ссылок на 100 документов, 20 мин на поиск для 1000 документов, 40 мин на 10000, и 5 часов на все 350000 документов. Поэтому при удалении документов предпринимается попытка найти ссылки сразу на все помеченные. И хотя процедура выполняется в фоновом задании, отображение хоть какого-то прогресса во время поиска ссылок не имеет смысла. Клиент всего лишь контролирует активность задания. По окончании, результаты поиска сохраняются (имя файла должно быть задано в параметрах обработки) и начинается удаление. Периодически выводится информация об обработанных ссылках и сохраняется состояние. Таким образом можно прервать выполнение задания, если необходимо, а потом продолжить, не вызываю долгую процедуру поиска ссылок.

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

Если обработка окажется востребованной, то в планах будет добавить многопоточный пересчет итогов, возможно проверку целостности без монопольной блокировки базы, а также мелочи типа: размеры справочников, поиск ошибочных записей в итогах, выполнение последовательно без фона, проверка правильной конфигурации корректировки регистров, рекурсивная проверка циклических ссылок помеченных на удаление документов, [добавьте свое].

Свертка баз, быстро, дешево :-).

 

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

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

Наименование Файл Версия Размер
Быстрая свертка базы 1С через MSSQL:

.epf 18,30Kb
142
.epf 18,30Kb 142 Скачать бесплатно

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

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Yashazz 3878 05.03.21 18:36 Сейчас в теме
Ойё. Зачем же обработку, да ещё общий модуль... Можно же расширение сделать. Ну и по тексту замечаний много - например, внешняя обработка с управляемой формой в сеансе обычного приложения просто не откроется. И ODBC - можно ж внешние источники прикрутить, это удобнее. Подробнее буду читать позже.
user966690; logarifm; +2 Ответить
2. PROGRAM1S 05.03.21 20:48 Сейчас в теме
3. gzharkoj 402 05.03.21 21:17 Сейчас в теме
Задачка уже заезженная,хоть и в другом виде у вас, но суть та же, оставлю ссылку на объяснение https://its.1c.ru/db/metod8dev/content/2606/hdoc

По свертке, все-таки у вас по сути свертка "слепым" методом, им можно свернуть только относительно простые регистры, которые самодостаточны, например, накопления, да и то не все. В современных конфигурация, например, УТ 11, если посворачиваете так регистры сведения и регистры накопления, думаю, там потом столько фантомов можно будет поймать, что проще будет завести новую базу. Поэтому вашим способом можно сворачивать точечно данные, например, регистры, которые слабо связаны с остальными метаданными и, что более важней, с логикой всей системы, например, можно свернуть цены.
triviumfan; +1 Ответить
10. user1466751 09.03.21 13:23 Сейчас в теме
(3)
А какая разница, какая сложность остаточного регистра? Составные типы - скулу по барабану, для него это лишь две дополнительных колонки, хранящих тип значения и индекс таблицы значения, если тип ссылочный. Приведите пример возможного фантома, пожалуйста.
11. gzharkoj 402 09.03.21 21:01 Сейчас в теме
(10) В название статьи у вас фигурирует слово "База", а по факту у вас речь идет только о конкретных регистрах накопления. К вопросу о логике, самый простой пример для УТ 11, скажем сворачиваем Товары на складах, документы реализации как я понимаю у вас удалятся. Зайдете в список продажи и везде, где были реализации будет объект не найден, потому что там отдельный регистр сведений не подчиненный регистратору, но со всеми цифрами. Я уже не говорю, о расчете себестоимости, что там будет.
Вот и получает, что это не свертка базы, а конкретных регистров-таблиц, за которые разработчик уверен, что ничего нигде не выстрелит.
13. user1466751 10.03.21 09:33 Сейчас в теме
(11)
Это не моя статья. Ваше позиция понятна - неудачное название статьи для вашего восприятия.
15. webester 35 13.03.21 14:48 Сейчас в теме
(11)
>>скажем сворачиваем Товары на складах
>>потому что там отдельный регистр сведений не подчиненный регистратору
Ничего не понятно но очень интересно, про какой регистр речь, если начинали про товары на складах причем здесь другие регистры? Вы рассказываете про удаление без контроля ссылочной целостности или о чем? Просто там процедура удаления отделена от свертки и можно с этим что-то делать. Ну там, что-то удалять, что-то нет.
4. TMV 14 06.03.21 18:59 Сейчас в теме
тз = новый структура()
за такое нужно палкой по рукам бить
Азверин; Филин; YanTsys; pavlov_dv; oleganatolievich; cleaner_it; +6 Ответить
5. KroVladS 33 07.03.21 13:32 Сейчас в теме
При создании обработки с похожим функционалом тоже начинал с удаления записей из таблицы по условию
DELETE Таблица.

Потом перешёл на копирование нужных записей в новую таблицу и удаление оригинальной
SEL ECT * INTO Temp_Таблица  From Таблица; 
DR OP   TABLE Таблица; 
exec sp_rename Temp_Таблица, Таблица;
работало быстрее.
В конце упёрся в то что после всех этих очисток ШРИНК БД один фиг может работать 2 суток.
И пришёл к созданию новой БД и копированию нужных данных в неё
SELECT * INTO НоваяБД.Таблица Fr om ИсходнаяБД.Таблица
Sergey.Noskov; +1 Ответить
14. Sergey.Noskov 1194 11.03.21 12:00 Сейчас в теме
19. buganov 175 27.04.21 11:59 Сейчас в теме
(14) Сергей, очень бы хотелось узнать подробнее, как Деловые линии переходили на 8.3 и сворачивали базу. Тоже предстоит переход и хотелось бы 10Тб порезать без боли
20. Sergey.Noskov 1194 04.05.21 12:34 Сейчас в теме
(19) Добрый день.
Переход на 8.3 кратно описывал тут Взгляд на ошибки и платформу через призму HI-Load
Без боли и непрерывно для бизнеса - только через две базы с обменом.
Срезку так же можно делать в две базы - в первой продолжаем работать и фиксируем изменения в планах обмена. А над второй базой "издеваемся как надо". Потом ждем синхронизации и "переключаем ярлыки" у пользователей (или базы на сервере 1с местами меняем).
6. goleaff2006 61 09.03.21 07:32 Сейчас в теме
интересно если базы терабайтные сколько времени ваши механизмы будут надрываться?!
7. YanTsys 12 09.03.21 10:13 Сейчас в теме
(0) напомнило сериал Биохакеры, где один персонаж вживлял себе имплант под ребро по роликам из ютуба...
8. hiduk 106 09.03.21 12:43 Сейчас в теме
Скачал на всякий случай.
Мой ответ на интересный вопрос: вывод значения переменной икс не состоится.
9. user1466751 09.03.21 13:20 Сейчас в теме
С измененными таблицами в расширении справится? Я про таблицы с суффиксами "X..."
12. XAKEP 09.03.21 22:26 Сейчас в теме
очередная сказка про волшебную таблетку.

выражение :
использующая прямое редактирование таблиц в MSSQL
и позволяющая в разы сократить время сей операции

означает : да не бойся, что это БД а не табличный документ
сначала страшно, а потом - через некоторое время будет и больно.
16. sergey_kuzmin_ 19 14.03.21 17:21 Сейчас в теме
Спасибо всем за комментарии. Обработка была собрана не теоретически, прошла несколько стадий эволюции :). Баз различных свернуто было более десятка. Самая большая была около 500Г, заняла порядка 4 часов, но надо понимать, что в наибольшей степени скорость зависит от дисковой подсистемы. Ну и как вы понимаете, более крупные базы крутятся на более сильном железе, поэтому зависимость не прямая. На крайних свертках логическая целостность уже не нарушалась и достаточно было только пересчета итогов. Специфика баз позволила отладить обработку только для ограниченного количества случаев, поэтому на суперуниверсальность не претендует. Код открыт и бесплатный, меняйте под себя. Что касается некоторых подходов и решений, то внимание уделялось тем из них, которые заметно влияли на производительность, прочие какие получились, такие и получились. Ну и это таки был эксперимент с фоновыми заданиями :) С (5) не согласен, но здесь углубляться не буду. Просто выложите рабочую обработку, которая делает тоже самое быстрее. Тогда можно сравнивать. А так можно сравнивать только скорость отдельных операций. И кстати, если у вас шринк работает 2 суток, то не ясно как сама база-то ворочается вообще на таком железе. Любые попытки внести сколь-нибудь серьезные изменения в такую базу, будут занимать столько же.
Что касается задачки, то, когда-то давно, когда я участвовал в школьных олимпиадах, то тоже удивлялся происходящему в задачах :) это для (4). Ну а ответ становится очевидным, как только принимаешь, что имя "тз" всего лишь ссылка на объект, а конструкция цикла "для каждого" получает итератор и увеличивает счетчик ссылок на тот же объект, таким образом после потери ссылки от "тз" (после любого переприсвоения), объект исходной таблицы будет уничтожен только после освобождения итератора, т.е. выхода из цикла.
17. Lapitskiy 990 30.03.21 15:52 Сейчас в теме
при открытии: ошибка "Переменная не определена (СверткаБазы)
<<?>>СверткаБазы.СоздатьДокументКорректировки(тз, Новый Структура("ДатаСвертки", Объект.ДатаСвертки));"
18. sergey_kuzmin_ 19 04.04.21 10:46 Сейчас в теме
Для (17). Надо действовать по инструкции в статье. Скорее всего вы не создали общий модуль. Использование обработки требует некоторых простых действий от программиста как программиста.
Оставьте свое сообщение

См. также

Свертка документа при переносе остатков (1с7 -> 1С8): Ввод начальных остатков по взаиморасчетам

Свертка базы Обработка документов v8 УТ10 Бесплатно (free)

Сворачивает индивидуально и глобально таблицы документа "Ввод начальных остатков по взаиморасчетам", пересоздаются документы "Документ расчетов с контрагентом (ручной учет)" к вводу остатков (делал для УТ)

23.04.2009    16510    176    izidakg    4    

Заполнение документа корректировка регистров

Обработка документов Свертка базы v8 УПП1 Бесплатно (free)

Обработка выгружает движения по регистрам за выбранный период в документ "Корректировка регистров". Делалось для УПП 1.2.3.1. С нового года контора решила начать все с нуля, но хочется обороты по продажам оставить, для статистики и отчетности. Но чтобы на себестоимость влияло, не хочется. Выбираем в списке регистры, движения по которым надо оставить(Продажи, например), создается документ со всеми движениями этих регистров. При необходимости все документы, совершившие движения помечаются на удаление. Халява, плиз.

17.03.2007    10038    587    ta44ik    7