В практике администрирования и разработки на платформе «1С:Предприятие» иногда возникает крайне коварная проблема: один и тот же документ (например, Больничный лист, делающий перерасчет прошлых периодов) в файловом варианте базы данных рассчитывает суммы абсолютно корректно, а в клиент-серверном варианте под управлением MS SQL Server выдает ошибочные результаты. Особенно часто с этим сталкиваются при расчете сложных начислений, таких как сдельный заработок, зависящий от накапливаемых показателей и вытеснения периодов.
В рамках данной статьи мы подробно разберем, почему возникает такое расхождение, проанализируем внутренние механизмы работы платформы 1С с различными СУБД и определим пошаговый план устранения этой проблемы.
Рассмотрим ситуацию: в информационной базе «1С:Зарплата и управление персоналом» выполняется расчет документа Больничный лист, который затрагивает прошлый период. На файловой копии базы документ успешно сторнирует старые начисления и создает новые корректные записи. На сервере MS SQL Server тот же самый документ в той же самой точке времени рассчитывает суммы неверно (например, задваивает показатели или, наоборот, полностью обнуляет сдельную часть оплаты за оставшиеся рабочие дни).
Такое поведение обусловлено фундаментальными различиями в архитектуре файлового движка 1С и полноценных реляционных СУБД (в данном случае MS SQL Server). Выделим четыре основные причины.
Разберем подробнее, как файловая база и MS SQL Server возвращают выборку строк при отсутствии в запросе явной инструкции упорядочивания.
УПОРЯДОЧИТЬ ПО (ORDER BY) СУБД возвращает строки в хаотичном порядке, который может меняться даже при повторном выполнении одного и того же запроса. Если промежуточные расчетные таблицы в ЗУП соединяются без жестко детерминированной сортировки, то на сервере SQL порядок обработки записей (например, сначала сторно, потом начисление) нарушается, что приводит к искажению итоговых сумм.Проанализируем работу со сдельным заработком. В отличие от обычного оклада, сдельная оплата опирается на накапливаемые показатели (вводимые документами Сдельные работы). При расчете за месяц программа часто использует формулы сравнения фактической выработки с гарантированным тарифом, например:
Макс(СдельныйЗаработок, ТарифнаяСтавка * Время)
Когда вводится документ Больничный лист «задним числом», система запускает механизм перерасчетов:
В этот момент платформа создает сложные пакетные запросы с использованием временных таблиц для распределения фактической выработки по рабочим дням. Если в СУБД MS SQL Server эти таблицы соединяются некорректно или в запросе из-за специфики оптимизатора SQL теряются связи по ключевым полям со значениями NULL (которые SQL обрабатывает значительно строже файлового движка), то распределение выработки ломается. В результате программа может полностью обнулить сдельную оплату на SQL-базе, тогда как на файловой версии все распределится верно.
Используемая в примере конфигурации версия платформы (например, 8.3.10.2699) имеет критические баги трансляции запросов 1С в инструкции T-SQL:
База (которая используется для расчета вытеснений и среднего заработка) платформа генерирует тяжелые SQL-запросы с множественными вложенными соединениями. На старых версиях платформы оптимизатор MS SQL Server некорректно оценивает количество строк (процесс Cardinality Estimation), из-за чего строится неоптимальный или ошибочный план выполнения.tempdb на сервере SQL для хранения временных таблиц запросов. Ошибки в механизме создания и автоматического индексирования этих таблиц со стороны старых версий платформы приводили к потере строк при операциях левого соединения (LEFT JOIN).Поведение оптимизатора запросов MS SQL Server напрямую зависит от установленных обновлений (Service Pack и Cumulative Update). На неотпатченных версиях СУБД (особенно MS SQL 2012, 2014, 2016) содержатся зарегистрированные ошибки оптимизатора, которые некорректно обрабатывают вложенные запросы с количеством соединений таблиц более четырех. Поскольку ЗУП при расчете начислений строит огромные пакетные запросы, старые релизы MS SQL без установленных обновлений возвращают неверные наборы данных.
Для устранения расхождений между файловой базой и MS SQL необходимо выполнить комплекс технических мероприятий.
Первым делом необходимо обновить платформу до актуального стабильного релиза (как минимум актуальной версии веток 8.3.21, 8.3.22 или выше). Фирма «1С» регулярно выпускает исправления механизмов трансляции запросов для СУБД. В новых версиях платформы оптимизирована работа с временными таблицами в tempdb и исправлены ошибки расчета виртуальных таблиц регистров расчета.
Установите самый свежий релиз конфигурации ЗУП. В коде расчетных модулей разработчики постоянно устраняют ошибки, явно добавляя операторы упорядочивания (УПОРЯДОЧИТЬ ПО) в те запросы, которые ранее возвращали некорректные результаты на SQL-серверах из-за хаотичной сортировки.
Обратитесь к вашему системному администратору или DBA (Database Administrator). Необходимо выполнить следующие действия на стороне СУБД:
Если обновление программного обеспечения не помогло, необходимо локализовать конкретный проблемный запрос. Настройте сбор Технологического журнала на сервере «1С:Предприятие» для фиксации SQL-запросов. Пример файла настройки logcfg.xml:
<config xmlns="http://v8.1c.ru/v8/tech-log">
<log location="C:\v8\logs" keep="24">
<event>
<eq property="Name" value="DBMSSQL"/>
</event>
<property name="all"/>
</log>
</config>
Выполните расчет документа Больничный лист на файловой базе и на SQL-базе. Найдите в Технологическом журнале тяжелый запрос к таблицам регистров расчета (в событии DBMSSQL). Скопируйте текст транслированного T-SQL запроса и запустите его выполнение напрямую в среде MS SQL Server Management Studio (SSMS). Это позволит наглядно увидеть, на каком именно этапе соединения таблиц (или при работе с NULL) происходит искажение данных, и при необходимости скорректировать индексную систему базы данных.