1с как очистить базу в sql

Обновлено: 06.01.2025

В данной статье будет рассмотрена методика удаления данных запросом в MSSQL-студии.

Отказ от ответственности

Проверяйте, пожалуйста, текст скрипта перед выполнением. Понимайте, что делаете. Помните, что ответственность лежит целиком на Вас!

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

Основная цель применения

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

Предыстория

Проблема: свертка стандартными средствами происходила неприлично долго. Точней, именно этап удаления старых данных. Остатки вводятся быстро, а вот удаление движений регистров, пометка на удаление документов, само удаление - по нашим оценкам на наших объёмах (500ГБ) заняло бы недели.

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

Препятствия: Данные в SQL хранятся в разных таблицах. Таблиц много, как их связать - не всегда понятно. То есть мало очистить сам документ (шапку), надо очистить также данные табличных частей, и движений документа. Движения по разным регистрам. Для одного документа регистров может быть много. Каждый регистр в свою очередь хранит данные также в нескольких таблицах. Наименования таблиц - неосмысленные.

Теория

Варианты (операторы) удаления в SQL.

DROP - полное удаление таблицы из структуры данных (вместе с данными). То есть очищаются не только данные, но и метаданные. Работает мгновенно.
TRUNCATE - полная очистка таблицы с сохранением структуры таблицы (очищаются только строки таблицы, колонки остаются прежними). Работает мгновенно.
DELETE - удаление записей в таблице по определенному условию. Занимает определенное время.

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

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

В итоге была выбрана и реализована следующая стратегия

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

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

Устаревшая стратегия (альтернатива)

Первое решение, от которого я впоследствии отказался. Хотя вначале оно казалось более удобным и простым. А именно - очистить шапку. А потом все связанные таблицы поочередно, у кого нет "пары" в основной таблице (ссылка/регистратор = "битая" ссылка).

Данный вариант не работает для движений документа. Так как после удаления основной таблицы документа - IS NULL даёт истину после соединения таблицы движений регистра и основной таблицы документа в 2х случаях

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

Вобщем порядок был такой (отличия в пунктах 2 и 3)

Удаляем записи регистров, которые двигает нужный вид документа, по связке с основной таблицей документа
Удаляем основную таблицу документа
Удаляем записи табличных частей документа, у которых Ссылка после соединения = IS NULL
Остальное (как в основном варианте, журналы и регистрация изменений)

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

Особенности

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

Порядок действий

При желании протестировать/посмотреть "что именно будет удаляться" предусмотрена соответствующая опция (галочка справа внизу), в этом случае скрипты будут формироваться с оператором SELECT, а не DELETE. Можно выделить нужный кусок, запустить на исполнение, посмотреть результаты, прежде чем запускать на удаление.

Дисклеймер! Политика 1С не разрешает никакие манипуляции с данными напрямую средствами СУБД, только средствами платформы! Поэтому качать, смотреть, думать, а уж тем более запускать эту обработку категорически нельзя. (или можно, но только ночью, обязательно завесив окна, выключив свет и закрывшись на семь замков). Шутка, конечно же. Просто в случае безвозвратной потери данных виноваты будете Вы и только Вы. Минздрав предупредил.

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

В качестве статистики приведу пример. 1С у меня удаляет данные со скоростью

25 000 объектов/час, Обработка -

2 000 000 объектов за 20 минут. Разница налицо, как говорится.

Как пользоваться обработкой?

Аутенфикация

Некоторые данные для аутенфикации подтягиваются автоматически из строки соединения с ИБ, но они совсем не всегда совпадают. Поэтому необходимо ввести имя именно своего сервера SQL.

Для проверки корректности данных есть кнопочка "Проверить подключение".

Тайм-ауты можно оставить как есть. 30 сек для подключения, 1ч на выполнение запроса в СУБД. Если в процессе удаления появится ошибка "(Microsoft OLE DB Provider for SQL Server): Query timeout expired", значит, необходимо увеличить тайм-аут выполнения запроса, т.к. слишком много данных, и СУБД не успела их почистить.

Удаление

Удаление возможно частичное или полное.

Обработка позволяет очищать следующие объекты:

Документы,
Журналы документов,
Регистры сведений,
Регистры накопления,
Регистры бухгалтерии.

Полное удаление выполняется быстро через команду TRUNCATE TABLE.

Частичное удаление производится путем выбора периода удаляемых данных (отбор по полю _Period в БД).

Для Документов также автоматически очищаются таблицы, содержащие данные табличных частей документов.

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

Для Регистров сведений есть возможность выбора Подчиненных или Независимых. Для Подчиненных возможен отбор по периоду регистратора.

Команда "Очистить всё" последовательно проходит по всем страницам обработки и выполняет действия в зависимости от выбранных параметров.

Эта статья для тех, кто хоть раз сталкивался с необходимостью очистить базу 1С , за период с устаревшими данными. Речь пойдет об удалении данных в ненужном периоде напрямую, средствами SQL -сервера. Также следует уточнить, что речь идет о конфигурациях, использующих компоненту «Оперативный учет». На бухгалтерских и расчетных базах данный метод не тестировался.

Для начала рассмотрим, как происходит резка периода. На определенную дату, чаще всего не конец года, скажем « 31.12.2003 », вычисляются конечные остатки по всем регистрам. Далее, на начало года, на « 01.01.2004 », вводятся эти остатки документами ввода начальных остатков. При этом в новом году в документах не должно быть ссылок на документы прошлого года – так называемых документов, введенных на основании, иначе удаление средствами 1С не сможет удалить документы, на которые есть ссылки. Также следует уделить внимания периодическим реквизитам, введенных документами, так как в момент отмены проведения документа эти периодические реквизиты перестанут существовать. Чтобы не было казусов, все периодические реквизиты, устанавливаемые документами нужно перед удалением выставить заново с начала года.

Теперь рассмотрим, как обычно происходит очистка базы средствами 1С . Запускается какая-то обработка проведения, которая помечает на удаление все документы. Это достаточно длительный процесс, так как при проведении каждого из документов 1С :Предприятие очищает все движения документов, в том числе периодические реквизиты, введенные документом. Это достаточно длительный процесс с учетом большого количества документов. Далее через пункт меню «Операции -> Удаление помеченных объектов» выполняется окончательное удаление.

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

Альтернативный вариант очистки базы 1С – воспользоваться имеющимися в нашем распоряжении средствами MS-SQL . Из вышеуказанной последовательности мы можем ускорить последние два пункта, а именно пометку на удаление и окончательное удаление документов.

Что требуется сделать? Просто запустить скрипт, например из SQL Query Analizer ’а. Текст скрипта представлен ниже:

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

После выполнения данного скрипта остается только войти в режим конфигуратора и сделать пересчет служебных данных и пересчет итогов. На случай, если вы не избавились от ссылок на старые документы, то необходимо сделать еще и проверку логической целостности. В настройках поставить « Очищать ссылки ».

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

Теперь о производительности. Я проверил оба варианта очистки базы 1С - удаление в 1С методом выбора всех документов и удаления их без контроля ссылочной целостности, и удаление путем выполнения данного скрипта. В базе данных содержалась информация за период с « 01.09.2001 » по « 15.06.2004 ». Удалялся период по 2003 год включительно. Конфигурация самописная, но в данном случае, думаю, это никакого значения не имеет. Данные сравнения указаны в таблице:

Как видно по результатам сравнения, удаление средствами MS-SQL фактически ровно в 10 раз быстрее. Это соотношение действительно с учетом того, что для обоих вариантов будет запущен полный пересчет итогов из конфигуратора. Хотя в случае с 1C его выполнять желательно, но не обязательно (почему - читайте здесь и здесь), а в случае с MS-SQL - обязательно. Так что, если вы не будете выполнять пересчет итогов в случае удаления в 1С , то для полноценного сравнения времени работы нужно к времени работы скрипта ( 5:39 ), добавить время пересчета итогов в конфигураторе ( 2:00 ).