При разработке в 1С перед программистами часто встает вопрос о наиболее эффективном способе формирования запросов к базе данных. Существует несколько основных подходов: использование пакетных запросов с временными таблицами, вложенных запросов и наборов данных в Системе Компоновки Данных (СКД). Каждый из этих методов имеет свои преимущества и недостатки, и понимание, когда какой подход применять, критически важно для создания производительного, читаемого и легко сопровождаемого кода.
Давайте подробно разберем каждый из этих способов, их особенности, а также общие рекомендации по оптимизации запросов, чтобы вы могли улучшить свой подход к разработке.
1. Пакетные запросы и временные таблицы
Пакетный запрос в 1С представляет собой последовательность из нескольких SQL-запросов, разделенных символом «;», которые выполняются за одно обращение к серверу базы данных. Ключевое преимущество пакетных запросов заключается в возможности использовать временные таблицы (ВТ) для хранения промежуточных результатов. Это один из наиболее рекомендуемых способов формирования сложных запросов.
Преимущества пакетных запросов с временными таблицами:
Оптимизация производительности: Мы можем избежать многократного выполнения одних и тех же запросов или дорогостоящих операций. Если одни и те же данные нужны несколько раз на разных этапах запроса, мы сначала помещаем их во временную таблицу, а затем используем эту ВТ как источник данных. Это значительно сокращает количество обращений к базе данных и ускоряет выполнение запросов, особенно при работе с большими объемами данных.
Улучшение читаемости и сопровождаемости кода: Разделение большого, сложного запроса на несколько более простых шагов с использованием временных таблиц делает код намного более наглядным и удобным для понимания. Каждый шаг становится логически завершенным, что упрощает отладку и последующую доработку. Мы можем легко понять, что происходит на каждом этапе обработки данных. Один из участников обсуждения справедливо заметил, что пакетный запрос «на порядок удобнее дорабатывать».
Гибкость: Временные таблицы могут служить источником данных для последующих запросов в рамках того же пакета. Кроме того, через МенеджерВременныхТаблиц мы можем передавать ВТ между различными запросами, расширяя возможности их использования.
Управление жизненным циклом: Менеджер временных таблиц 1С автоматически управляет созданием и удалением временных таблиц из базы данных, а также их областью видимости, избавляя нас от ручной работы по очистке.
Недостатки пакетных запросов с временными таблицами:
Накладные расходы: При создании временных таблиц система фактически создает реальные объекты в базе данных, которые затем уничтожает. Этот процесс требует времени и ресурсов, особенно от дисковой подсистемы. Однако эти накладные расходы обычно окупаются многократно за счет повышения производительности при повторном использовании данных или уменьшении объема обработки.
Необходимость контроля: Использование временных таблиц должно быть осознанным. Если мы просто копируем данные без агрегирования или существенного сокращения общего объема выборки, ВТ могут, наоборот, увеличить нагрузку. Важно, чтобы временная таблица выполняла функцию промежуточного хранилища для уже обработанных или отфильтрованных данных.
Практический пример пакетного запроса:
// Шаг 1: Получаем основные данные и помещаем их во временную таблицу
ВЫБРАТЬ
ЗаказыПокупателей.Ссылка КАК Заказ,
ЗаказыПокупателей.Контрагент КАК Контрагент,
ЗаказыПокупателей.СуммаДокумента КАК СуммаЗаказа
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
Документ.ЗаказПокупателя КАК ЗаказыПокупателей
ГДЕ
ЗаказыПокупателей.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания;
// Шаг 2: Получаем данные по оплатам и помещаем их во временную таблицу
ВЫБРАТЬ
ПоступлениеБезналичныхДенежныхСредств.Ссылка КАК Оплата,
ПоступлениеБезналичныхДенежныхСредств.Контрагент КАК Контрагент,
ПоступлениеБезналичныхДенежныхСредств.СуммаДокумента КАК СуммаОплаты
ПОМЕСТИТЬ ВТ_Оплаты
ИЗ
Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств
ГДЕ
ПоступлениеБезналичныхДенежныхСредств.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания;
// Шаг 3: Объединяем данные из временных таблиц
ВЫБРАТЬ
ВТ_Заказы.Заказ,
ВТ_Заказы.Контрагент,
ВТ_Заказы.СуммаЗаказа,
ВТ_Оплаты.Оплата,
ВТ_Оплаты.СуммаОплаты
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Оплаты КАК ВТ_Оплаты
ПО ВТ_Заказы.Контрагент = ВТ_Оплаты.Контрагент;
В этом примере мы сначала отбираем заказы, затем оплаты, а потом объединяем их. Если бы мы пытались сделать это одним запросом с вложенными запросами, логика стала бы гораздо сложнее, а производительность снизилась бы, если бы одни и те же данные приходилось бы выбирать несколько раз.
2. Вложенные запросы
Вложенный запрос – это SQL-запрос, который является частью другого SQL-запроса, используя его результат в качестве источника данных или условия. Это один из старых способов структурирования запросов, который ранее был популярен, но в современной разработке на 1С не рекомендуется к широкому применению.
Недостатки вложенных запросов (почему 1С не рекомендует их):
Снижение производительности: Мы часто сталкиваемся с тем, что оптимизатор СУБД не всегда может корректно определить размер выборки вложенного запроса и построить оптимальный план выполнения. Это может привести к значительным задержкам. Вложенные запросы, особенно используемые в условиях соединения (JOIN), могут быть крайне неэффективными, приводя к замедлению или даже неработоспособности запроса на больших объемах данных. Каждый вложенный запрос может выполняться отдельно или многократно для каждой строки внешнего запроса. Один из участников прямо заявляет: "Вложенные запросы - вообще никогда не рекомендуется использовать", а другой добавляет, что они "при компиляции 1с могут значительно медленнее работать".
Сложность отладки и сопровождения: Глубоко вложенные запросы крайне трудно читать, отлаживать и поддерживать. Их логика становится запутанной, что затрудняет поиск ошибок и внесение изменений.
Многократное выполнение: Если один и тот же вложенный запрос используется несколько раз в одном основном запросе, он будет выполняться повторно, что приводит к избыточным вычислениям и снижению производительности.
Ограниченная поддержка: В некоторых сценариях, например, в динамических списках, есть ограничения на использование временных таблиц, что иногда вынуждает использовать вложенные запросы. Однако это скорее исключение, чем правило.
Изначально вложенные запросы были актуальны до появления временных таблиц. Как упоминается в обсуждении, "Вложенные запросы были актуальны до появления временных таблиц (которые вроде как считаются более прогрессивным и удобным средством)". Таким образом, использование временных таблиц вместо вложенных запросов почти всегда делает запрос более оптимальным и понятным.
Пример вложенного запроса (для понимания, но не для рекомендации):
ВЫБРАТЬ
Заказы.Заказ,
Заказы.Контрагент,
Заказы.СуммаЗаказа,
Оплаты.СуммаОплаты
ИЗ
(ВЫБРАТЬ
ЗаказыПокупателей.Ссылка КАК Заказ,
ЗаказыПокупателей.Контрагент КАК Контрагент,
ЗаказыПокупателей.СуммаДокумента КАК СуммаЗаказа
ИЗ
Документ.ЗаказПокупателя КАК ЗаказыПокупателей
ГДЕ
ЗаказыПокупателей.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания) КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ
ПоступлениеБезналичныхДенежныхСредств.Контрагент КАК Контрагент,
СУММА(ПоступлениеБезналичныхДенежныхСредств.СуммаДокумента) КАК СуммаОплаты
ИЗ
Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств
ГДЕ
ПоступлениеБезналичныхДенежныхСредств.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
СГРУППИРОВАТЬ ПО
ПоступлениеБезналичныхДенежныхСредств.Контрагент) КАК Оплаты
ПО Заказы.Контрагент = Оплаты.Контрагент
Сравните этот пример с пакетным запросом выше. Читабельность и возможность отладки значительно ухудшаются, особенно при увеличении сложности.
3. Наборы данных в СКД (Система Компоновки Данных)
СКД – это мощный инструмент для построения отчетов в 1С, который позволяет нам гибко настраивать структуру отчета, его параметры, группировки и вычисления. СКД позволяет использовать несколько наборов данных, которые затем могут быть связаны между собой.
Преимущества использования нескольких наборов данных в СКД:
Читаемость и структурированность: Разделение сложного отчета на несколько наборов данных существенно улучшает его читаемость и логическую структуру. Каждый набор данных может отвечать за свой логический блок информации, что делает разработку и поддержку отчетов более удобными.
Гибкость настройки: СКД позволяет объединять данные из различных источников (например, из запроса и из объекта/таблицы значений) и гибко настраивать их связь. Это открывает широкие возможности для формирования сложных отчетов, где данные берутся из разных мест.
Расчет итогов: Использование отдельных наборов данных упрощает корректный расчет итогов, в том числе в случаях, когда требуется исключить дубли при объединении или выполнить специфические агрегации.
Передача параметров: Параметры могут легко передаваться из одного набора данных в другой. Это предоставляет нам аналог коррелирующего запроса, но с гораздо большей гибкостью и контролем.
Декомпозиция сложных отчетов: Мы можем разбить большой, сложный запрос на несколько логических частей. Это особенно полезно для отчетов со специфичным расчетом итогов или сложной логикой получения данных.
Работа с иерархиями: СКД активно используется для эффективной работы с иерархическими структурами данных, позволяя строить отчеты с различными уровнями детализации.
Автоматическая оптимизация: СКД способна самостоятельно отбирать, сортировать и размещать данные, а также оптимизировать запросы на основе настроек отчета, что часто улучшает производительность без прямого участия программиста.
Независимость от кода: СКД позволяет создавать значительную часть отчетов без написания программного кода, используя набор данных-объект для отчетов, основанных на таблицах значений, что упрощает разработку для пользователей и аналитиков.
В целом, СКД и набор запросов — это "фишка, которая позволяет расширить удобство разработки и доработки", как метко заметил один из участников обсуждения. Мы получаем мощный инструмент для создания гибких и производительных отчетов.
4. Общие рекомендации по оптимизации запросов в 1С
Помимо выбора конкретного способа формирования запроса, мы должны учитывать ряд общих рекомендаций, которые помогут сделать наши запросы максимально эффективными:
Используйте временные таблицы вместо вложенных запросов. Как мы уже выяснили, это почти всегда приводит к более оптимальному и понятному запросу, особенно при работе с большими объемами данных или когда промежуточные результаты используются многократно.
Применяйте отборы внутри виртуальных таблиц. Это позволяет нам уменьшить объем данных на самых ранних этапах выполнения запроса, что существенно снижает нагрузку на СУБД. Например, вместо выбора всего регистра и последующей фильтрации, мы указываем отбор прямо в виртуальной таблице ОстаткиИОбороты.
Избегайте разыменования ссылочных полей составного типа без типизации. Когда мы разыменовываем поле составного типа без указания конкретного типа (например, Номенклатура.Наименование вместо Номенклатура.Номенклатура.Наименование, если Номенклатура имеет составной тип), система вынуждена проверять все возможные типы, что увеличивает число используемых источников и замедляет запрос.
Анализируйте план выполнения запроса. Мы можем использовать встроенные средства Конструктора запросов 1С или инструменты самой СУБД (например, SQL Server Management Studio для MS SQL Server, pgAdmin для PostgreSQL) для анализа плана выполнения. Это позволяет нам выявить узкие места, неоптимальные индексы и другие причины низкой производительности.
Используйте агрегатные функции правильно. Если нам нужен только итог (например, сумма), нет необходимости выбирать детальные записи. Агрегация на ранних этапах снижает объем передаваемых и обрабатываемых данных.
Разбивайте сложные запросы на более простые. Использование пакетных запросов с временными таблицами или нескольких наборов данных в СКД позволяет нам декомпозировать сложную логику, что улучшает как читаемость, так и производительность. Как было сказано, "самое простое читайте книжки... да и вообще больше читайте и еще больше экспериментируйте в консоли запросов с замером производительности при этом обращайте внимание на удобочитаемость запросов, на удобство поддержки".
Тестируйте и замеряйте производительность. Это единственный надежный способ выявить "виновников" низкой скорости работы. Мы должны регулярно проводить замеры на реальных или приближенных к реальным объемам данных.
Помните о специфике СУБД. Поведение оптимизатора может значительно отличаться в разных СУБД (MS SQL Server, PostgreSQL, Oracle), что влияет на план выполнения запроса. Как один из участников справедливо отметил, "1с - orm, который работает на куче субд . анализаторы разных субд по-разному строят план выполнения запроса." Это означает, что компетенция оптимизации запроса часто относится к эксплуатации, а не к разработке, и может включать модификацию запросов, индексов или условий исполнения. Разработчики должны предполагать среду исполнения, но для 1С это может быть проблематично из-за "много субдшности" и смеси OLAP/OLTP нагрузок.
Обращайте внимание на удобочитаемость и удобство поддержки. Даже если запрос кажется производительным, его невозможность прочитать или доработать в будущем создаст больше проблем, чем решит. Важно найти баланс между производительностью и сопровождаемостью. Один из участников отметил, что разбросанные по функциям запросы в ЗУП могут быть сделаны так "наверно так отлаживать, корректировать и дорабатывать проще и быстрее, чем разбираться в одном очень большом запросе". Это важный аспект декомпозиции, который мы должны учитывать.
Таким образом, мы видим, что в современной разработке на 1С предпочтение следует отдавать пакетным запросам с использованием временных таблиц для сложных выборок данных и нескольким наборам данных в СКД для построения гибких и производительных отчетов. Вложенные запросы, по большей части, уступили место более эффективным и читаемым решениям. Всегда помните о необходимости баланса между производительностью, читаемостью и удобством сопровождения кода.