Другие статьи по оптимизации 1С:

«Оптимизация 1С – Проблемы производительности»

«Как ускорить 1С – Многопоточная обработка данных»

«Анализ запросов с помощью SQL Profiler»

Как в 1С оптимизировать запрос – пример ускорения в 6 раз

Что Вы узнаете из этой статьи?

  • Одну из основных причин медленной работы запросов
  • Анализ возможных методов ускорения запросов
  • Эффективный способ ускорения запросов, в которых происходит обращение через «2 точки»

Часто при внедрении программ «1С: Предприятие 8» возникают ситуации, в которых простые запросы работают достаточно медленно.

Покажем варианты оптимизации таких запросов.

Для примера рассмотрим запрос из реального проекта (в базе клиента он выполнялся более 6 секунд)

ВЫБРАТЬ
ДенежныеСредстваКПоступлениюБезналичныеОбороты.Документ.Контрагент.Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
СУММА(ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход) КАК СуммаПриход
ИЗ
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ.Контрагент.Партнер = &Партнер) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты

СГРУППИРОВАТЬ ПО
ДенежныеСредстваКПоступлениюБезналичныеОбороты.Документ.Контрагент.Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период),
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период)

C первого взгляда все хорошо, но опытный программист увидит неоптимальный код в запросе.

Источником проблем выступает параметр виртуальной таблицы, а точнее – обращение через «две точки» в фильтре.

В общем случае такой подход допустим, но есть одна проблема: поле «Документ» имеет составной тип, и при получении реквизитов данного поля выполняется соединение с каждой таблицей, входящей в составной тип этого поля.

Самым первым вариантом решения в голову приходит использовать конструкцию языка запросов «ВЫРАЗИТЬ», чтобы привести поле «Документ» к некоторому определенному типу. Это позволит избежать соединений с лишними таблицами.

Но по ряду ограничений данный вариант не подходит:

  1. Нам нужны все документы, содержащиеся в составном типе. Таковы условия постановки задачи. Получается, что необходимо фильтровать все типы документов, входящие в составной тип.
  2. Даже если бы не было предыдущего ограничения, то обращение через «две точки» никуда не делось.
  3. Если бы можно было использовать «ВЫРАЗИТЬ», то это не спасало бы ситуацию: в параметрах виртуальной таблицы «ВЫРАЗИТЬ» не дает прироста производительности.

Оптимизация

Исходя из вышесказанного, прежде всего необходимо избавиться от обращения через «две точки» и при этом не испортить саму логику нашего запроса.
Из нескольких способов решения задачи предлагаем два следующих варианта:

Вариант 1

В регистр «ДенежныеСредстваКПоступлениюБезналичные» добавить новое измерение «Партнер», заполняя его при записи движений документов. Ввиду использования условия по данному измерению его необходимо проиндексировать.

После внесенных нами изменений у нас достаточно легко получится наложение фильтра на новое измерение в параметрах виртуальной таблицы:

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Партнер = &Партнер) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты
Что мы видим? Этот запрос начинает работать моментально.

И это, к сожалению, единственный положительный момент, минусов наблюдается существенно больше.

Главный минус – изменение структуры конфигурации, возникают проблемы при последующих обновлениях, использовании типовых обменов и т.д.

К тому же у нас хранится дублируемая информация, что приводит к увеличению размера таблицы, а установка признака индексирования повышает скорость чтения, но при этом замедляет запись в регистр.

Поэтому рассмотрим второй вариант.

Вариант 2

Можно попробовать изменить запрос так, чтобы фильтр по полю «Документ» накладывался примерно следующим образом:

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ В (ВЫБРАТЬ Ссылка ИЗ ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты

Что необходимо сделать, чтобы наш запрос пришел к подобному виду?
Вначале соберем все документы, входящие в составной тип поля «Документы». Для них должно соблюдаться условие:

Контрагент.Партнер = &Партнер

В нашем составном типе определены 5 документов, причем искомый реквизит «Контрагент» присутствует только в документах:

  • ПоступлениеБезналичныхДенежныхСредств
  • СписаниеБезналичныхДенежныхСредств
  • РасходныйКассовыйОрдер
  • ОперацияПоПлатежнойКарте

Далее сформируем временную таблицу для фильтрации. В ней будут документы, у которых реквизит «Партнер» равен нужному значению. Применим полученный фильтр по документам в нашем запросе:

ВЫБРАТЬ ОперацияПоПлатежнойКарте.Ссылка
ПОМЕСТИТЬ ВТ_ДокументыСПартнером
ИЗ Документ.ОперацияПоПлатежнойКарте КАК ОперацияПоПлатежнойКарте
ГДЕ ОперацияПоПлатежнойКарте.Контрагент.Партнер = &Партнер

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ ПоступлениеБезналичныхДенежныхСредств.Ссылка
ИЗ Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств
ГДЕ ПоступлениеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РасходныйКассовыйОрдер.Ссылка
ИЗ Документ.РасходныйКассовыйОрдер КАК РасходныйКассовыйОрдер
ГДЕ РасходныйКассовыйОрдер.Контрагент.Партнер = &Партнер

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ СписаниеБезналичныхДенежныхСредств.Ссылка
ИЗ Документ.СписаниеБезналичныхДенежныхСредств КАК СписаниеБезналичныхДенежныхСредств
ГДЕ СписаниеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер
;

////////////////////////////////////////////////////////////////////////////////

ВЫБРАТЬ
&Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход КАК СуммаПриход
ИЗ
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(,, Месяц, Документ В
(ВЫБРАТЬ
ВТ_ДокументыСПартнером.Ссылка
ИЗ
ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты

С другой стороны, можно сначала получить контрагентов с данным партнером и затем искать документы с фильтром по контрагенту, но особой разницы в скорости не наблюдается.

После проведенной оптимизации запрос стал выполняться менее одной секунды!

Да, при этом он стал сложнее, но нет необходимости в изменении структуры метаданных, как в первом варианте.

Резюме

Вам представлен вариант решения оптимизации достаточно простого запроса, при котором не возникло необходимости в перестройке метаданных, создании дополнительных индексов.

Рекомендуем оптимизировать запросы посредством изменения текста самого запроса.

Бурмистров Андрей

PDF-версия статьи для участников группы ВКонтакте

Мы ведем группу ВКонтакте — http://vk.com/kursypo1c.

Если Вы еще не вступили в группу — сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.

Если Вы уже участник группы — нужно просто повторно авторизоваться в ВКонтакте, чтобы скрипт Вас узнал. В случае проблем решение стандартное: очистить кеш браузера или подписаться через другой браузер.
Станьте экспертом по оптимизации 1С, изучив наш курс
«Ускорение и оптимизация систем на 1С:Предприятие 8.3 (2016). Подготовка на 1С:Эксперт по технологическим вопросам»

Содержание курса и форма заказа: http://курсы-по-1с.рф/1c-v8/optimization/

35 учебных часов, подготовка к 1С:Эксперт, правильная настройка серверной части, оптимизация кода, мониторинг загруженности оборудования и прочие взрослые вещи.

10 Responses to Как в 1С оптимизировать запрос – пример ускорения в 6 раз

  • archie
    Archie

    1. В конце статьи в фразе:
    >>С другой стороны, можно сначала получить контрагентов с данным партнером и затем искать документы с фильтром по контрагенту, но особой разницы в скорости не наблюдается.
    имелась ввиду разница относительно первоначального варианта запроса, или окончательного?
    2. В конечном варианте запроса присутствует один существенный недостаток — после добавления нового вида документа, который является регистраторов данного регистра, запрос перестанет возвращать достоверные данные. Причем выясниться это может только через несколько месяцев. Насколько допустимо писать такие вопросы на реальных задачах? И не будет ли в данном случае более предпочтительным вариант «сначала получить контрагентов с данным партнером»?

    • Андрей Бурмистров

      1. Относительно первоначального варианта
      2. Да, есть такой момент, но в данном случае это было оправдано. При любом изменении конфигурации надо быть внимательным, особенно при таком существенном как добавление нового регистратора. В типовой конфигурации тоже могут быть запросы с ВЫРАЗИТЬ которые так же надо будет переписывать. Этот пример взят с реального проекта по оптимизации, как и большинство примеров описанных в курсе.

  • Андрей
    Андрей

    Изучаю ваш курс Быстрый старт в профессию, там есть подобный пример использования ВТ в параметрах виртуальной таблицы. На платформе 8.2 и 8.3 все работает, на работе стоит 8.1, при формировании запроса в параметрах виртуальной таблицы временная таблица не видна, это ограничения платформы или проблема в другом?

    • Андрей Бурмистров

      С 8.1 работал уже очень давно.
      Если не получается сделать это в конструкторе, попробуйте написать подобный запрос руками.
      Если запрос будет выполняться то это просто недоработка старого конструктора запроса.
      Если не будет выполняться, значит ограничения 8.1.

  • Ivgen
    Ivgen

    Немного не по теме. На сайте ИС выкладываются эти статьи тоже (на примере этой статьи http://infostart.ru/public/306903/kusry-po-1c.ru), и в публикации есть ссылка на текущий материал: «Другие полезные статьи и видео смотрите на нашем сайте: http://Курсы-по-1С.рф«. Только вот ссылка неправильная — указана ссылка статьи на ИС (и здесь рекурсия! ))) ). Уже вторая статья с рекурсивной ссылкой. Первая была http://infostart.ru/public/307547/. Остальные может тоже надо подкорректировать?
    Пишу здесь так как на ИС автор запретил комменты.

    • Татьяна Гужавина

      Добрый день!
      Спасибо. Всё уже исправлено.

  • Данияр
    hjkl

    Добрый день.

    Скажите пожалуйста:
    1) Почему в параметрах виртуальной таблицы «ВЫРАЗИТЬ» не дает прироста производительности?

    2) Во втором примере используется виртуальная таблица, если я правильно понял по рекомендациям 1С можно проиндексировать поля временной таблицы и это увеличивает оптимальность запроса.

    Однако есть мнение что на построение индексации затрачивается время и иногда это даже понижает скорость выполнения запроса.

    Так ли это и в каких случаях нужно использовать индексацию полей временных таблиц, а также почему в вашем примере этого не было сделано?

    3) Насколько необходимо для повышения оптимизации уничтожать временные таблицы в тексте запроса — например у вас во втором примере можно сперва создать временную таблицу, затем выполнить основной запрос и третьим запросом пакета уничтожить 1 временную таблицу.

    4) Правда ли что временные таблицы в период их существования хранятся на жестком диске?

    5) Разве менее оптимальным будет вместо создания временной таблицы во втором примере сделать аналогичный вложенный запрос в параметрах виртуальной таблицы. Таким образом получится 2 запроса вместо 3 — 1 создание вт, 2 запрос к виртуальной таблице и 3 параметрах виртуальной таблицы?
    Хотя количество запросов несколько выше поскольку в первом запросе используется объединение.

    • Татьяна Гужавина

      Добрый день!

      1. Если использовать ВЫРАЗИТЬ в параметрах виртуальной таблицы, то в запросе на языке SQL это преобразуется в условие «CASE WHEN» т.е. не будет никакого ограничения типов.
      Здесь возможно замедление запроса, т.к. индекс в данном случае использоваться не может.

      2. На создание индекса действительно затрачивается время, и если временная таблица маленькая, то нет большого смысла ее индексировать.
      Правила индексирования временных таблиц те же, что и для физических таблиц, надо понимать когда индекс будет использоваться, а когда нет.
      Например нет смысла индексировать ВТ если вы потом выбираете все строки из этой ВТ т.е. будет использоваться сканирование.
      Если у вас например запрос вида «ВремТаблица ЛевоеСоединение ФизическаяТаблица» то зачем вам индексировать поля временной таблицы если вы все равно будете выбирать из нее все строки и индекс использоваться не будет.
      Но если левое соединение заменить внутренним, то это уже другое дело.
      В случае из статьи индексирование никак не сказалось на производительности т.к. ВТ небольшие по объему.

      3. На производительность это никак не влияет.

      4. MS SQL Server всегда пытается создать временную таблицу в памяти, но если ВТ слишком большая и свободной памяти мало, тогда СУБД может принять решение сохранить временную таблицу на диск.

      5. Запрос предложенный вам будет сложнее, а чем сложнее запрос, тем больше вероятность что оптимизатор может ошибиться.
      Здесь лучше использовать несколько маленьких простых запросов которые гарантированно работают быстро на любой СУБД, независимо от условий. В данном случае мы практически не оставляем оптимизатору шансов на ошибку.

  • Крынецкий Александр
    Алексаднр

    Вариант 1
    В регистр «ДенежныеСредстваКПоступлениюБезналичные» добавить новое измерение «Партнер», заполняя его при записи движений документов. Ввиду использования условия по данному измерению его необходимо проиндексировать.

    — Поскольку, используется обращение именно к таблице оборотов, то индексировать новое измерение «Партнер» нет необходимости. В таблице оборотов измерения и так все проиндексированы. Поправьте меня, если я ошибаюсь

    • Татьяна Гужавина

      Добрый день, Александр!

      Регистр ДенежныеСредстваКПоступлениюБезналичные является регистром остатков, а не оборотов.

      По умолчанию в основной таблице регистра накопления создается только 2 индекса:
      1. Период+Регистратор+Номер строки (Кластерный)
      2. Регистратор+Номер строки

      Индексы по измерениям и реквизитам по умолчанию в таблице оборотов не создаются.

      Индекс Период+Измерение1+Измерение2+…+ИзмерениеN создается для таблицы итогов, а не для таблицы оборотов.
      Если добавлять новое измерение в конец списка, то его в любом случае надо будет индексировать.
      Если не проиндексировать, то в таблице оборотов индекса по этому полю не будет вообще, а в таблице итогов это поле стоит не на первом месте, т.е. он не сможет использоваться.

      Даже если бы регистр был оборотным, то нам все равно пришлось бы делать индекс по измерению, т.к. для оборотных регистров по умолчанию создается индекс
      Период+Измерение1+Измерение2+…+ИзмерениеN.
      При добавлении нового измерения в конец списка, данный индекс просто не смог бы использоваться, т.к. наше поле стоит не на первом месте.

Написать ответ

Ваш email не будет опубликован. Обязательные поля отмечены *