В процессе разработки отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда отчет работает мгновенно при выборе конкретного значения (равенство), но «зависает» на долгие минуты, если пользователь устанавливает отбор «В группе» или «В группе из списка». Особенно остро эта проблема проявляется в типовых конфигурациях, таких как «Бухгалтерия предприятия 3.0», при работе с большими справочниками «Контрагенты» или «Номенклатура».
В этой статье мы подробно разберем, почему так происходит, проанализируем внутренние механизмы работы платформы 1С и SQL-сервера, а также рассмотрим практические способы оптимизации запросов для ускорения формирования данных.
Рассмотрим стандартную ситуацию: нам нужно получить данные по продажам и возвратам в разрезе контрагентов. Когда мы используем отбор Контрагент = &ВыбранныйКонтрагент, платформа передает в SQL-запрос простое условие фильтрации по конкретному идентификатору (GUID). SQL-сервер мгновенно находит нужную запись по индексу.
Однако при использовании оператора «В группе» механизм работы кардинально меняется. Разберем по шагам, что делает платформа 1С:
IN (...).Ситуация усугубляется, если запрос написан неоптимально с использованием «разыменования» полей через точку.
Проанализируем фрагмент кода, который часто становится причиной тормозов (аналогичный приведенному в сообщении 8 форума):
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка.Контрагент КАК Контрагент,
РеализацияТоваровУслуг.Номенклатура КАК Номенклатура,
СУММА(РеализацияТоваровУслуг.Количество) КАК Количество
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслуг
ГДЕ
РеализацияТоваровУслуг.Ссылка.Дата МЕЖДУ &ДатаНач И &ДатаКон
СГРУППИРОВАТЬ ПО
РеализацияТоваровУслуг.Номенклатура,
РеализацияТоваровУслуг.Ссылка.Контрагент
В данном примере мы обращаемся к полю Контрагент и Дата через поле Ссылка табличной части Товары. С точки зрения платформы 1С, каждое такое обращение через точку (РеализацияТоваровУслуг.Ссылка.Контрагент) порождает неявное левое соединение (LEFT JOIN) с основной таблицей документа (Header). Если к этому добавляется тяжелый отбор по иерархии, количество соединений и проверяемых условий растет в геометрической прогрессии, что и приводит к выполнению отчета в течение 10–15 минут.
Чтобы существенно ускорить работу, необходимо избавиться от обращений через точку и заменить их явным соединением табличной части с заголовком документа. Рассмотрим оптимизированный вариант запроса, который позволил сократить время формирования с 15 минут до нескольких секунд:
ВЫБРАТЬ
ДокументПродажи.Контрагент КАК Контрагент,
ТабличнаяЧасть.Номенклатура КАК Номенклатура,
СУММА(ТабличнаяЧасть.Количество) КАК Количество,
СУММА(ТабличнаяЧасть.Сумма) КАК Сумма
ИЗ
Документ.РеализацияТоваровУслуг КАК ДокументПродажи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК ТабличнаяЧасть
ПО ДокументПродажи.Ссылка = ТабличнаяЧасть.Ссылка
ГДЕ
ДокументПродажи.Дата МЕЖДУ &ДатаНач И &ДатаКон
И ДокументПродажи.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ДокументПродажи.Контрагент,
ТабличнаяЧасть.Номенклатура
Почему это работает быстрее?
ВНУТРЕННЕЕ СОЕДИНЕНИЕ, которое отсекает лишние данные эффективнее, чем ЛЕВОЕ СОЕДИНЕНИЕ.Контрагент берется напрямую из таблицы ДокументПродажи.Если задача стоит в получении итоговых данных по продажам или взаиморасчетам, обращение напрямую к документам в «1С:Предприятии 8» считается плохим тоном и неэффективным методом. Документы — это объекты для ввода данных, а для отчетности предназначены регистры.
В «Бухгалтерии предприятия 3.0» данные о продажах номенклатуры в разрезе контрагентов лучше всего получать из Регистра бухгалтерии (Хозрасчетный) или специализированных регистров накопления, если они предусмотрены конфигурацией.
Выясним преимущества работы с регистром бухгалтерии:
Обороты), которые уже содержат агрегированные данные.Пример запроса к регистру бухгалтерии для получения выручки по счету 90.01.1:
ВЫБРАТЬ
ХозрасчетныйОбороты.Субконто1 КАК Номенклатура,
ХозрасчетныйОбороты.Субконто2 КАК Контрагент,
ХозрасчетныйОбороты.КоличествоОборотДт КАК Количество,
ХозрасчетныйОбороты.СуммаОборотКт КАК Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&ДатаНач, &ДатаКон, , Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Выручка), , , , ) КАК ХозрасчетныйОбороты
Такой запрос будет работать на порядок быстрее даже при использовании иерархических отборов, так как фильтрация происходит по проиндексированным измерениям виртуальной таблицы.
Иногда требуется оставить отбор именно в настройках компоновки данных. Чтобы СКД не «тормозила» при раскрытии группы, проанализируем вкладку «Компоновка данных» в конструкторе запроса.
Попробуем применить следующий прием: наложим условие не на весь запрос, а на временную таблицу. Если мы предварительно выберем нужных контрагентов во временную таблицу, используя механизм СКД, а затем соединим её с данными документов, общая скорость выполнения возрастет.
Также важно проверить, установлен ли флаг «Использовать быстрый выбор» в свойствах поля. Для справочников с огромной иерархией это может влиять на то, как платформа формирует список элементов для оператора IN в SQL-запросе.
Проанализируем еще несколько факторов, которые могут влиять на скорость:
Родитель. Это критично для быстрого развертывания иерархии платформой.UPDATE STATISTICS для таблиц справочника и документов. Устаревшая статистика — частая причина того, что SQL выбирает медленный план выполнения для оператора IN.Проведен = ИСТИНА. Это не только гарантирует правильность данных, но и позволяет SQL-серверу эффективнее использовать индексы документов.Подводя итог, можно сказать, что решение проблемы медленной работы отбора «В группе» кроется в переходе от неявных соединений через точку к явным JOIN в запросах, а еще лучше — в использовании виртуальных таблиц регистров вместо прямого обращения к табличным частям документов.