gifts2017

Оптимизация запросов 1С:Предприятие – от теории к практике

Опубликовал Евгений Моисеенко (bpc222) в раздел Программирование - Практика программирования

Я давно отметил достаточно серьезный интерес сообщества к вопросам оптимизации и повышения производительности высоконагруженных систем. Общаясь с различными специалистами, обслуживающими малые и большие предприятия (торговые и производственные), я пришел к выводу, что вопросы повышения производительности интересны почти всем, вне зависимости от того, какую конфигурацию они обслуживают – большую или маленькую, измененную или без значительных изменений. В связи с этим я пришел к выводу, что стоит посвятить данный материал вопросам оптимизации запросов, поскольку именно запросы являются основным инструментом извлечения данных из системы.

Основные причины неоптимальной работы запросов

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

  • Первая причина – это разработчик. Сразу скажу, что я сам являюсь разработчиком, но это – мое мнение, может быть, у вас сложилось другое.
  • Еще бывают случаи, когда архитектор решения не предусмотрел подходящий регистр, позволяющий получать аналитическую информацию с большей эффективностью для системы.
  • Также нередко происходят ситуации, когда директор предприятия сокращает бюджет, или распределяет его, размазывая по сроку предоставления таким образом, что собрать систему к моменту ее запуска просто не на что.
  • Или иногда ответственные за проект лица принимают решение о запуске системы без предварительной опытной эксплуатации.
  • И еще бывают случаи, когда администратор базы данных просто не имеет представления о том, что необходимо для стабильной работы СУБД.

Эти факторы могут прямо или косвенно повлиять на то, что запросы будут работать неоптимально. Но я буду, в основном, говорить именно про текст запроса, потому что это – самая основная и первая причина неоптимальной работы запросов.

Чем «портят» запросы программисты?

Как это выглядит? Я вам из Барнаула привез самолетик, к которому спроектировал довольно большое крыло в надежде на то, что он так лучше полетит. Но происходит примерно так: самолетик падает плашмя вниз. То же самое с запросами. Разработчик, предполагая использование в запросе каких-то навороченных решений, может воспроизвести такой текст запроса, который «не полетит», поскольку его результат становится тяжелым, как и в случае с этим самолетиком.

Итак, сконцентрируемся на тексте запросов. Что может «утяжелить» запрос? Здесь перечислены основные причины такого «утяжеления», про все это подробно написано на всем известном сервисе http://its.1c.ru/, очень доступном, открытом – вы можете принимать это во внимание.

Безусловно, самым часто встречающимся случаем является неиспользование отборов внутри виртуальных таблиц. Я думаю, что нет ни одного разработчика 1С, кто не знает этого правила, однако в реальных решениях такие ошибки все-таки встречаются довольно часто. Причем их могут совершать даже разработчики с многолетним стажем

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

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

Чуть реже, но тоже в значительной мере встречаются моменты, когда нет подходящего индекса.

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

Методика оптимизации запросов

Сама методика оптимизации запросов описана в открытых источниках (диск ИТС). Ничего нового (или почти ничего нового) я вам не скажу. Но важно научиться применять эту методику, вовремя вспомнить о ней, увидеть неоптимальный код в своем решении – это дано не каждому. Несмотря на это, сделать правильные выводы из готовых рекомендаций намного проще, чем увидеть проблему в своем коде самому.

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

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

Итак, в первом примере вы видите, что идет обращение к виртуальной таблице остатков. При этом в первом случае для отбора по товарам документа используется вложенный запрос, а во втором случае – вложенный запрос с использованием временной таблицы, что, по идее, должно предоставить нам возможности для более эффективной работы с данными. Однако этого не происходит.

Справа показаны замеры скорости исполнения, сделанные с помощью всем известной обработки с диска ИТС «Консоль запросов для управляемого приложения 8.3», позволяющей видеть затраты на исполнение запроса для сервера MSSQL. Как вы видите, в данном случае использование временной таблицы не привело к более быстрому результату, поскольку затраты на ее создание оказались более существенными, чем работа с вложенным запросом. Такое очень часто встречается на практике.

Другой случай, который также может привести к неожиданному результату, – это затраты на индексирование временных таблиц. Нередко при индексации временной таблицы, в которую помещается небольшое количество записей, мы не получаем прироста производительности, а наоборот, затрачиваем время на то, чтобы проиндексировать временную таблицу (несем накладные расходы и не получаем при этом никакого ускорения).

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

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

Например, на слайде показан текст запроса, демонстрирующий обращение к полю составного типа «Регистратор» (а именно к его вложенному свойству «Контроль цен»). Здесь видно, что запрос, в конечном счете, не производит обращения к документу «Реализация товаров и услуг», поскольку свойства «Контроль цен» у этого вида документов нет, и, соответственно,сама платформа не генерирует никаких дополнительных левых соединений. А на практике я встречаю чрезмерные усилия разработчиков (не всех, конечно), направленные на то, чтобы исключить лишние источники данных, хотя они в этом случае и так не будут подключены. И это тоже нередко встречающаяся ситуация.

Фильтрация внутри виртуальных таблиц

Еще одна интересная ситуация – это фильтрация внутри виртуальных таблиц.

Как вы понимаете, использование параметра «Отбор по периоду» при получении актуальных данных не требуется. И если произвести получение остатков на актуальную отметку времени без использования этого параметра, мы получим данные с использованием одного источника – физической таблицы итогов. А при указании в этом значении любого подходящего периода (например, будущего времени сегодняшнего дня, который тоже будет указывать на необходимость получения актуальных остатков), у нас уже произойдет получение данных из двух источников – таблицы итогов и таблицы движений. И в результате мы получим значительное снижение производительности запроса. Такая ситуация тоже очень часто встречается.

Использование критериев отбора для получения некластеризованного индекса

Однако в возможностях, которые предоставлены нам платформой, есть и неявные способы повышения производительности за счет «неявного индексирования» (так я назвал этот способ). Например, если нам необходимо осуществить выборку данных по табличной части документа с отбором по номенклатуре, то в случае, если этот реквизит табличной части не проиндексирован, мы можем получить низкопроизводительный запрос. Поэтому, если мы хотим, чтобы у нас был построен индекс для эффективной выборки данных с отбором по этому полю, мы можем воспользоваться способом «неявного индексирования» – добавить критерий отбора. При этом мы получим необходимый индекс для такой выборки, не усложняя себе работу по обновлению текущей конфигурации в дальнейшем.

И опять же, эта теория описана на ИТС.

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

Неправильное использование критерия отбора

Однако при использовании критерия отбора для выборки определенных номенклатурных позиций из документов различных видов мы не получим той эффективности, которую можем получить, используя аналогичное по функциональности, но более объемное по написанию объединение запросов. Это связано с тем, что в первом случае физически на стороне СУБД будет сформирован гораздо более сложный запрос, чем во втором случае. Как вы здесь видите, запрос с объединением работает более эффективно, чем использование критерия отбора.

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

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

Как правильно оптимизировать запросы?

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

  • Самое главное, на что хотелось бы обратить внимание, это то, что прежде чем приступить к оптимизации того или иного запроса, необходимо выяснить, как часто он выполняется и нужно ли его вообще оптимизировать. Если запрос исполняется один раз в три года, то смысл в такой оптимизации отпадает.
  • Также я хочу отметить самую распространенную, на мой взгляд, причину неоптимальной работы запросов – это получение «лишних» данных. Иногда достаточно всего лишь «отсечь» ненужные источники, чтобы запрос «полетел». Отбросить лишнее, на мой взгляд, является самым первым, что вам необходимо выполнить. Соответственно, я отметил это в начальных пунктах данного порядка оптимизации запросов.
  • Кроме этого, очень важно правильно оценить:
    • Стоимость исполнения запроса,
    • Затраты ввода/вывода,
    • А также затраты процессора на исполнение данной операции на стороне СУБД.

Для этого у нас есть специальная обработка «Консоль запросов для управляемого приложения 8.3», позволяющая при использовании сервера MS SQL визуально увидеть план запроса, затраты, понесенные на его исполнение, и пр.

  • Ну и, безусловно, не забывайте оптимизировать систему в тех же условиях, в которых она и работает.

Продукт для интерактивного изучения методов оптимизации запросов

Как вы видите, вся статья связана с демонстрацией практических результатов, которые я получил в процессе переписывания неоптимальных запросов. Но как научиться оптимизировать запросы, если нет подходящих инструментов? Размышляя над этим, мы решили создать продукт«Интерактивное изучение методов оптимизации запросов».

Это решение выложено на Инфостарте, есть даже бесплатный вариант, вы можете его использовать и получить эти навыки.

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

После проверки вашего решения (для этого есть специальная кнопка «Проверка») вы получаете оценку исполнения вашего запроса (производится сравнение времени исполнения вашего запроса и эталонного, текст которого вам изначально недоступен). Если ваши затраты меньше или равны затратам на исполнение эталонного запроса, ваш запрос является эффективно оптимизированным.

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

  • Представлены ссылки на краткие рекомендации методистов;
  • На материалы сервиса «Статьи ИТС»;
  • И, безусловно, дана сама инструкция, которая содержит текст эталонного запроса. Вы можете использовать этот эталонный запрос, пройти задание и увидеть, в чем же была разница между вашим запросом и оптимизированным.

В процессе прохождения заданий система строит статистику. Это – такой дополнительный бонус, который мотивирует людей к более эффективной работе с данным решением. Если вы используете подсказку, то количество баллов, начисляемое вам за решение задания, уменьшается. Но пока вы не решите текущего задания, перейти к следующему невозможно. Это практика всех наших образовательных программ.

Решение опубликовано на сайте Инфостарт – http://infostart.ru/public/374023/ .

Архитектура корпоративной системы интерактивного обучения

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

Порядок работы с сервером интерактивного обучения следующий:

  • Организатор обучения направляет учащимся приглашения к исполнению заданий или прохождению аттестации.
  • Руководитель подразделений, используя интернет-браузер или прямое подключение к информационной базе сервера интерактивного обучения, отслеживает результат. Для предоставления веб-страницы результата используется http-сервис.
  • Учащиеся получают доступ к заданиям посредством запуска обработки «Консоль изучения программ». Ее технология основана на использовании веб-сервисов – она очень легкая, не требующая каких-то мега-соединений, ресурсов и прочего. На нашем проекте мы видели одновременное использование сервиса количеством человек свыше 1000.

Вот пример той статистики, которую видит руководитель подразделения. Статистика генерируется http-сервисом «Сервер интерактивного обучения».

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

****************

Данная статья написана по итогам доклада, прочитанного на конференции INFOSTART EVENT 2015 CONNECTION 15-17 октября 2015 года.

Приглашаем вас на новую конференцию INFOSTART EVENT 2016 DEVELOPER.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. bulpi bulpi (bulpi) 07.10.16 11:47
Плюс поставил, но :
1)Мало полезной информации, много рекламы
2)Пример на рис.8 вообще не о чем. В комментарии написано "обращение к полям составного типа без типизации" , но пример к типизации не относится.

2. Анатолий Бритько (headMade) 07.10.16 14:00
(1) bulpi,
"обращение к полям составного типа без типизации" там прописано Регистратор.Контрагент
Регистратор - поле составного типа.
Предполагается что прежде чем получать значение "Контрагент" само значение "Регистратор" необходимо было типизировать.
3. Евгений Моисеенко (bpc222) 07.10.16 15:45
4. Евгений Моисеенко (bpc222) 07.10.16 15:46
(1) bulpi,

спасибо. цель прошлогоднего выступления была в том, чтобы продемонстрировать интерактивные инструменты обучения, которые даже запросы могут сравнивать.
5. Yaroslav (maddy) 07.10.16 21:49
В статье описаны интересные практические моменты и недостатки "оптимизации по книжке"
Первая картинка исключительно хороша!
9. Александр Хомяк (logarifm) 09.10.16 10:16
Ну на первом примере очевидно, что ВТ проиграет. Потому как идет вставка и заполнение ВТ. ВТ - это не панацея и ее ненужно пихать где только вздумается. Это ест-но, что документ мгновенно будет найден по ссылке из базы, чем произойде вначале го поиск, наполнение ВТ.

В даном простом случае ВТ - избыточное решение. Но вот если у Вас к примеру 10 виртуальных таблиц, вот здесь уже надо сравнивать по-другому.

Автор нельзя так рвать из контекста .... ВТ надо применять, когда это действительно необходимо!!!

Итак, в первом примере вы видите, что идет обращение к виртуальной таблице остатков. При этом в первом случае для отбора по товарам документа используется вложенный запрос, а во втором случае – вложенный запрос с использованием временной таблицы, что, по идее, должно предоставить нам возможности для более эффективной работы с данными. Однако этого не происходит.


Извените, но это полнейшая белеберда. Это равносльно тому, что вначале получить Справочник Номенклатура и заполнить его в ВТ и сделать выборку по ВТ , и сказать - ну я ожидал прирост ведь я использую ВТ.... Бред. Автору рекомендую выкладывать более сложные варианты, то что предложенно абсолютно не правильный пример сравнения!!!

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

Ндао назвать - где не стоит применять временной таблицы или что-то тип такого... Или ВТ это не панацея оптимизации в простых запросах!!!!

Именно надо подчеркнуть, что в примере расматриваются простые запросы.
VladimirL; mindcannon; Gilev.Vyacheslav; Madsos; sigmov; +5 Ответить 1
11. Vladimir K (KroVladS) 09.10.16 20:34
(0) Вы осознано вкладывали тайный смысл в первую картинку?
Или "так получилось"?
madonov; savchenkodenis; vasiliy_b; Drizer2000; sergelemon; dj_serega; Westbound; tehas; ardarik; ABudnikov; yurii_host; +11 Ответить 2
13. Евгений Моисеенко (bpc222) 10.10.16 08:23
(9) logarifm,

Александр, все просто... у всего, что я делаю есть цель, смысл, намерения. Посмотрите предыдущие посты :). В них предлагаю партнерам вести работу на своем поле и при наличии предложений направлять их с позиции взаимного уважения и доверия.

И так, отвечаю вам кратко. Эта статья имеет свое позиционирование, свою целевую аудиторию и т.д. Моя задача была примерно следующей - сделать материал для большинства людей, для которых вопросы оптимизации являются новыми, не раскрытыми. Именно им может быть интересно обучение. Если Вам что-то "очевидно", то для большинства, как показывает практика, это так: "да? а почему? а как надо?".

Так что не вырываю я из контекста. За рекомендацию спасибо, только куда с ней :)))
14. Евгений Моисеенко (bpc222) 10.10.16 08:24
(11) KroVladS,

:) я не специально
15. Nikita Leleko (sigmov) 10.10.16 08:52
1. Сначала было сказано про причины возникновения (ну 4ка)
2. Потом были разобраны несколько примеров (на 3ку) с выводом что все очень гибко и использовать нужно когда-так, а когда-не так
3. Ну и наконец "Покупайте наши помидоры"

Не гуд....
16. Евгений Моисеенко (bpc222) 10.10.16 09:51
(15) sigmov,

так не покупайте :)
Статья знакомит с технологией и, заметьте, не предлагает никому ничего покупать.
17. Саша Карбачин (HardBall) 10.10.16 10:17
"П"
"А"
"Д"
"Л"
"А"
Только я заметил на первом рисунке?
18. pirm2 (pirm2) 10.10.16 12:39
19. Котэ Пруидзе (kote) 10.10.16 13:49
2 более-менее ценных подхода - тут, но способы предложены не корректные, ИМХО:
- Ограничение источников. Но сделано это тут неправильно. "Выразить" - это неправильный подход. Нужно использовать для этого условие с "Ссылка"..
- Индексы. Но на практике - это связано с использованием ВременныхТаблиц. А Вы их заругали за зря. Совет простой - поля ВременныхТаблиц, по которым будете их а) объединять с другими таблицами б) использовать для фильтров (в части условий запроса - после ГДЕ) полезно проиндексировать..

История с избеганием ИЛИ, создавая объединения - вопрос неоднозначный.. Сложную логику таким образом очень сложно реализовать. Читабельность запроса и проверка его соответсвия ТЗ - практически станет нереально проверить.. Выигрыш же будет не особо большим, чтоб пожертвовать указанными вещами.. В общем, это скорее антипаттерн, чем хороший совет.

На практике же наибольший выигрыш можно получить, например, отказавшись от использования выражения:
"Подразделение.Ссылка В ИЕРАРХИИ (&Подразделение)".. не зря аналога в SQL не существует.. ой, не зря.

Последний раз я получил ускорение порядка в ~30 раз (3000%) убрав это безобразие из запроса, предварительно отобрав то, что должно быть в иерархии во временную таблицу с индексированием (на всякий случай) и потом используя её таким макаром:

"Подразделение.Ссылка В (Выбрать ВТ_Подразделения.Ссылка ИЗ ВТ_Подразделения КАК ВТ_Подразделения)"..

Таким образом, ваш совет - не использовать вложенные запросы в условиях запроса и в соединениях - тоже скорее вреден, чем полезен. Хотя - всё хорошо в меру, естественно..

Слайды красивые.
20. Сергей Беликов (HAMMER_59) 10.10.16 13:54
"Безусловно, самым часто встречающимся случаем является неиспользование отборов внутри виртуальных таблиц. Я думаю, что нет ни одного разработчика 1С, кто не знает этого правила, однако в реальных решениях такие ошибки все-таки встречаются довольно часто. Причем их могут совершать даже разработчики с многолетним стажем."

Я вижу обратное - многие пытаются максимальное количество условий прописать в виртуальных таблицах.
Подозреваю, мало кто задается вопросом: "А как же потом SQL будет отбирать данные?".
Так вот, если указать много отборов - по-разному будет отбирать. И это очень прекрасно. По одному и тому же запросу, в зависимости от входных данных, будет построен различный план запроса. В итоге на выходе имеем запрос, который в зависимости от ситуации, будет выполнятся очень по разному.

ИМХО нужно стремится, чтобы максимально эффективно использовались индексы. А как SQL заставить использовать конкретные индексы, вот об этом в статье ни слова.
Да и про сами индексы в статье ни слова, все ведь и так про них всё знают.

21. Руслан Хитров (Sheff) 10.10.16 17:16
24. Евгений Моисеенко (bpc222) 11.10.16 12:31
(20) HAMMER_59,

"попасть в индекс" - важная задача, конечно же...

Сделал поиск по статье, слово "индекс" встречается около 20 раз...
Про конкретный индекс представлено решение с использованием критерия отбора...
25. Евгений Моисеенко (bpc222) 11.10.16 12:44
(19) kote,

...Но на практике - это связано с использованием ВременныхТаблиц. А Вы их заругали за зря...
...В общем, это скорее антипаттерн, чем хороший совет...
...ваш совет - не использовать вложенные запросы в условиях запроса и в соединениях - тоже скорее вреден, чем полезен...


Я удивлен указанием вами в обратной связи того, что в статье советуется тот или иной метод... :)
Заметьте, здесь рассмотрены примеры с указанием того, какие достигнуты результаты, используя тот или иной подход выборки/изменения структуры...
Здесь нет симпатий к одному методу или другому, я не советую использовать или не использовать одно или другое...

В любом случае, ваши указания будут, полагаю, ценными для сообщества.
26. Елена Кушнир (kkkelena1963) 17.10.16 06:23
А я хочу сказать спасибо автору даже за то, что есть над чем подумать и обсудить.