Анти-оптимизация: как мы ускорили запрос в 4 раза, сделав его неоптимальным

02.07.19

Разработка - Запросы

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

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

Более 2-х лет я занимаюсь вопросами повышения производительности 1С за счет оптимизации программного кода. Одна из частых задач (особенно при борьбе с ожиданиями на блокировках) - это повышение режима совместимости конфигурации 1С.

Описание проблемы

Один из клиентов обратился к нам для перевода конфигурации из режима совместимости с 8.1 в режим "без совместимости" (версия платформы 8.2.19.102).

Код конфигурации был адаптирован под 8.2, режим совместимости благополучно изменен.

Примечание: версия СУБД в процессе повышения режима совместимости не изменялась.

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

Поиск выполнялся следующим образом: пользователь в специальном окне вводил строку поиска и нажимал Enter. На 8.1 поиск выполнялся около 5 секунд. При переходе на 8.2 стал выполняться 20 секунд (в 4 раза дольше). Нужно было ускорить поиск хотя бы до старых значений в 5 секунд.

Анализ и решение

При поиске номенклатуры выполнялся вот такой запрос:

С помощью SQL Server Profiler были получены тексты запросов и планы выполнения для платформы 8.1 и для платформы 8.2.

Текст запроса в терминах SQL (платформа 8.1):

План запроса (платформа 8.1):

Таким образом, при выполнении запроса на 8.1 происходил полный скан таблицы справочника. Любой человек, который когда-нибудь занимался оптимизацией запросов скажет вам - "скан - это плохо", и будет прав. Но дальше увидим, что на самом деле это не всегда так :)

Текст запроса в терминах SQL (платформа 8.2):

План запроса (платформа 8.2):

Несмотря на то, что сканов в этом плане запроса не было, запрос выполнялся в 4 раза дольше (20 сек.), чем этот же самый запрос на платформе 8.1 (5 сек.).

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

Тогда было принято решение намеренно "ухудшить" запрос, чтобы повлиять на выбор плана запроса оптимизатором СУБД, и получить скан таблицы справочника (как было в 8.1).

Для этого в текст запроса были добавлены выражения над полями условий, запрос стал выглядеть так:

План запроса стал следующим:

То есть, мы получили такой же план запроса, который был при режиме совместимости с 8.1 - полный скан таблицы справочника.

При этом запрос ускорился в 4 раза: с 20 сек. до 5 сек.

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

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

Если вам понравилась статья, поставьте "+" :)

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

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 10400 руб.

02.09.2020    121608    670    389    

711

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5740    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2000    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6279    30    mkalimulin    25    

49

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

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

14.12.2023    1742    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

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

06.12.2023    5386    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

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

11.10.2023    16166    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
118. Dach 372 12.07.19 10:03 Сейчас в теме
(116)

Собственно, вот похожие ситуации описаны:

http://qaru.site/questions/690877/how-can-like-seek-on-an-index

https://social.msdn.microsoft.com/Forums/sqlserver/ru-RU/232d0f1c-9658-463f-afae-ebbcbcae05c4/how-is-it-possible-to-use-index-seek-for-like-searchstring-case

В обоих случаях пришли к выводу, что реальную работу выполняет Seek Predicate
119. ildarovich 7850 12.07.19 14:29 Сейчас в теме
(118) Спасибо за ссылки, но там я не увидел ответа на наш вопрос ускорения при использовании второго плана. У них и в том и другом случае был индекс, просто в первом варианте план показывал скан, а во втором поиск, что и озадачило авторов вопросов. Говорится о том, что поиск ведется по трем предикатам. Двум оптимизированным: "от", "до" и одному неоптимизированному "включает подстроку". Но в случае, когда шаблон начинается с %, первые предикаты вместе возвращают все строки! К которым затем применяются третий предикат. А в случае опции RECOMPILE второй план превращается в первый, так как вид строки поиска при формировании плана уже определен.

(117)
Мне кажется, просто этот оператор для like работает несколько иначе, чем просто перебор строк и разбор каждой на предмет соответствия условию. А вот как именно он это делает - это я уже не знаю
- моя догадка была в том, что поиск подстроки в относительно небольшом блоке памяти всех выбранных строк или в закэшированном файле индекса быстрее, чем работа со строками "по одной"
67. Dach 372 03.07.19 13:29 Сейчас в теме
(64)

все же написано в документации

https://docs.microsoft.com/ru-ru/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

а про шаблоны - многие компании такое используют

Лично видел! В Москве есть весьма такая неплохая сеть магазинов автозапчастей: АвтоРусь (не сочтите за рекламу).
Там сотрудник для поиска конкретной запчасти по наименованию в поле поиска вбивает не просто абы как, а по шаблону, что-то типа: "патруб*Д10*Bosh*и т.д."

Каждый раз, кстати, как захожу к ним - всегда подглядываю, как у них 1С меняется - очень нравится, как все внедрено, формы красивые-удобные, кнопки там, прям пожать руку хочется разрабам
75. AlX0id 03.07.19 14:44 Сейчас в теме
(67)
"патруб*Д10*Bosh*и т.д."

Как раз таки подобная строка очень хорошо укладывается в поиск по индексу.. А вот кабы было "*патруб*Д10*Bosh*и т.д." - было бы интереснее.
Хотя конкретно для номенклатуры поиск правильнее организовать по свойствам, значения фильтров которых выбирать на форме. Ну или брать из строки "патруб*Д10*Bosh*и т.д.", разбив ее по звездочкам, например.
65. CyberCerber 852 03.07.19 13:21 Сейчас в теме
(61) Я тоже сразу об этом подумал, но не написал, т.к. постеснялся. :-) Подумал, да, решение очевидное, но я в оптимизации и планах запроса не очень силен, наверное, не подходит, раз о нем никто не пишет.
83. capitan 2466 03.07.19 16:32 Сейчас в теме
(61)В (15) предложили более приятное решение чем составной реквизит
100% оно за 1 сек отработает
84. Dach 372 03.07.19 16:45 Сейчас в теме
(83) не такое уж оно и приятное. Сколько времени будет такое РЗ выполняться? Если номенклатуры много, надо каждую разбить на блоки, перезаполнить по ней РС и т.д... Плюс есть временной лаг "неактуальности" данных, новая номенклатура в базу попала, а блоки поиска в РС по ней еще не заполнены - значит надо городить подписки на события, обработчики вешать на проверку изменения наименования и т.д. и т.п.

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

Нет у этой задачи однозначно хороших решений, придется чем-то жертвовать
85. capitan 2466 03.07.19 17:03 Сейчас в теме
(84)Новая и измененная номенклатура легко вычисляется - при записи пишете ее в этот же регистр
И вуаля - небольшое получится РЗ
Можете ваш составной реквизит сделать измерением регистра и проиндексировать
88. Diversus 2306 03.07.19 17:16 Сейчас в теме
(84) Не обязательно перезаполнять все (это можно делать только при первом запуске).

А в общем алгоритм с оптимизациями примерно такой:
1) Создаем план обмена, который регистрирует изменения в номенклатуре.
2) Обработка регламентным заданием только той номенклатуры, которая была изменена.
3) После обработки убираем регистрацию с обработанной номенклатуры.
4) Регламентное, которое отрабатывает раз в минуту прекрасно будет справляться и не нагружать сервер.

В регистр пишем только начала слов начиная с 3-букв
"Туфли женские" трансформируются в регистре в:
туф
туфл
туфли
жен
женс
женск
женски
женские

Предположим, в строке поиска введено: "туф жен"
Разбиваем строку на слова по пробелам и загоняем результат в массив.

Ну а дальше запрос к регистру, без использования оператора ПОДОБНО.

Состав регистра с измерениями:
1. Подстрока (Индексирование включено);
2. Номенклатура;

Поиск должно работать быстро, места занимать не так уж и много будет, кстати, и все очень даже актуально (в минуту будет не много измененной номенклатуры... наверное...).

В очередной раз скажу:
1) Это алгоритм, который может потребовать дополнительных оптимизаций.
2) Велосипед придумал не я и я его не пробовал как оно работает в боевых условиях.
3) Да, я открыл для себя ПДД/ElasticSearch и т.п. и это осознаю :)
90. Dach 372 03.07.19 17:44 Сейчас в теме
(88)

убедили

Возьмем на заметку
91. Diversus 2306 03.07.19 17:59 Сейчас в теме
(90) А вообще, было бы интересно сравнить, что из всех этих методов (правильных и не очень) на реально большой базе будет быстрее.
92. acanta 03.07.19 20:17 Сейчас в теме
(88) в вашем случае условие будет и?
62. AlX0id 03.07.19 13:03 Сейчас в теме
Любой человек, который когда-нибудь занимался оптимизацией запросов скажет вам - "скан - это плохо", и будет прав.

Ни на одном из курсов, кстати, такое и не утверждается. Насчет книги не помню.
74. vbirin 21 03.07.19 14:44 Сейчас в теме
Добрый день.
А можно ли увидеть"Текст запроса в терминах SQL (платформа 8.2)", получившийся после добавления в запрос 1С 'ПОДОБНО'?
76. shard 279 03.07.19 14:55 Сейчас в теме
для любопытствующих, на постгре 9.6 план выполнения в обоих случаях
"seq scan on Справочник.Номенклатура t1  (cost=0.00..2206.72 rows=235 width=29) (actual time=2.254..82.594 rows=98 loops=1)
  filter: ((not ПометкаУдаления) and ЭтоГруппа and (ОбластьДанныхОсновныеДанные = '0'::numeric) and ((Наименование ~~ '%абыр%'::mvarchar) or (Артикул ~~ '%абыр%'::mvarchar)))
  rows removed by filter: 26896
planning time: 0.154 ms
execution time: 82.610 ms
",rowsaffected=98

справочник порядка 25 тыс товаров
82. Slava_prog 03.07.19 15:51 Сейчас в теме
Очень интересно бы получить ответы на следующие вопросы:

1) Зачем клиенту понадобилось повышать уровень совместимости (что это дало пользователям/бизнесу) ?
2) Какие проблемы были решены этим переводом ?
107. acanta 04.07.19 14:02 Сейчас в теме
Как минимум может быть
Обувь туфли женские
Или запчасти шарикоподшипник
Поскольку и так понятно, но в общем списке неудобно.
Справочник с ценами поставщиков в подборе по прайсу это хорошо.
С покупателями сложнее, но все это не относится к теме.
109. triviumfan 92 04.07.19 18:02 Сейчас в теме
Не верится. Что-то тут не так. Какой-то подвох! :)
Пробовали выполнять прямо из ssms?
110. gubanoff 63 09.07.19 13:09 Сейчас в теме
(0) в защиту авторов курсов по подготовке экспертов - уважаемый господин Бургомистров так и говорит на своих курсах, что всегда надо проверять результат оптимизации на практике в конкретных условиях и да, рекомендуемые методы не всегда дают требуемый результат.
Это в общем-то и есть адекватная точка зрения на этот вопрос. Не стоит переоценивать методические рекомендации, но и отказываться от них тоже не стоит. Рекомендации будут работать в 80% случаев, поэтому они и стали рекомендациями.
114. fedorovd81 10.07.19 23:39 Сейчас в теме
igordynets, а можно показать текстовый план запроса? Нет ли там seek...where?
115. igordynets 87 11.07.19 11:53 Сейчас в теме
(114) К сожалению, текстовый план запроса не сохранился, и возможности его получить уже нет.
Оставьте свое сообщение