Запросы, связывающие первичные документы (например, накладные реализации) и подчиненные им сущности (счета-фактуры), чрезвычайно распространены в аналитических отчетах и обработках выгрузки данных. Однако при росте базы данных подобные запросы начинают сильно тормозить. Пользователи сталкиваются с зависанием системы, а сервер СУБД начинает испытывать колоссальную нагрузку на дисковую подсистему и процессор.
В этой статье мы подробно проанализируем исходный неоптимальный запрос, разберем скрытые архитектурные проблемы платформы 1С и СУБД, а затем по шагам проведем глубокую оптимизацию кода для достижения максимального быстродействия.
Для начала рассмотрим исходный вариант запроса, который был предложен автором темы на форуме:
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Ссылка,
РеализацияТоваровУслуг.СуммаДокумента КАК СуммаДокумента,
РеализацияТоваровУслуг.Контрагент КАК Контрагент,
ВЫБОР
КОГДА НЕ СчетФактураВыданный.Ссылка.Проведен
ТОГДА СчетФактураВыданный.Ссылка = ЗНАЧЕНИЕ(Документ.СчетФактураВыданный.ПустаяСсылка)
ИНАЧЕ СчетФактураВыданный.Ссылка
КОНЕЦ КАК СФ
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактураВыданный
ПО РеализацияТоваровУслуг.Ссылка = СчетФактураВыданный.ДокументОснование
ГДЕ
РеализацияТоваровУслуг.Дата МЕЖДУ &Дата1 И &Дата2
И РеализацияТоваровУслуг.Организация = &Организация
И РеализацияТоваровУслуг.Проведен = ИСТИНА
УПОРЯДОЧИТЬ ПО
РеализацияТоваровУслуг.Дата,
РеализацияТоваровУслуг.Номер
Проанализируем эту конструкцию. На первый взгляд, запрос кажется простым и понятным, но в нем скрыто сразу пять критических проблем производительности и логики:
ВЫБОР мы видим обращение СчетФактураВыданный.Ссылка.Проведен. Поскольку таблица Документ.СчетФактураВыданный уже левым соединением подключена к запросу, обращение через .Ссылка.Проведен является избыточным. Платформа 1С транслирует это обращение в дополнительное (еще одно!) неявное левое соединение на уровне СУБД. Это приводит к лишним логическим чтениям и усложнению плана выполнения.ТОГДА СчетФактураВыданный.Ссылка = ЗНАЧЕНИЕ(Документ.СчетФактураВыданный.ПустаяСсылка). В языке запросов 1С знак равенства = в этой секции интерпретируется как операция сравнения, а не присваивания. В результате СУБД вернет булево значение (Истина/Ложь) вместо ожидаемой пустой ссылки документа. В ветке ИНАЧЕ при этом возвращается ссылка на документ. В итоге поле СФ получит некорректный составной тип данных (Булево + Ссылка), что вызовет проблемы при чтении выборки.УПОРЯДОЧИТЬ ПО РеализацияТоваровУслуг.Дата, РеализацияТоваровУслуг.Номер заставляет СУБД выполнять операцию сканирования кластерного индекса (Clustered Index Scan) или тяжелую операцию сортировки в TempDB (Sort Spool). В СУБД стандартный индекс для документов строится по полям [Организация +] Дата + Ссылка. Поля Номер в этом индексе нет. Сортировка по номерам документов внутри одной секунды на больших базах данных обходится очень дорого.ДокументОснование в документе СчетФактураВыданный имеет составной тип данных (может ссылаться на реализации, акты, отчеты и т.д.). Физически в СУБД составное поле хранится в виде нескольких колонок (тип ссылки и уникальный идентификатор). Прямое левое соединение по такому полю заставляет СУБД сравнивать данные по нескольким колонкам, из-за чего оптимизатор запросов часто отказывается использовать индексы.---
Для начала уберем лишнее обращение через точку к реквизиту Проведен и исправим логику возврата пустой ссылки в конструкции ВЫБОР. Вместо сравнения мы должны напрямую вернуть пустую ссылку. Также избавимся от неявного сравнения Проведен = ИСТИНА, так как поле Проведен само по себе является булевым (согласно стандартам разработки 1С):
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Ссылка,
РеализацияТоваровУслуг.СуммаДокумента КАК СуммаДокумента,
РеализацияТоваровУслуг.Контрагент КАК Контрагент,
ВЫБОР
КОГДА НЕ СчетФактураВыданный.Проведен
ТОГДА ЗНАЧЕНИЕ(Документ.СчетФактураВыданный.ПустаяСсылка)
ИНАЧЕ СчетФактураВыданный.Ссылка
КОНЕЦ КАК СФ
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактураВыданный
ПО РеализацияТоваровУслуг.Ссылка = СчетФактураВыданный.ДокументОснование
ГДЕ
РеализацияТоваровУслуг.Дата МЕЖДУ &Дата1 И &Дата2
И РеализацияТоваровУслуг.Организация = &Организация
И РеализацияТоваровУслуг.Проведен
Мы существенно упростили работу СУБД: теперь оптимизатор строит план выполнения без дополнительного неявного соединения таблицы счетов-фактур самой с собой.
---
Выясним причину, почему сортировка по полю Номер замедляет выполнение запроса. Как мы уже упоминали, стандартный индекс таблицы документов выглядит как Период + Ссылка (или Организация + Период + Ссылка, если включено разделение по организациям). Поля Номер в данном индексе нет. Соответственно, СУБД не может выдать результат сразу в отсортированном виде.
Рассмотрим два варианта решения этой проблемы:
УПОРЯДОЧИТЬ ПО РеализацияТоваровУслуг.Дата, РеализацияТоваровУслуг.Ссылка, СУБД прочитает данные непосредственно из индекса в уже готовом отсортированном порядке без дополнительных затрат.---
Перейдем к наиболее эффективному методу ускорения — пакетным запросам с использованием временных таблиц. Основная идея заключается в том, чтобы сначала отобрать ограниченное количество документов реализации (первичный ключ выборки) во временную таблицу по индексированным полям (Дата и Организация), проиндексировать полученный результат, а затем соединить его с таблицей счетов-фактур.
При этом мы применим функцию ВЫРАЗИТЬ() для типизации поля связи ДокументОснование. Это заставит СУБД выполнять поиск по конкретному индексу таблицы СчетФактураВыданный, минуя проверки других возможных типов документов-оснований.
Разберем оптимальную структуру пакетного запроса:
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Ссылка,
РеализацияТоваровУслуг.СуммаДокумента КАК СуммаДокумента,
РеализацияТоваровУслуг.Контрагент КАК Контрагент,
РеализацияТоваровУслуг.Дата КАК Дата,
РеализацияТоваровУслуг.Номер КАК Номер
ПОМЕСТИТЬ ВТ_Реализации
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
РеализацияТоваровУслуг.Дата МЕЖДУ &Дата1 И &Дата2
И РеализацияТоваровУслуг.Организация = &Организация
И РеализацияТоваровУслуг.Проведен
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Реализации.Ссылка КАК Ссылка,
ВТ_Реализации.СуммаДокумента КАК СуммаДокумента,
ВТ_Реализации.Контрагент КАК Контрагент,
ВЫБОР
КОГДА СчетФактураВыданный.Ссылка ЕСТЬ NULL
ТОГДА ЗНАЧЕНИЕ(Документ.СчетФактураВыданный.ПустаяСсылка)
КОГДА НЕ СчетФактураВыданный.Проведен
ТОГДА ЗНАЧЕНИЕ(Документ.СчетФактураВыданный.ПустаяСсылка)
ИНАЧЕ СчетФактураВыданный.Ссылка
КОНЕЦ КАК СФ
ИЗ
ВТ_Реализации КАК ВТ_Реализации
ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактураВыданный
ПО ВТ_Реализации.Ссылка = ВЫРАЗИТЬ(СчетФактураВыданный.ДокументОснование КАК Документ.РеализацияТоваровУслуг)
УПОРЯДОЧИТЬ ПО
ВТ_Реализации.Дата,
ВТ_Реализации.Номер
---
Проанализируем физический план выполнения, который сформирует оптимизатор СУБД для нашего пакетного запроса:
Организация + Дата. СУБД мгновенно локализует нужный диапазон записей и помещает их во временную таблицу ВТ_Реализации. Количество строк в ней будет относительно невелико (например, несколько тысяч за месяц).ИНДЕКСИРОВАТЬ ПО Ссылка создает индекс по полю связи во временной таблице. Это критически важно для последующего соединения.ВЫРАЗИТЬ(СчетФактураВыданный.ДокументОснование КАК Документ.РеализацияТоваровУслуг) оптимизатор использует внутренний индекс по полю ДокументОснование в таблице счетов-фактур. Поиск каждой соответствующей строки происходит через точечные операции Index Seek (в планах выполнения СУБД часто используется алгоритм Nested Loops).Дата и Номер теперь выполняется не для миллионов записей всей базы данных, а только для небольшого результирующего набора данных временной таблицы, что практически не расходует ресурсы сервера.Применение этой методики позволяет сократить время выполнения запроса с десятков секунд (или даже минут при сильной загрузке дисков) до сотых долей секунды, гарантируя стабильную работу системы даже на терабайтных базах данных.