В практике разработки на платформе 1С:Предприятие часто возникает задача сопоставления данных различных документов. Классический пример — сравнение спецификации расходной накладной с ее документом-основанием (приходной накладной), чтобы выявить расхождения в объемах поставленных и списанных товаров. Однако при написании подобных запросов начинающие разработчики часто совершают критические ошибки, которые приводят к колоссальной нагрузке на СУБД и даже к зависанию всей информационной базы.
В рамках этой статьи мы подробно разберем, почему исходный запрос автора темы является неоптимальным, к каким последствиям на реальной базе данных это приводит, и рассмотрим два профессиональных сценария решения этой задачи с детальным объяснением каждого шага.
Для начала проанализируем исходный вариант запроса, предложенный автором, и разберем причины, по которым опытные разработчики называют его «ересью» и предрекают падение производительности СУБД.
Исходный код запроса выглядел следующим образом:
Запрос = Новый Запрос();
Запрос.Текст = "ВЫБРАТЬ
| РасходнаяНакладнаяСостав.Ссылка КАК ДокументРасходная,
| ПриходнаяНакладнаяСостав.Ссылка КАК ДокументПриходная,
| РасходнаяНакладнаяСостав.Количество * РасходнаяНакладнаяСостав.Коэффициент - ПриходнаяНакладнаяСостав.Количество * ПриходнаяНакладнаяСостав.Коэффициент КАК Количество,
| ВЫБОР
| КОГДА РасходнаяНакладнаяСостав.Ссылка.ДокОснование = ПриходнаяНакладнаяСостав.Ссылка
| ТОГДА РасходнаяНакладнаяСостав.Номенклатура = НЕОПРЕДЕЛЕНО
| ИНАЧЕ РасходнаяНакладнаяСостав.Номенклатура
| КОНЕЦ КАК Номенклатура
|ИЗ
| Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав,
| Документ.ПриходнаяНакладная.Состав КАК ПриходнаяНакладнаяСостав
|ГДЕ
| РасходнаяНакладнаяСостав.Номенклатура = ПриходнаяНакладнаяСостав.Номенклатура
| И РасходнаяНакладнаяСостав.Ссылка.Дата МЕЖДУ &ДатаНач И &ДатаКон";
Выделим четыре ключевые проблемы этого кода:
ИЗ Документ.РасходнаяНакладная.Состав, Документ.ПриходнаяНакладная.Состав без явного указания оператора соединения заставляет СУБД перемножить каждую строку первой таблицы на каждую строку второй таблицы. Если в вашей базе содержится 10 000 строк расходов и 10 000 строк приходов, СУБД сначала сформирует гигантскую виртуальную таблицу объемом 100 000 000 строк, и только после этого начнет применять фильтры из секции ГДЕ. Это гарантированный способ заблокировать работу пользователей.РасходнаяНакладнаяСостав.Ссылка.Дата в секции ГДЕ заставляет платформу выполнять неявное левое соединение с таблицей шапки документа. Поскольку СУБД не может эффективно использовать индексы по дате при таком обращении, операция фильтрации по периоду становится крайне медленной и тяжелой. Аналогичная проблема скрывается и в конструкции РасходнаяНакладнаяСостав.Ссылка.ДокОснование в секции ВЫБОР.Номенклатура приведет к эффекту «размножения» строк. Итоговое количество будет рассчитано неверно, так как строки соединятся друг с другом в геометрической прогрессии.РасходнаяНакладнаяСостав.Номенклатура = ПриходнаяНакладнаяСостав.Номенклатура в секции ГДЕ неявно превращает запрос во внутреннее соединение. Если в расходной накладной есть товар, которого вообще не было в приходе, то разница по нему должна быть стопроцентной. Однако из-за жесткого равенства этот товар просто исчезнет из результирующей выборки.Основной вопрос автора заключался в том, как красиво отсечь строки, в которых разница между расходом и приходом равна нулю. В рамках исходного (неоптимального) запроса это сделать невозможно без еще большего ухудшения производительности.
Для решения этой задачи мы должны полностью переписать архитектуру запроса. Ниже мы детально разберем два надежных и производительных сценария решения.
Наиболее надежный и масштабируемый способ решения подобных задач в профессиональной разработке на платформе 1С — это использование пакета запросов с временными таблицами. Этот подход позволяет разбить сложную задачу на простые изолированные этапы, каждый из которых работает максимально быстро и задействует индексы СУБД.
Рассмотрим этот метод по шагам:
Шаг 1. Отбор и подготовка данных Расхода. Сначала мы выберем данные только по тем расходным накладным, которые попадают в указанный период. Чтобы избежать дублирования строк, мы сразу сгруппируем данные по номенклатуре и документам. Обратите внимание: для быстрого отбора по дате мы используем явное внутреннее соединение с таблицей шапки документа Документ.РасходнаяНакладная.
Шаг 2. Отбор данных Прихода. Мы выберем данные только по тем приходным накладным, которые выступают документами-основаниями для отобранных расходных документов. Это позволит СУБД не обрабатывать лишние данные за пределами анализируемой выборки.
Шаг 3. Соединение данных и фильтрация. Мы выполним левое соединение подготовленной таблицы расхода с таблицей прихода. С помощью функции ЕСТЬNULL мы корректно обработаем ситуации, когда прихода по данной номенклатуре не было, а условие в секции ГДЕ позволит моментально отсечь строки с нулевой разницей.
Проанализируем готовый текст оптимизированного запроса:
Запрос = Новый Запрос();
Запрос.Текст =
"ВЫБРАТЬ
| РасходСостав.Ссылка КАК ДокументРасходная,
| РасходСостав.Ссылка.ДокОснование КАК ДокументПриходная,
| РасходСостав.Номенклатура КАК Номенклатура,
| СУММА(РасходСостав.Количество * РасходСостав.Коэффициент) КАК КолРасход
|ПОМЕСТИТЬ ВТ_Расход
|ИЗ
| Документ.РасходнаяНакладная.Состав КАК РасходСостав
| // Явное соединение для быстрого отбора по индексу даты шапки
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РасходнаяНакладная КАК РасходШапка
| ПО РасходСостав.Ссылка = РасходШапка.Ссылка
|ГДЕ
| РасходШапка.Дата МЕЖДУ &ДатаНач И &ДатаКон
|
|СГРУППИРОВАТЬ ПО
| РасходСостав.Ссылка,
| РасходСостав.Ссылка.ДокОснование,
| РасходСостав.Номенклатура
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ПриходСостав.Ссылка КАК ДокументПриходная,
| ПриходСостав.Номенклатура КАК Номенклатура,
| СУММА(ПриходСостав.Количество * ПриходСостав.Коэффициент) КАК КолПриход
|ПОМЕСТИТЬ ВТ_Приход
|ИЗ
| Документ.ПриходнаяНакладная.Состав КАК ПриходСостав
|ГДЕ
| // Ограничиваем выборку прихода только документами-основаниями из ВТ_Расход
| ПриходСостав.Ссылка В (ВЫБРАТЬ РАЗЛИЧНЫЕ ДокументПриходная ИЗ ВТ_Расход)
|
|СГРУППИРОВАТЬ ПО
| ПриходСостав.Ссылка,
| ПриходСостав.Номенклатура
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Расход.ДокументРасходная КАК ДокументРасходная,
| Расход.ДокументПриходная КАК ДокументПриходная,
| Расход.Номенклатура КАК Номенклатура,
| // Вычисляем разницу с обработкой возможных значений NULL
| Расход.КолРасход - ЕСТЬNULL(Приход.КолПриход, 0) КАК Количество
|ИЗ
| ВТ_Расход КАК Расход
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Приход КАК Приход
| ПО Расход.ДокументПриходная = Приход.ДокументПриходная
| И Расход.Номенклатура = Приход.Номенклатура
|ГДЕ
| // Исключаем строки, где разница равна 0
| (Расход.КолРасход - ЕСТЬNULL(Приход.КолПриход, 0)) <> 0";
Запрос.УстановитьПараметр("ДатаНач", НачалоДня(ДатаНач));
Запрос.УстановитьПараметр("ДатаКон", КонецДня(ДатаКон));
Документы = Запрос.Выполнить().Выгрузить();
ЭлементыФормы.Документы.СоздатьКолонки();
Преимущества данного решения:
ЛЕВОМУ СОЕДИНЕНИЮ и функции ЕСТЬNULL мы не теряем товары, отсутствующие в приходных документах.Если перед вами стоит задача сопоставить общие объемы поступления и списания товаров за период без жесткой привязки к конкретному документу-основанию на уровне строк, идеальным архитектурным решением станет использование конструкции ОБЪЕДИНИТЬ ВСЕ с последующей группировкой.
Этот подход примечателен тем, что он вообще полностью исключает операцию соединения таблиц (JOIN) на уровне СУБД, которая является самой ресурсоемкой. Вместо этого СУБД выполняет последовательное чтение таблиц, что происходит практически мгновенно.
Разберем логику работы этого метода:
ИМЕЮЩИЕ, которая накладывает условие фильтрации непосредственно на результат группировки.Посмотрим на пример реализации такого запроса:
Запрос = Новый Запрос();
Запрос.Текст =
"ВЫБРАТЬ
| ОбъединенныеДанные.Номенклатура КАК Номенклатура,
| СУММА(ОбъединенныеДанные.Количество) КАК КоличествоРазница
|ИЗ
| (ВЫБРАТЬ
| РасходСостав.Номенклатура КАК Номенклатура,
| РасходСостав.Количество * РасходСостав.Коэффициент КАК Количество
| ИЗ
| Документ.РасходнаяНакладная.Состав КАК РасходСостав
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РасходнаяНакладная КАК РасходШапка
| ПО РасходСостав.Ссылка = РасходШапка.Ссылка
| ГДЕ
| РасходШапка.Дата МЕЖДУ &ДатаНач И &ДатаКон
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| ПриходСостав.Номенклатура КАК Номенклатура,
| - (ПриходСостав.Количество * ПриходСостав.Коэффициент) КАК Количество
| ИЗ
| Документ.ПриходнаяНакладная.Состав КАК ПриходСостав
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная КАК ПриходШапка
| ПО ПриходСостав.Ссылка = ПриходШапка.Ссылка
| ГДЕ
| ПриходШапка.Дата МЕЖДУ &ДатаНач И &ДатаКон) КАК ОбъединенныеДанные
|
|СГРУППИРОВАТЬ ПО
| ОбъединенныеДанные.Номенклатура
|
|ИМЕЮЩИЕ
| СУММА(ОбъединенныеДанные.Количество) <> 0";
Запрос.УстановитьПараметр("ДатаНач", НачалоДня(ДатаНач));
Запрос.УстановитьПараметр("ДатаКон", КонецДня(ДатаКон));
Документы = Запрос.Выполнить().Выгрузить();
Преимущества метода объединения:
ИМЕЮЩИЕ.Мы проанализировали исходную проблему и выяснили, что простое добавление фильтра Количество = 0 в неоптимальный запрос не решило бы проблему быстродействия, а лишь усугубило бы её. Профессиональная оптимизация запросов 1С всегда требует пересмотра архитектуры связей.
Для надежного сопоставления связанных документов (например, по документу-основанию) всегда используйте Временные Таблицы (Вариант 1). Если же вам необходимо быстро оценить расхождения по номенклатуре за период в целом, лучшим выбором станет метод Объединения и Группировки (Вариант 2).