Другие статьи по оптимизации 1С:
«Оптимизация 1С – Проблемы производительности»
«Как ускорить 1С – Многопоточная обработка данных»
«Анализ запросов с помощью SQL Profiler»
Как в 1С оптимизировать запрос – пример ускорения в 6 раз
- Одну из основных причин медленной работы запросов
- Анализ возможных методов ускорения запросов
- Эффективный способ ускорения запросов, в которых происходит обращение через «2 точки»
Часто при внедрении программ «1С: Предприятие 8» возникают ситуации, в которых простые запросы работают достаточно медленно.
Покажем варианты оптимизации таких запросов.
Для примера рассмотрим запрос из реального проекта (в базе клиента он выполнялся более 6 секунд)
ДенежныеСредстваКПоступлениюБезналичныеОбороты.Документ.Контрагент.Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
СУММА(ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход) КАК СуммаПриход
ИЗ
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ.Контрагент.Партнер = &Партнер) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты
СГРУППИРОВАТЬ ПО
ДенежныеСредстваКПоступлениюБезналичныеОбороты.Документ.Контрагент.Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период),
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период)
C первого взгляда все хорошо, но опытный программист увидит неоптимальный код в запросе.
Источником проблем выступает параметр виртуальной таблицы, а точнее – обращение через «две точки» в фильтре.
В общем случае такой подход допустим, но есть одна проблема: поле «Документ» имеет составной тип, и при получении реквизитов данного поля выполняется соединение с каждой таблицей, входящей в составной тип этого поля.
Самым первым вариантом решения в голову приходит использовать конструкцию языка запросов «ВЫРАЗИТЬ», чтобы привести поле «Документ» к некоторому определенному типу. Это позволит избежать соединений с лишними таблицами.
Но по ряду ограничений данный вариант не подходит:
- Нам нужны все документы, содержащиеся в составном типе. Таковы условия постановки задачи. Получается, что необходимо фильтровать все типы документов, входящие в составной тип.
- Даже если бы не было предыдущего ограничения, то обращение через «две точки» никуда не делось.
- Если бы можно было использовать «ВЫРАЗИТЬ», то это не спасало бы ситуацию: в параметрах виртуальной таблицы «ВЫРАЗИТЬ» не дает прироста производительности.
Оптимизация
Исходя из вышесказанного, прежде всего необходимо избавиться от обращения через «две точки» и при этом не испортить саму логику нашего запроса.
Из нескольких способов решения задачи предлагаем два следующих варианта:
Вариант 1
В регистр «ДенежныеСредстваКПоступлениюБезналичные» добавить новое измерение «Партнер», заполняя его при записи движений документов. Ввиду использования условия по данному измерению его необходимо проиндексировать.
После внесенных нами изменений у нас достаточно легко получится наложение фильтра на новое измерение в параметрах виртуальной таблицы:
И это, к сожалению, единственный положительный момент, минусов наблюдается существенно больше.
Главный минус – изменение структуры конфигурации, возникают проблемы при последующих обновлениях, использовании типовых обменов и т.д.
К тому же у нас хранится дублируемая информация, что приводит к увеличению размера таблицы, а установка признака индексирования повышает скорость чтения, но при этом замедляет запись в регистр.
Поэтому рассмотрим второй вариант.
Вариант 2
Можно попробовать изменить запрос так, чтобы фильтр по полю «Документ» накладывался примерно следующим образом:
Что необходимо сделать, чтобы наш запрос пришел к подобному виду?
Вначале соберем все документы, входящие в составной тип поля «Документы». Для них должно соблюдаться условие:
Контрагент.Партнер = &Партнер
- ПоступлениеБезналичныхДенежныхСредств
- СписаниеБезналичныхДенежныхСредств
- РасходныйКассовыйОрдер
- ОперацияПоПлатежнойКарте
Далее сформируем временную таблицу для фильтрации. В ней будут документы, у которых реквизит «Партнер» равен нужному значению. Применим полученный фильтр по документам в нашем запросе:
ПОМЕСТИТЬ ВТ_ДокументыСПартнером
ИЗ Документ.ОперацияПоПлатежнойКарте КАК ОперацияПоПлатежнойКарте
ГДЕ ОперацияПоПлатежнойКарте.Контрагент.Партнер = &Партнер
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ПоступлениеБезналичныхДенежныхСредств.Ссылка
ИЗ Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств
ГДЕ ПоступлениеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ РасходныйКассовыйОрдер.Ссылка
ИЗ Документ.РасходныйКассовыйОрдер КАК РасходныйКассовыйОрдер
ГДЕ РасходныйКассовыйОрдер.Контрагент.Партнер = &Партнер
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ СписаниеБезналичныхДенежныхСредств.Ссылка
ИЗ Документ.СписаниеБезналичныхДенежныхСредств КАК СписаниеБезналичныхДенежныхСредств
ГДЕ СписаниеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
&Партнер,
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход КАК СуммаПриход
ИЗ
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(,, Месяц, Документ В
(ВЫБРАТЬ
ВТ_ДокументыСПартнером.Ссылка
ИЗ
ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты
С другой стороны, можно сначала получить контрагентов с данным партнером и затем искать документы с фильтром по контрагенту, но особой разницы в скорости не наблюдается.
После проведенной оптимизации запрос стал выполняться менее одной секунды!
Да, при этом он стал сложнее, но нет необходимости в изменении структуры метаданных, как в первом варианте.
Резюме
Вам представлен вариант решения оптимизации достаточно простого запроса, при котором не возникло необходимости в перестройке метаданных, создании дополнительных индексов.
Рекомендуем оптимизировать запросы посредством изменения текста самого запроса.
PDF-версия статьи для участников группы ВКонтакте
Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.
Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.
Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)
«Ускорение и оптимизация систем на 1С:Предприятие 8.3 (2016). Подготовка на 1С:Эксперт по технологическим вопросам»
Содержание курса и форма заказа: https://курсы-по-1с.рф/1c-v8/optimization/
35 учебных часов, подготовка к 1С:Эксперт, правильная настройка серверной части, оптимизация кода, мониторинг загруженности оборудования и прочие взрослые вещи.
При использовании ВТ всегда нужно думать о их возможном размере. Одно дело если вы выбираете список документов по определенному клиенту, которых в базе десятки тысяч, с примерно равным количеством документов по каждому, и потом находите все записи только по этим документам среди всех документов. Соответственно скорость при удаче можно увеличить в эти самые десятки тысяч раз. Другое дело если есть контрагенты, например “частное лицо”, пусть даже несколько, например по каждому филиалу один, но документов частных лиц в целом половина от общего объема документов, и мы в результате ВТ получим список документов допустим 1/50 всех документов, затем по нему ставим отбор.. и не видим ускорения, скорее замедление, а можно случайно и блокировку поставить на несколько минут, которая по цепочке вызовет блокировку продаж на данном подразделении (даже управляемые блокировки могут не помочь, если продажу именно по этому клиенту и надо делать, или ввиду большого объема выгрузки SQL может решить “для ускорения запроса” на время формирования ВТ временно наложить блокировку на исходную таблицу, хоть его об этом и не просили).
В общем при оценке оптимизации запроса нужно оценивать еще и исходные данные, что вы там выбираете.
Скажу проще, при оптимизации нужно оценивать эффективность вашей оптимизации, это обычный здравый смысл.
P.S. Запросы на чтение не ставят блокировок в 8.3
Только что столкнулся как раз. Ищу решение.
Тоже на PostgreSQL работает база. Нагруженная. Есть справочник, условно Объект. При количестве объектов до 6 миллионов 2й вариант худо бедно работал. При резком увеличении объектов за 12 миллионов стало нереально ждать в таком виде результат запроса. РС со срезами последних с составным измерением Объект в котором много разных объектов метаданных и ресурс Статус. Как пример одного регистра и их ещё не один и не два…
И все в режиме 8.2 пока.
Отбор в параметре ВТ типа:
ОбъектТОИР В
(ВЫБРАТЬ
Объект.Ссылка
ИЗ
ВТ_Объекты КАК Объект)
Вешает запрос на часы, если же просто левое соединение сделать, то за несколько минут результат получится.
Что бы разобраться в вашей ситуации нужен как минимум текст запроса и его план.
Если там срез последних то велика вероятность что дело как раз в этом, а не в составном типе, и возможно написав срез самостоятельно, вы решите эту проблему.
Ну посмотрел ещё раз Ваш курс по оптимизации и устаканил ещё раз понимание.
Сначала отдельными пакетами выбрал все используемые в запросе виртуальные таблицы, к сожалению отборы не наложить заранее и только целиком грузим виртуальную таюлицу во временную. Проиндексировал поля по которым связи будут. Добавил их в начало самое запроса.
И затем в используемых местах заменил виртуальные таблицы на эти временные таблицы.
Вместо нескольких часов от пол минуты до минут 5 выборка формируется. Зависит от отборов.
Так что спасибо за курсы!
:о)
Это конечно все здорово, а как оптимизировать большие запросы в динамических списках? Допустим если переводить базу из MS SQL в POSTGRES запрос с соединениями в динамическом списке в MS SQL выполняется пару секунд, и при прокрутке списка все нормально, а в POSTGRES может тормозить до пару минут и работа становиться адом, как в такой ситуации быть?
Тут есть несколько вариантов.
1. Переписать запрос с использованием временных таблиц. Начиная с версии 8.3.8 это возможно.
2. Максимально упростить запрос и возможно не выводить какие-то поля если они не нужны. Зачастую в список выводят все колонки какие надо и не надо. Можно выяснить какие колонки действительно используются и выводить только их, и в результате упростить запрос.
3. Отказаться вообще от дин. списка и заменить его обычной таблицей, которая будет заполнятся по кнопке. При этом просто вывести фильтры на форму. Получиться некое подобие отчета, но для сложных запросов и нужен отчет, динамические списки изначально рассчитаны на простые запросы.
Статья преподносит вариант 2 как лучший, без минусов. Но это не так, по возможности следует делать по варианту 1.
1. Производительность. Нет никакой гарантии того, что запрос второго варианта будет всегда быстрее выполняться первого. Время на создание временной таблицы сильно зависти от скорости дисковой подсистемы. При неудачной конфигурации сервера даже простенькие запросы с ВТ могут выполняться медленнее сложных без ВТ. На каком объеме данных вы сверяли запросы? А если регистраторов не 2, а 20? А если подходящих ссылок регистраторов миллионы? А двойное разыменование в строке “ПоступлениеБезналичныхДенежныхСредств.Контрагент.Партнер” вас не смущает?
2. В варианте 2 не требуется редактировать конфигурацию. Весомый плюс, если конфигурация на замке и нужно только сделать внешний отчет к регистру без нужного измерения. Если же конфигурация снята с замка то делать следует только так, как методологически будет правильным – отбросить страх и лень, добавить измерение, включить при необходимости индексирование, подправить планы обмена и все что еще потребуется.
Вариант 2 – это костыль. Усложнение добавления регистраторов в регистр. Ловушка, в которую по забывчивости легко попасть даже самому автору такого запроса. Разбухание одной строчки запроса на сотни. Ухудшение читабельности, которое не так заметно в единичном случае, но доставит много головной боли при поддерживании напичканной такими трюками конфигурации.
Ну, а виноваты во всем авторы платформы, которые не закрывают огромные дыры в этой абстракции над SQL. Если они дали возможность писать “Документ.Контрагент.Партнер = “, то и должны были поработать над тем, чтобы платформа переводила текст в SQL без огромных провалов в производительности. А так в эту ловушку попадается каждый первый начинающий разработчик.
Как правило за скорость всегда приходится чем-то расплачиваться, в данном случае это универсальность и читаемость кода. Если хотите ездить на быстрой машине, будьте готовы к жесткой подвеске, вы платите комфортом. Если нужна быстрая система, то придется поработать над кодом и вовсе не факт что кода станет меньше и он станет проще. Вы в первую очередь работаете для пользователей, а не для себя и нужно что бы в первую очередь пользователи были довольны и система работала быстро. Лучше большой запрос работающий быстро, чем маленький и удобный для программиста, но работающий медленно, к тому же комментарии еще никто не запрещал.
Насчет скорости работы ВТ, я еще не встречал такую дисковую систему где скан большой таблицы был бы быстрее создания временной таблицы.
Именно в моей ситуации были испробованы разные варианты запросов, в том числе убиралось обращение через 2 точки, вариант представленный в статье был самым оптимальным.
На мой взгляд решить проблему исправлением текста запроса проще чем добавлять новое измерение.
Всегда есть несколько вариантов решения любой проблемы, и у каждого из них есть свои плюсы и минусы, просто выбираете тот вариант который больше подходит именно вам.
Выдержка из Рекомендаций по оптимизации запросов.
Использование подзапросов в условии соединения
Рекомендации
Не следует использовать подзапросы в условии соединения. Это может привести к значительному замедлению запроса и (в отдельных случаях) к его полной неработоспособности на некоторых СУБД. Пример запроса с использованием подзапроса в условии соединения:
Запрос.Текст = “ВЫБРАТЬ
| ОстаткиТоваров.Номенклатура КАК Номенклатура,
| Цены.Цена КАК ЦенаПрошлогоМесяца
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(…) КАК ОстаткиТоваров
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
| ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
| Цены.Период В (
| ВЫБРАТЬ МАКСИМУМ(ЦеныПрошлогоМесяца.Период)
| ИЗ РегистрСведений.Цена КАК ЦеныПрошлогоМесяца
| ГДЕ ЦеныПрошлогоМесяца.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ)
| И ЦеныПрошлогоМесяца.Номенклатура = ОстаткиТоваров.Номенклатура
| )
| ГДЕ ОстаткиТоваров.Склад = &Склад";
В данном случае подзапрос в условии соединения используется для получения как бы "среза последних" на конец предыдущего периода. Причем, для каждой номенклатуры период может быть разным. Подобный запрос рекомендуется переписать с использованием временных таблиц. Например, это можно сделать следующим образом:
Запрос.Текст = "
// Максимальные даты установки цен в прошлом периоде для данных номенклатур
|ВЫБРАТЬ
| ОстаткиТоваров.Номенклатура КАК Номенклатура,
| МАКСИМУМ(Цены.Период) КАК Период
|ПОМЕСТИТЬ ДатыПоНоменклатурам
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(…) КАК ОстаткиТоваров
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
| ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
| Цены.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ)
| СГРУППИРОВАТЬ ПО ОстаткиТоваров.Номенклатура
| ГДЕ ОстаткиТоваров.Склад = &Склад;
// Выбрать данные по цене за найденный период
|ВЫБРАТЬ
| ДатыПоНоменклатурам.Номенклатура КАК Номенклатура,
| Цены.Цена КАК ЦенаПрошлогоМесяца
|ИЗ ДатыПоНоменклатурам
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
| ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
| Цены.Период = ДатыПоНоменклатурам.Период
";
Вопрос. Почему вы не используете данную рекомендацию при оптимизации запроса?
А где вы здесь видите подзапрос в условии соединения?
1. В конце статьи в фразе:
>>С другой стороны, можно сначала получить контрагентов с данным партнером и затем искать документы с фильтром по контрагенту, но особой разницы в скорости не наблюдается.
имелась ввиду разница относительно первоначального варианта запроса, или окончательного?
2. В конечном варианте запроса присутствует один существенный недостаток – после добавления нового вида документа, который является регистраторов данного регистра, запрос перестанет возвращать достоверные данные. Причем выясниться это может только через несколько месяцев. Насколько допустимо писать такие вопросы на реальных задачах? И не будет ли в данном случае более предпочтительным вариант “сначала получить контрагентов с данным партнером”?
1. Относительно первоначального варианта
2. Да, есть такой момент, но в данном случае это было оправдано. При любом изменении конфигурации надо быть внимательным, особенно при таком существенном как добавление нового регистратора. В типовой конфигурации тоже могут быть запросы с ВЫРАЗИТЬ которые так же надо будет переписывать. Этот пример взят с реального проекта по оптимизации, как и большинство примеров описанных в курсе.
Изучаю ваш курс Быстрый старт в профессию, там есть подобный пример использования ВТ в параметрах виртуальной таблицы. На платформе 8.2 и 8.3 все работает, на работе стоит 8.1, при формировании запроса в параметрах виртуальной таблицы временная таблица не видна, это ограничения платформы или проблема в другом?
С 8.1 работал уже очень давно.
Если не получается сделать это в конструкторе, попробуйте написать подобный запрос руками.
Если запрос будет выполняться то это просто недоработка старого конструктора запроса.
Если не будет выполняться, значит ограничения 8.1.
Немного не по теме. На сайте ИС выкладываются эти статьи тоже (на примере этой статьи http://infostart.ru/public/306903/kusry-po-1c.ru), и в публикации есть ссылка на текущий материал: “Другие полезные статьи и видео смотрите на нашем сайте: http://Курсы-по-1С.рф“. Только вот ссылка неправильная – указана ссылка статьи на ИС (и здесь рекурсия! ))) ). Уже вторая статья с рекурсивной ссылкой. Первая была http://infostart.ru/public/307547/. Остальные может тоже надо подкорректировать?
Пишу здесь так как на ИС автор запретил комменты.
Добрый день!
Спасибо. Всё уже исправлено.
Добрый день.
Скажите пожалуйста:
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.
При добавлении нового измерения в конец списка, данный индекс просто не смог бы использоваться, т.к. наше поле стоит не на первом месте.