В процессе разработки кадровых отчетов и аналитических панелей в системе «1С:Предприятие 8» программисты часто сталкиваются с необходимостью сопоставить состояние данных на разные моменты времени. Типичный пример такой задачи — найти всех сотрудников организации, которые как на дату «Д1», так и на дату «Д2» занимали одну и ту же конкретную должность. Решение этой задачи кроется в правильной работе с периодическими регистрами сведений (в нашем случае — РаботникиОрганизаций) и механизмами среза последних.
В данной статье мы подробно разберем, как спроектировать такой запрос, почему стандартные подходы новичков (такие как объединение таблиц) здесь не работают, и как написать максимально быстродействующий и оптимизированный код, соответствующий всем стандартам разработки фирмы «1С».
Представим сценарий: у нас есть периодический регистр сведений РаботникиОрганизаций, в котором хранится история кадровых перемещений сотрудников (их должности, подразделения, ставки и т.д.). Нам необходимо получить список физических лиц (сотрудников), у которых в кадровой истории на дату &д1 установлена должность &Должн, и на более позднюю (или раннюю) дату &д2 установлена абсолютно та же должность &Должн.
Важно понимать логическую суть условия «И». Нам нужны только те люди, которые удовлетворяют обоим условиям одновременно. Если сотрудник работал на требуемой должности на дату &д1, но на дату &д2 был переведен на другую позицию (или уволен), он не должен попасть в финальную выборку. И наоборот.
Часто начинающие разработчики пытаются решить подобные задачи через оператор ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ ВСЕ, как это показано в одном из сообщений форума:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РаботникиОрганизацийСрезПоследних.Сотрудник КАК Сотрудник
ИЗ
РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д2) КАК РаботникиОрганизацийСрезПоследних
ГДЕ
РаботникиОрганизацийСрезПоследних.Должность = &Должн
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ
РаботникиОрганизацийСрезПоследних.Сотрудник КАК Сотрудник
ИЗ
РегистрСведений.РаботникиОрганизаций.SрезПоследних(&д1) КАК РаботникиОрганизацийСрезПоследних
ГДЕ
РаботникиОрганизацийСрезПоследних.Должность = &Должн
Давайте проанализируем, к какому результату приведет этот запрос. Оператор ОБЪЕДИНИТЬ выполняет сложение двух результирующих выборок и удаляет дубликаты. В итоге мы получим сотрудников, которые занимали целевую должность хотя бы на одну из указанных дат. То есть мы реализуем логическое условие «ИЛИ». Если сотрудник работал на нужной должности на дату &д1, но на &д2 уже сменил работу, он все равно отобразится в результате. Это в корне противоречит исходной бизнес-логике.
Чтобы реализовать строгое логическое условие «И» (сотрудник должен соответствовать критериям на обе даты одновременно), нам необходимо использовать механизм внутреннего соединения (ВНУТРЕННЕЕ СОЕДИНЕНИЕ) двух срезов последних. Внутреннее соединение оставляет в результирующем наборе данных только те строки, для которых сопоставляемые ключи присутствуют в обеих таблицах.
Рассмотрим структуру базового запроса, который решает эту задачу:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ТабД1.Сотрудник КАК Сотрудник,
ТабД1.Организация КАК Организация,
ТабД1.Должность КАК Должность,
ТабД1.ПодразделениеОрганизации КАК ПодразделениеД1,
ТабД2.ПодразделениеОрганизации КАК ПодразделениеД2
ИЗ
РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д1) КАК ТабД1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д2) КАК ТабД2
ПО ТабД1.Сотрудник = ТабД2.Сотрудник
ГДЕ
ТабД1.Должность = &Должн
И ТабД2.Должность = &Должн
В этом запросе мы берем срез данных на первую дату (как таблицу ТабД1) и соединяем его со срезом на вторую дату (ТабД2) по ключевому полю Сотрудник. Условия в секции ГДЕ гарантируют, что в выборку попадут только сотрудники с целевой должностью на оба момента времени.
Хотя вышеописанный запрос выдает логически верный результат, с точки зрения производительности на реальных базах данных он является абсолютно неприемлемым. Разберем детально, какие критические ошибки здесь допущены, и почему данный запрос вызовет серьезные зависания системы при росте количества сотрудников.
ГДЕ ТабД1.Должность = &Должн, платформа выполняет следующее действие на уровне СУБД: сначала извлекается срез последних по абсолютно всем сотрудникам из базы данных на даты &д1 и &д2, затем происходит их полное соединение в памяти сервера, и только после этого СУБД отсекает лишние строки, не соответствующие должности. Если в компании работают десятки тысяч сотрудников, этот запрос будет выполняться крайне медленно и потреблять колоссальное количество оперативной памяти.СрезПоследних с другим СрезПоследних) является крайне нерекомендуемой операцией. Оптимизаторы современных СУБД (MS SQL Server, PostgreSQL) часто не способны построить адекватный и быстрый план выполнения для подобных конструкций. В результате СУБД может выбрать неэффективное сканирование индексов, что приведет к блокировкам таблиц и снижению параллельной работы пользователей.Сотрудник.Статус <> &ВыбСтатус в секции ГДЕ, платформа 1С осуществляет неявное левое соединение с основной таблицей справочника Сотрудники. Наличие таких неявных соединений поверх уже соединенных виртуальных таблиц увеличивает в геометрической прогрессии время обработки данных SQL-сервером.Для того чтобы исправить все вышеперечисленные проблемы и написать отказоустойчивый, быстродействующий код, мы применим две важнейшие методики оптимизации 1С: параметризацию виртуальных таблиц и использование временных таблиц с индексацией.
Разберем процесс оптимизации по шагам.
Конструкция виртуальной таблицы СрезПоследних имеет два параметра: СрезПоследних(Период, Условие). Второй параметр предназначен именно для предварительной фильтрации данных на уровне базы данных перед расчетом самого среза последних. Мы должны передать условие отбора по должности прямо туда. Таким образом, СУБД вернет нам срез последних только по тем сотрудникам, у которых должность на нужную дату уже равна &Должн. Объем обрабатываемых данных сократится в сотни раз!
// Правильная фильтрация данных:
РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д1, Должность = &Должн) КАК ТабД1
Чтобы облегчить работу оптимизатора СУБД, мы разобьем наш сложный запрос на несколько простых последовательных шагов. Сначала мы выберем данные на дату &д1 и поместим их в оперативную память сервера в виде временной таблицы. Обязательно выполним индексацию временной таблицы по полю связи Сотрудник. Это позволит СУБД моментально находить строки при последующем соединении.
ВЫБРАТЬ
РаботникиД1.Сотрудник КАК Сотрудник,
РаботникиД1.Организация КАК Организация,
РаботникиД1.ПодразделениеОрганизации КАК ПодразделениеД1,
РаботникиД1.Период КАК ПериодД1
ПОМЕСТИТЬ ВТ_РаботникиД1
ИЗ
РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д1, Должность = &Должн) КАК РаботникиД1
ИНДЕКСИРОВАТЬ ПО
Сотрудник
На финальном этапе мы выберем данные из проиндексированной временной таблицы ВТ_РаботникиД1 и соединим её со вторым срезом последних на дату &д2, в параметрах которого также установлена фильтрация по целевой должности. Благодаря тому, что первая таблица физически подготовлена и проиндексирована, СУБД выполнит операцию соединения (обычно методом Index Seek / Nested Loops) за сотые доли секунды.
Объединим все наши шаги в единый пакетный запрос. Данный вариант полностью соответствует стандартам качественной разработки «1С:Совместимо» и гарантирует высокую скорость работы даже на терабайтных базах данных.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗЛИЧНЫЕ
| РаботникиД1.Сотрудник КАК Сотрудник,
| РаботникиД1.Организация КАК Организация,
| РаботникиД1.ПодразделениеОрганизации КАК ПодразделениеД1,
| РаботникиД1.Период КАК ПериодД1
|ПОМЕСТИТЬ ВТ_РаботникиД1
|ИЗ
| РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д1, Должность = &Должн И Сотрудник.Статус <> &ВыбСтатус) КАК РаботникиД1
|
|ИНДЕКСИРОВАТЬ ПО
| Сотрудник
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_РаботникиД1.Сотрудник КАК Сотрудник,
| ВТ_РаботникиД1.Организация КАК Организация,
| ВТ_РаботникиД1.ПодразделениеД1 КАК ПодразделениеД1,
| РаботникиД2.ПодразделениеОрганизации КАК ПодразделениеД2,
| ВТ_РаботникиД1.ПериодД1 КАК ПериодД1,
| РаботникиД2.Период КАК ПериодД2
|ИЗ
| ВТ_РаботникиД1 КАК ВТ_РаботникиД1
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РаботникиОрганизаций.СрезПоследних(&д2, Должность = &Должн И Сотрудник.Статус <> &ВыбСтатус) КАК РаботникиД2
| ПО ВТ_РаботникиД1.Сотрудник = РаботникиД2.Сотрудник
|
|УПОРЯДОЧИТЬ ПО
| ПодразделениеД1,
| Сотрудник";
Запрос.УстановитьПараметр("д1", Дата1);
Запрос.УстановитьПараметр("д2", Дата2);
Запрос.УстановитьПараметр("Должн", ЦелеваяДолжность);
Запрос.УстановитьПараметр("ВыбСтатус", ИсключаемыйСтатусСотрудника);
РезультатЗапроса = Запрос.Выполнить();
Обратим внимание на выбор полей подразделения и периода в нашем финальном запросе. Мы специально вывели в итоговый результат поля для обеих дат: ПодразделениеД1, ПодразделениеД2, ПериодД1 и ПериодД2.
Зачем это нужно? Дело в том, что за время между датами «Д1» и «Д2» сотрудник мог быть переведен в другое подразделение компании, оставаясь при этом в той же должности. Если бы мы вывели подразделение только из первой таблицы среза последних, мы бы упустили этот важный кадровый факт и получили бы неактуальные аналитические данные на дату «Д2». Предоставление информации по обеим датам делает отчет прозрачным и понятным для конечного пользователя (бухгалтера или кадровика).
Подведем итоги нашего исследования. Чтобы ваши запросы к регистру сведений на несколько дат всегда работали эффективно и без сбоев, придерживайтесь следующих золотых правил разработки в 1С:
СрезПоследних. Никогда не используйте для этих целей секцию ГДЕ, если только это не обусловлено сложной логикой, которую невозможно выразить в параметрах.ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Оператор ОБЪЕДИНИТЬ решает противоположную задачу нахождения элементов по условию «ИЛИ».Сотрудник.Статус). Если есть возможность, передавайте отборы по реквизитам непосредственно в параметры виртуальной таблицы или предварительно подготавливайте списки разрешенных ссылок во временных таблицах.Соблюдение этих простых рекомендаций гарантирует стабильность вашей конфигурации, быструю генерацию отчетов и отсутствие проблем с блокировками данных при коллективной работе пользователей.