При написании сложных запросов в системе «1С:Предприятие 8» разработчики регулярно сталкиваются с необходимостью отфильтровать данные по списку значений, который уже находится в созданной ранее временной таблице (ВТ). Попытка сделать это простым и интуитивным способом, например, написав ГДЕ Номенклатура.Ссылка В (ВтНом), приводит к синтаксической ошибке платформы. Платформа ожидает увидеть в скобках либо перечисление параметров, либо полноценный подзапрос.
В рамках этой статьи мы подробно разберем, как правильно накладывать условия с использованием временных таблиц, сравним два популярных подхода (подзапросы и соединения) с точки зрения производительности СУБД, а также детально изучим особенности оптимизации запросов к виртуальным таблицам регистров.
Самый простой и синтаксически корректный способ ограничить выборку по данным из временной таблицы — это использование конструкции ГДЕ ... В (ВЫБРАТЬ ... ИЗ ВТ). Давайте разберем этот подход на базовом примере.
Представим, что у нас уже есть сформированная временная таблица ВтНоменклатура, содержащая список ссылок, по которым нужно отфильтровать итоговый запрос. Правильный синтаксис будет выглядеть следующим образом:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Код КАК Код,
Номенклатура.Наименование КАК Наименование
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В
(ВЫБРАТЬ
ВтНом.Ссылка КАК Ссылка
ИЗ
ВтНоменклатура КАК ВтНом)
Рассмотрим, что происходит на уровне СУБД при выполнении такого запроса. SQL Server преобразует конструкцию WHERE IN (SELECT...) в так называемое полусоединение (Semi-Join), чаще всего реализуемое оператором Left Semi Join. Это крайне эффективная операция: СУБД сканирует основную таблицу и прекращает поиск во временной таблице сразу же, как только находит первое совпадение. Это гарантирует отсутствие дублирования строк в конечном результате, даже если во временной таблице по какой-то причине оказались дублирующиеся записи.
Альтернативный и очень популярный среди разработчиков 1С способ — это связывание физической таблицы справочника или документа с временной таблицей через оператор связи. Разберем этот вариант на том же примере:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Код КАК Код,
Номенклатура.Наименование КАК Наименование
ИЗ
Справочник.Номенклатура КАК Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтНоменклатура КАК ВтНом
ПО Номенклатура.Ссылка = ВтНом.Ссылка
Этот способ отлично решает задачу фильтрации, но имеет важную семантическую особенность. Если во временной таблице ВтНоменклатура будут содержаться дублирующиеся строки (например, одна и та же ссылка запишется туда дважды), то при использовании ВНУТРЕННЕГО СОЕДИНЕНИЯ строки исходной таблицы «размножатся» в итоговой выборке. Если вы далее рассчитываете суммы или количество, это приведет к грубым ошибкам в отчете. Для предотвращения этого во временной таблице обязательно должны быть только уникальные записи.
Среди разработчиков 1С часто возникают споры о том, какой вариант работает быстрее. Давайте проанализируем ситуацию детально:
Semi-Join (подзапрос), так и для обычного Inner Join. При правильном индексировании разница в производительности будет неощутима.ИНДЕКСИРОВАТЬ ПО при создании временной таблицы.ГДЕ В безопаснее с точки зрения сохранения структуры данных выборки (избежание дублей), однако в некоторых сложных планах запросов СУБД может выбрать менее оптимальный вариант соединения. Внутреннее соединение дает оптимизатору СУБД больше пространства для маневра (например, выбор алгоритмов Hash Match или Merge Join).Особое внимание следует уделить работе с виртуальными таблицами регистров накопления, сведений или бухгалтерии (например, .Остатки, .Обороты). Наложение условий на виртуальные таблицы «снаружи» через секцию ГДЕ или последующее соединение — одна из самых частых причин падения производительности систем 1С.
Рассмотрим пример из практики, когда нам необходимо получить обороты по акциям, которые на текущий момент являются действующими (информация о которых лежит во временной таблице ВтДостАкции).
Неэффективный вариант (соединение после получения данных):
ВЫБРАТЬ
ДвиженияОбороты.Акция,
ДвиженияОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ДвиженияПоАкциям.Обороты КАК ДвиженияОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтДостАкции КАК ВтДостАкции
ПО ДвиженияОбороты.Акция = ВтДостАкции.Акция
При таком подходе СУБД сначала выберет обороты из базы данных по ВСЕМ акциям (что может составить миллионы записей), построит виртуальную таблицу в памяти и только после этого отсечет лишнее с помощью соединения. Это крайне ресурсоемкая операция.
Эффективный вариант (передача условия в параметры виртуальной таблицы):
Для минимизации объема выбираемых данных мы должны передать условие фильтрации непосредственно в параметры виртуальной таблицы. Внутри параметров мы можем использовать подзапрос к нашей временной таблице:
ВЫБРАТЬ
ДвиженияОбороты.Акция,
ДвиженияОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ДвиженияПоАкциям.Обороты(
,
,
,
Акция В
(ВЫБРАТЬ
Вт.Акция
ИЗ
ВтДостАкции КАК Вт)) КАК ДвиженияОбороты
При таком написании платформа 1С транслирует условие фильтрации непосредственно в SQL-запрос, который обращается к физическим таблицам базы данных. СУБД применит фильтр на самом раннем этапе — при чтении индексов регистров, выбрав из базы данных только те записи, которые действительно соответствуют действующим акциям из нашей ВТ. Это гарантирует максимальное быстродействие.
Чтобы ваши запросы всегда выполнялись быстро и не перегружали сервер СУБД, придерживайтесь следующих правил при работе с временными таблицами в условиях:
ПОМЕСТИТЬ ВтНом ИЗ ... ИНДЕКСИРОВАТЬ ПО Ссылка.tempdb на сервере СУБД. Помещение туда сотен тысяч записей без крайней необходимости замедлит работу всей системы.Если НЕ ВременнаяТаблицаПуста() Тогда.... Это поможет избежать выполнения тяжелых «холостых» запросов к базе данных.В (ВЫБРАТЬ...), старайтесь делать выборку уникальной, добавляя ключевое слово РАЗЛИЧНЫЕ в подзапрос, чтобы облегчить построение плана оптимизатору SQL.