Свертка старых периодов в крупных базах данных 1С 7.7 — задача ресурсоемкая. При использовании штатных средств платформы (таких как программное распроведение документов) процесс может растянуться на многие часы или даже дни. Логичным решением в этой ситуации является использование механизма прямых запросов с помощью внешней компоненты 1С++ (1cpp). Прямая очистка таблиц движений регистров позволяет выполнить операцию за считанные минуты.
Однако при работе с файловыми базами данных (формат DBF) разработчики часто сталкиваются со специфическими ошибками синтаксиса при попытке выполнить операцию DELETE. В этой статье мы подробно разберем, почему стандартные SQL-конструкции вызывают сбои в файловом режиме, как написать правильный и быстрый запрос на удаление движений регистров и в чем заключается секрет оптимизации с использованием символов "ZZZZZZZZZ".
В файловой версии 1С 7.7 выполнение прямых запросов происходит через драйвер VFPOLEDB (Visual FoxPro). Язык запросов FoxPro SQL имеет ряд существенных отличий от диалекта T-SQL, используемого в Microsoft SQL Server.
Проанализируем типичную ошибку, которую совершают разработчики, привыкшие к MS SQL. Попытка выполнить запрос с соединением таблиц напрямую через INNER JOIN приводит к ошибке:
-- Данный запрос вызовет ошибку "Command contains unrecognized phrase/keyword" в DBF
DELETE r
FROM $Регистр.ИмяРегистра as r
INNER JOIN 1Sjourn as j ON j.IDDoc = r.IDDoc
WHERE j.Date BETWEEN :НачДата~~ AND :КонДата~~
Конструкция DELETE ... FROM ... JOIN является расширением Microsoft и не поддерживается стандартным SQL-синтаксисом драйвера FoxPro. При попытке выполнить такой запрос СУБД выдаст ошибку "Command contains unrecognized phrase/keyword" или "Syntax error".
Для решения этой проблемы мы должны использовать стандартный синтаксис SQL-92 с использованием подзапроса в секции WHERE и оператора IN. Разберем по шагам правильное построение такого запроса.
Чтобы успешно удалить движения регистра за определенный период из таблицы RAxxxx (где xxxx — идентификатор таблицы регистра), нам необходимо связать ее с общим журналом документов 1Sjourn, так как именно в журнале хранится дата документа. Мы выберем идентификаторы документов IDDoc за нужный период во вложенном запросе, а во внешнем запросе выполним удаление движений.
Рассмотрим рабочий пример кода на языке 1С 7.7 с использованием объекта ODBCRecordset из библиотеки 1С++:
// Создаем объект для выполнения прямого запроса
Запрос = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = "
|DELETE FROM $Регистр.ОстаткиТоваров
|WHERE IDDoc IN (
| SELECT j.IDDoc
| FROM 1Sjourn as j
| WHERE j.Date BETWEEN :НачДата~~ AND :КонДата~~
|)";
Запрос.УстановитьТекстовыйПараметр("НачДата", НачДата);
Запрос.УстановитьТекстовыйПараметр("КонДата", КонДата);
// Выполняем удаление данных
Запрос.ВыполнитьИнструкцию(ТекстЗапроса);
Этот синтаксис поддерживается драйвером FoxPro и отработает корректно. Однако при обработке больших объемов данных (миллионы записей) такой запрос может выполняться очень медленно. Выясним причину этой проблемы и способы ее решения.
В СУБД Visual FoxPro существует мощная технология оптимизации запросов, которая называется Rushmore. Она позволяет выполнять выборку и удаление записей мгновенно, но только при одном важнейшем условии: выражение в секции WHERE должно точно соответствовать выражению существующего индекса в файле индекса (.CDX).
Если условие в запросе не совпадает с индексным выражением, драйвер FoxPro не сможет применить индекс и перейдет в режим последовательного сканирования всей таблицы (Full Table Scan), что приведет к катастрофическому падению производительности.
Проанализируем структуру индекса по дате в таблице журнала документов 1Sjourn. В системе 1С 7.7 основной индекс по дате и времени документа называется ACDATETIM и строится по следующему составному выражению:
DTOS(date) + time + iddoc
Где:
DTOS(date) — дата, преобразованная в строку формата "YYYYMMDD" (8 символов).time — время документа в 36-ричном представлении (6 символов).iddoc — уникальный идентификатор документа (9 символов).Чтобы задействовать оптимизацию Rushmore, наше условие в подзапросе должно сравнивать именно это строковое выражение, а не просто поле Date. Поскольку нам нужно отобрать документы за период, мы должны сравнивать составную строку с начальной и конечной границами диапазона.
При этом важно правильно заполнить значения времени и идентификаторов для границ диапазона:
DTOS(НачДата) + ' ' + ' '.'ZZZZZZ', а максимальный ID документа — как 'ZZZZZZZZZ'.Если не указать суффикс из символов "Z" для конечной даты, то документы, созданные внутри последнего дня периода (например, в течение дня 31.12.2018), не попадут в выборку, так как их время и ID будут лексикографически "больше" пустой строки. Использование суффикса +'ZZZZZZZZZZZZZZZ' гарантирует 100% точность выборки и включает оптимизацию Rushmore на полную мощность.
Посмотрим на пример оптимизированного запроса с учетом этих правил:
ТекстЗапроса = "
|DELETE FROM $Регистр.ОстаткиТоваров
|WHERE IDDoc IN (
| SELECT j.IDDoc
| FROM 1Sjourn as j
| WHERE DTOS(j.Date) + j.Time + j.IDDoc BETWEEN
| DTOS(:НачДата~~) + ' ' AND
| DTOS(:КонДата~~) + 'ZZZZZZZZZZZZZZZ'
|)";
Этот запрос отработает в сотни раз быстрее обычного, так как FoxPro мгновенно найдет нужные записи в таблице 1Sjourn по индексу ACDATETIM без полного сканирования таблицы.
При работе с регистрами в 1С 7.7 важно учитывать состояние флага "Быстрая обработка движений" в свойствах регистра в конфигураторе. Наличие или отсутствие этого свойства кардинально меняет структуру индексов самой таблицы движений RAxxxx.
Вариант 1. Быстрая обработка движений ВЫКЛЮЧЕНА.
В этом случае в таблице движений регистра нет собственного индекса по дате. Чтобы очистить движения, нам обязательно нужно связываться с таблицей журнала документов 1Sjourn по полю IDDoc, как показано в предыдущем примере кода.
Вариант 2. Быстрая обработка движений ВКЛЮЧЕНА.
В этом случае таблица движений регистра RAxxxx получает собственный сложный индекс. Нам больше нет необходимости обращаться к таблице журнала документов! Мы можем удалять данные напрямую из таблицы регистра, обращаясь к ее собственному индексу. Это самый быстрый способ удаления из возможных.
Индексное выражение для таблицы регистра при включенной быстрой обработке выглядит так:
DTOS(date) + time + iddoc + STR(lineno,4) + STR(actno,6)
Разберем пример запроса для удаления напрямую из регистра (быстрая обработка включена):
// Удаление движений напрямую по индексу регистра без обращения к 1Sjourn
ТекстЗапроса = "
|DELETE FROM $Регистр.ОстаткиТоваров
|WHERE DTOS(Date) + Time + IDDoc + STR(LineNo,4) + STR(ActNo,6) BETWEEN
| DTOS(:НачДата~~) + ' ' AND
| DTOS(:КонДата~~) + 'ZZZZZZZZZZZZZZZZZZZZZZZZZ'
|";
Обратите внимание на количество символов пробела и символов "Z" во второй части выражения — их общая длина должна в точности соответствовать суммарной длине полей Time (6), IDDoc (9), LineNo (4) и ActNo (6) для обеспечения точного совпадения с индексом.
Для того чтобы ваши операции по удалению и сворачиванию базы данных проходили без сбоев и с максимальной скоростью, рекомендуем придерживаться следующих правил:
Collating Sequence=MACHINE. Это ускоряет работу с индексами в разы, так как индексы файлов DBF в 1С построены именно в кодировке Machine (строгое побайтовое сравнение символов).DELETE в СУБД FoxPro физически не удаляет строки из файлов .DBF, а лишь помечает их как "удаленные" (выставляет специальный байт-маркер в начале строки). Файлы базы данных при этом не уменьшаются в размере. Чтобы физически освободить место на диске и перестроить индексы, после выполнения всех удалений необходимо выполнить монопольную индексацию базы в Конфигураторе 1С (это выполнит команду PACK для всех таблиц).Применяя данные подходы, мы можем эффективно оптимизировать процесс свертки устаревших данных, превращая многочасовое ожидание в секундную операцию.