Вопрос оптимизации запросов является одним из ключевых в разработке на платформе 1С:Предприятие. Часто перед разработчиками встает дилемма: использовать ли временные таблицы для предварительной фильтрации данных или положиться на оптимизатор СУБД и использовать обычное соединение с условием в ГДЕ. Давайте подробно разберем эту ситуацию, проанализируем механизмы работы MS SQL Server и определим лучшие практики.
Рассмотрим ситуацию, когда нам необходимо получить данные из регистра сведений, связанные с определенным документом. Предположим, что в базе данных есть:
Документ.КакойТоДокумент), содержащая миллионы записей.РегистрСведений.КакойТоРегистр), также содержащая миллионы записей.Нам нужно выбрать данные из регистра для конкретного документа (ссылка передается как параметр). Существует два классических подхода к реализации этой задачи.
В этом подходе мы сначала отбираем интересующий нас документ во временную таблицу, а затем соединяем эту временную таблицу с регистром.
Код запроса выглядит следующим образом:
ВЫБРАТЬ
Т1.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ
ИЗ
Документ.КакойТоДокументМиллионЗаписей КАК Т1
ГДЕ
Т1.Ссылка = &Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Ссылка КАК Ссылка,
Т2.Ресурс КАК Значение
ИЗ
ВТ КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КакойТоРегистрМиллоинЗаписей КАК Т2
ПО Т1.Ссылка = Т2.СсылкаНаДокумент
В этом варианте мы сразу соединяем две большие таблицы, но накладываем условие на ссылку из первой таблицы в секции ГДЕ.
ВЫБРАТЬ
Т1.Ссылка КАК Ссылка,
Т2.Ресурс КАК Значение
ИЗ
Документ.КакойТоДокументМиллионЗаписей КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КакойТоРегистрМиллоинЗаписей КАК Т2
ПО Т1.Ссылка = Т2.СсылкаНаДокумент
ГДЕ
Т1.Ссылка = &Ссылка
Среди разработчиков часто встречается мнение, основанное на теоретическом логическом порядке выполнения SQL-запросов: сначала выполняется ИЗ (FROM), затем СОЕДИНЕНИЕ (JOIN), и только в конце ГДЕ (WHERE). Исходя из этой логики, кажется, что во втором варианте СУБД сначала попытается соединить миллионы записей документов с миллионами записей регистра, и только потом отфильтрует результат по одной ссылке.
Однако на практике время выполнения обоих запросов часто оказывается почти одинаковым и составляет доли секунды (при наличии индексов). Давайте разберемся, почему это происходит.
Современные СУБД, такие как MS SQL Server или PostgreSQL, обладают "умными" оптимизаторами запросов. Логический порядок выполнения команд не совпадает с физическим планом выполнения.
Существует механизм, называемый Predicate Pushdown (проталкивание предикатов). Оптимизатор анализирует запрос и видит, что в секции ГДЕ есть строгое условие по уникальному индексу (ссылке документа). Он понимает, что нет смысла соединять все записи. Поэтому физический план выполнения для Варианта 2 будет выглядеть примерно так:
&Ссылка.Таким образом, фильтрация происходит до соединения, а не после. Это делает Вариант 2 крайне эффективным для отбора одиночных значений.
Хотя использование временных таблиц часто рекомендуется стандартами 1С для сложных запросов, в случае выбора одной ссылки (Вариант 1) этот метод может быть менее эффективным из-за накладных расходов:
tempdb.Если временная таблица используется для хранения всего одной строки, затраты на ее создание превышают выигрыш от упрощения плана запроса.
Давайте систематизируем знания и определим критерии выбора метода.
Давайте посмотрим на задачу еще критичнее. Нам нужно получить данные из регистра по ссылке на документ. В Варианте 2 мы делаем ЛЕВОЕ СОЕДИНЕНИЕ от таблицы документа к регистру. Но нужна ли нам вообще таблица документа в запросе?
Если мы уже знаем &Ссылка, и нам нужны данные только из регистра, обращение к таблице Документ.КакойТоДокумент является избыточным чтением. Мы просто проверяем существование документа, что часто не требуется, или выбираем ту же самую ссылку, которая у нас и так есть.
Самым производительным вариантом (Вариант 3) будет запрос напрямую к регистру:
ВЫБРАТЬ
Т.СсылкаНаДокумент КАК Ссылка,
Т.Ресурс
ИЗ
РегистрСведений.КакойТоРегистрМиллоинЗаписей КАК Т
ГДЕ
Т.СсылкаНаДокумент = &Ссылка
Преимущества этого подхода:
Важное замечание про Индексы:
Независимо от выбранного варианта (1, 2 или 3), критически важным является наличие индекса в регистре сведений по полю СсылкаНаДокумент. Если поле измерения не проиндексировано (или не входит в кластерный индекс первым полем), СУБД будет вынуждена делать Table Scan (полное сканирование) таблицы регистра, что на миллионах записей займет секунды или минуты, даже если вы используете временную таблицу.