Разработка отчетов на системе компоновки данных (СКД) часто кажется простой задачей, пока дело не доходит до работы с реальными объемами данных на серверных базах. Ситуация, когда отчет «летает» в файловой тестовой базе, но намертво «вешает» рабочий сервер, является классической. Особенно остро эта проблема проявляется при использовании СУБД PostgreSQL, которая крайне чувствительна к структуре запроса и соединениям с виртуальными таблицами. В данной статье мы разберем по шагам, как оптимизировать тяжелый запрос СКД, чтобы добиться высокой скорости формирования отчета.
Прежде чем приступать к оптимизации, проанализируем типичные ошибки, которые присутствовали в исходном коде автора темы. Рассмотрим фрагмент проблемного запроса:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваровКомпании.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, Движения, ) КАК ОстаткиИОборотыТоваров
ПО ОстаткиИОборотыТоваров.Номенклатура.Артикул = ГруппыАналогов.АртикулДляПоиска
В этом коротком отрывке скрыто сразу несколько критических проблем:
ОстаткиИОбороты требует от системы вычисления оборотов за период, даже если нам нужны только конечные остатки. Это создает колоссальную нагрузку на дисковую подсистему и процессор.Номенклатура.Артикул заставляет СУБД выполнять неявное соединение с таблицей справочника Номенклатура для каждой строки результата. На уровне SQL это превращается в огромное количество вложенных соединений (JOIN).ЛЕВОЕ СОЕДИНЕНИЕ к виртуальной таблице (особенно СрезПоследних или Остатки) генерирует сложный вложенный подзапрос, который PostgreSQL часто не может эффективно оптимизировать, выбирая план Nested Loop (вложенный цикл).Выясним причину использования конкретных таблиц. Если отчет должен выводить только текущее наличие товара и цены его аналогов, нам категорически не нужна таблица ОстаткиИОбороты. Перейдем к использованию таблицы Остатки. Она работает значительно быстрее, так как обращается к итоговым таблицам регистра, а не пересчитывает движения.
Также важно помнить: все фильтры (склад, организация, номенклатура) должны передаваться в параметры виртуальной таблицы, а не в секцию ГДЕ. Сравним:
Плохо:
ИЗ РегистрНакопления.ТоварыНаСкладах.Остатки() КАК Остатки
ГДЕ Остатки.Склад = &Склад
Хорошо:
ИЗ РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад) КАК Остатки
Во втором случае СУБД отсечет лишние данные на самом раннем этапе, используя индексы таблицы итогов.
Для баз на PostgreSQL (да и на MS SQL) золотым правилом является вынос виртуальных таблиц во временные таблицы (ВТ). Разберем, как это реализовать. Вместо одного огромного запроса с соединениями, мы разобьем задачу на этапы.
Сначала выберем основные остатки во временную таблицу и обязательно ее проиндексируем:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.КоличествоОстаток КАК Количество
ПОМЕСТИТЬ ВТ_ОсновныеОстатки
ИЗ
РегистрНакопления.ОстаткиТоваровКомпании.Остатки(&Период, СкладКомпании = &СкладКомпании) КАК Остатки
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Зачем нужна индексация? Без ключевого слова ИНДЕКСИРОВАТЬ ПО последующее соединение этой ВТ с другими таблицами будет происходить крайне медленно (сканирование всей таблицы вместо поиска по индексу).
Виртуальная таблица СрезПоследних является одной из самых «дорогих». Если мы соединяем её в основном запросе, система может выполнять поиск цены для каждой строки товара отдельно. Правильная стратегия — подготовить временную таблицу цен только для тех товаров, которые уже попали в нашу выборку.
Проанализируем пример кода для подготовки цен:
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
Цены.Цена КАК Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
РегистрСведений.Цены.СрезПоследних(&Период, ТипЦен = &ТипЦен) КАК Цены
ГДЕ
Цены.Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_ОсновныеОстатки)
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Такой подход гарантирует, что мы не будем тянуть из базы весь прайс-лист, а ограничимся только нужными позициями.
Как мы уже выяснили, использование Номенклатура.Артикул в условиях связи — это яд для производительности. Если данные в регистре аналогов связаны только по артикулу (строке), нам нужно получить этот артикул заранее.
Рассмотрим, как подготовить данные для связи аналогов:
ВЫБРАТЬ
Остатки.Номенклатура КАК СсылкаНоменклатура,
Остатки.Номенклатура.Артикул КАК АртикулДляСвязи,
Остатки.Количество
ПОМЕСТИТЬ ВТ_ДанныеСАртикулом
ИЗ
ВТ_ОсновныеОстатки КАК Остатки
ИНДЕКСИРОВАТЬ ПО
АртикулДляСвязи
;
Теперь мы можем соединять нашу временную таблицу с регистром аналогов по текстовому полю АртикулДляСвязи, что будет в десятки раз быстрее, чем постоянное обращение к справочнику номенклатуры в цикле запроса.
Иногда даже оптимизированный запрос с множеством ЛЕВОЕ СОЕДИНЕНИЕ остается тяжелым. В СКД есть мощный механизм, который часто недооценивают — Связи наборов данных.
Вместо того чтобы пытаться получить всё (основной товар, аналоги, цены основного товара, цены аналогов) в одном запросе, мы можем создать два набора данных:
&ИдентификаторГруппы или &Артикул.На вкладке «Связи наборов данных» мы указываем связь по соответствующему полю. СКД работает очень интеллектуально: она сначала выполнит первый запрос, получит список нужных артикулов и передаст их во второй запрос как массив параметров. Это позволяет избежать гигантских декартовых произведений таблиц на уровне СУБД.
Подведем итог и составим чек-лист для ускорения вашего отчета:
ОстаткиИОбороты на Остатки, если не требуется анализ движений (приходов и расходов) за период.Номенклатура, Склад, Артикул).ГДЕ Склад = &Склад в конце запроса, пишите это внутри скобок виртуальной таблицы.Применение этих техник позволит сократить время формирования отчета с нескольких минут (или бесконечного зависания) до нескольких секунд, обеспечив стабильную работу системы даже под высокой нагрузкой.