Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

13.03.24

База данных - HighLoad оптимизация

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

Задача. Выбрать все записи из регистра по заказу.

На первый взгляд это тривиальная задача с простым запросом

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

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

 


 

Необходимо избавиться от сканирования (операция Index Scan), а «попасть» в индекс и получить на выходе операцию Index Seek. Т.к. операция поиска в индексе менее затратная по ресурсам и времени.

1)    Определим, какие у нас есть индексы (https://its.1c.ru/db/metod8dev/content/1590/hdoc)

 


 
2) Смотрим структуру регистра

 

 

Для того, чтобы у нас был индекс по измерению, это п3 из таблицы п1, нам необходимо, чтобы измерение было проиндексировано. Можно установить признак индексировать, и тогда задача будет решена, но регистр типовой и, к примеру, нам запрещено снимать с поддержки типовые объекты. Тогда мы снова обращаемся к таблице с индексами и видим, что можно использовать индекс по регистратору, и наша задача сводится к тому, чтобы найти регистраторы по заказу и только потом выбрать эти записи.  

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


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

 


 
Разберем сам план. Первый запрос — это помещение во временную таблицу документов реализации с отбором по нашему заказу.

Видим, что так же используется поиск в индексе. Почему происходит поиск в индексе, если в типовом решении данное поле не индексировано у документа ?

 


 
Снова обращаемся к статье про индексы объектов

 


 
Нам подходит индекс либо реквизит, если оно индексировано, но это не наш случай, либо критерий отбора. Смотрим типовые критерии отбора  


 


И видим, что есть отбор по Заказу клиента --> план запроса будет использовать этот индекс.

Переходим к анализу второго плана запроса. Разобьем его на части.

 


 

В данной итерации происходит перебор строк из нашей временной таблицы запроса 1 и поиск этой записи в индексе. 


 
 


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

На этом этапе мы уже выиграли более чем в два с половиной раза процессорного времени.

При изначальном запросе у нас предполагаемая стоимость ЦП = 0,32, сейчас 0,12 остальными итерациями можно пренебречь в плане запроса, т.к. их стоимость < 0,0001

А зачем нам вторая часть запроса?
 

 

В ней происходит перебор строк нашего соединения, описанного выше, и поиск в кластерном индексе, также поиск происходит не перебором строк, а поиск по индексу. Это необходимо для того, чтобы получить количество ВыручкаИСебестоимостьПродаж.Количество КАК Количество, т.к наш индекс ничего не знает об этом поле, нам это значение необходимо получить из кластерного индекса.

 

 

На очень простом и маленьком примере мы смогли облегчить жизнь процессору СУБД более чем в 2,5 раза.

См. также

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5468    vasilev2015    19    

38

Анализируем SQL сервер глазами 1С-ника

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

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    8039    166    ZAOSTG    69    

98

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    6408    doom2good    48    

64

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    9266    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5291    a.doroshkevich    20    

72

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

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

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

11.10.2023    16490    skovpin_sa    14    

101

Как эффективно настроить autovacuum в Postgres для 1С

HighLoad оптимизация Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    5173    1CUnlimited    5    

51
Комментарии
Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. garlev 13.03.24 18:25 Сейчас в теме
2. Goruch 7 13.03.24 19:10 Сейчас в теме
3. ivanov660 4343 13.03.24 19:52 Сейчас в теме
1. Некоторые картинки с таблицами не читабельные, поправьте.
2. Хороший пример.
Дмитрий74Чел; t278; +2 Ответить
28. Дмитрий74Чел 234 03.04.24 17:36 Сейчас в теме
(3) Картинки конечно вырвиглаз. То огромный шрифт, то под микроскопом не разглядеть.
4. localhost127 13.03.24 20:06 Сейчас в теме
Хорошая статья и лаконичная к тому же, без воды.
5. gzharkoj 504 13.03.24 22:19 Сейчас в теме
Немного позанудствую, раз в заголовке стоит слово оптимизация, то временную таблицу можно не индексировать, там у вас явно не более 1000 записей будет (https://its.1c.ru/db/v8std/content/777/hdoc), да и ставить ее надо первой в запросе, как наименьшую, тогда тем более индексировать не надо, главное, чтобы во второй индекс был, но оптимизатор MS это выправит, что и видно на плане.
Единственный момент, что надо доставать все возможные документы-регистраторы по заказу клиента.
BigB; Sиlьver; +2 Ответить
6. ivanov660 4343 14.03.24 08:43 Сейчас в теме
(5) Порядок использования таблиц обычно планировщик выбирает самостоятельно при формировании плана запроса особенно в MS SQL, поэтому перестановкой таблиц мы вряд ли сможем повлиять на фактический план, в данном случае.
binx; SerVer1C; Shmell; +3 Ответить
19. gzharkoj 504 15.03.24 10:17 Сейчас в теме
(6) В данном конечно, но если соединений больше и в добавок установлены ограничивающие параметры для анализа планировщику на количество соединений, например в postgres это join_collapse_limit, то ситуация становится противоположной, поэтому в целом организовывать запросы лучше в едином ключе с общими рекомендациями, даже не смотря на то, что там всего две таблицы.
20. ivanov660 4343 15.03.24 13:53 Сейчас в теме
(19)
1. Согласно тем же рекомендациям, то формировать запрос с десятком соединений также не следует.
2. В MS SQL я не видел, где можно управлять порядком соединений, если не брать в расчет хуки. А вот в постгрес этим можно поиграться.
3. В общем случае количество строк в таблицах может меняться от случая к случаю, поэтому лучше сюда не лезть и отдать - это все на откуп оптимизатору.
22. gzharkoj 504 15.03.24 14:15 Сейчас в теме
(20)
1. Согласен, для любой реляционной СУБД соединения тяжелые операции
2. В MS SQL не знаю, а в postgres значение join_collapse_limit = 8 по умолчанию, из 10 таблиц две не будут обработаны, если 9 и 10 будет временная таблица не факт, что поменяется порядок.
3. А тут интересный момент если мы знаем пределы, то почему бы и не поставить корректно, а если сомневаемся, то да, как вы и написали.
8. binx 167 14.03.24 10:06 Сейчас в теме
На мой взгляд это смелое допущение что Регистратором по заказу клиента может быть только Реализация. Вдруг регистратором в регистре накопления ВыручкаИСебестоимостьПродаж будет другой регистратор? Если сейчас тесты показывают одинаковый результат, то в какой то момент хозяйственной деятельности предприятия будет заведен документ по регистратору с другим типом. Нужно очень хорошо знать функционал конфигурации, чтобы утверждать что Регистратором по заказу клиента может быть только Реализация и это поведение не изменится в будущем. Но сама идея интересная.
Sиlьver; shard; +2 Ответить
9. booksfill 14.03.24 12:22 Сейчас в теме
(8)
Нужно очень хорошо знать функционал конфигурации, чтобы утверждать что Регистратором по заказу клиента может быть только Реализация и это поведение не изменится в будущем


Провидение будущего, говорят, было доступно только Кассандре, да и ту никто не слушал.
Я тоже могу выступить провидцем: "Ежели сия программа будет использоваться, то в скором времени кто-то, что-то обязательно изменит. Причем этот кто-то никакого понятия о такой "оптимизации" иметь не будет. И ждет нас дальняя дорога прояснения чакр и понимания почему отчет/ы стал врать"

Идея статьи в учебных целях - полезная, тут и напоминание про то, как надо читать план запроса и про то как творчески надо искать возможности.
А вот на практике - фу, так делать.

P.S.
Лучше уж нужный индекс создать самим в СУБД.
Да-да, это ая-яй, низззя.
Да еще индекс при реструктуризации пропадет (разумеется, никто никак не знает как это предотварить - это сложнее доказательства гипотезы Пуанкаре).
12. Bassgood 1428 14.03.24 15:33 Сейчас в теме
(9)
Да еще индекс при реструктуризации пропадет (разумеется, никто никак не знает как это предотварить - это сложнее доказательства гипотезы Пуанкаре).

Пишите скрипт, который проверяет в СУБД наличие нужного индекса для таблицы (созданного Вами ранее), если он отсутствует (например, был "убит" платформой при реструктуризации), то отправляете в СУБД команду на его создание - например, при обновлении ИБ.
Как такой вариант? ;)
14. booksfill 14.03.24 16:29 Сейчас в теме
(12) Тут есть нюанс, насколько помню, в не Enterprise Edition создание индекса приведет к разделяемой блокировке на запись.
В Enterprise версии можно попытаться изобразить что-то типа
cre ate index MyIDX on MyTable (MyColumn) with (on line = on).

В общем, надо тестировать. Или не заморачиваться и включить это в регламент обновления 1С.
7. spyke 62 14.03.24 09:44 Сейчас в теме
(5) По поводу индекса временной таблицы, вы абсолютно правы.
10. s22 19 14.03.24 13:33 Сейчас в теме
Интересно, а если запрос

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

не будет быстрее?
11. binx 167 14.03.24 14:00 Сейчас в теме
(10)
Использований функции
Типзначения( ВыручкаИСебестоимостьПродаж.Регистратор) = Тип(Документ.РеализацияТоваровУслуг)
отключает использование индекса, поэтому индекс по регистратору не будет использоваться получим скан таблицы или индекса, так что нет не будет
13. s22 19 14.03.24 15:50 Сейчас в теме
(11)
отключает использование индекса, поэтому индекс по регистратору не будет использоваться получим скан таблицы или индекса, так что нет не будет

а не пробывали?

только опытом можно ответить на такой вопрос )
16. binx 167 15.03.24 08:01 Сейчас в теме
(13)
На its четко написано, что использование функций отключает использование индекса. Действительно, индекс может использовать только записанные в индекс значения. При использовании функций значения вычисляются соответственно индекс по регистратору просто не может работать, так как в индексе не хранятся вычисленные значения типа регистратора.
18. gzharkoj 504 15.03.24 09:43 Сейчас в теме
(16) Это не вычисляемая функция в понятиях SQL, под капотом, это отдельно колонка-поле, в котором хранится тип, 1С в сам запрос уже подставляет вычисленные данные, а не в процессе исполнения запроса, так это выглядит в postgres:
ГДЕ ТИПЗНАЧЕНИЯ(ОборотныйРегистр.Регистратор) = ТИП(Документ.Документ1) конвертируется в
WHERE (T1._RecorderTRef = '\\000\\000\\0001'::bytea)
Поле это индексируемое и в плане запроса будет так
Index Cond: (t1._recordertref = ....,
но более важно, что это поле низкоселективное, а значит в большинстве случаем пользы будет немного от него.
23. binx 167 15.03.24 20:51 Сейчас в теме
(18)
Спорить не буду, приму к сведению.
Исходя из того что поле низкоселективное, оптимизатор скорее всего не будет использовать низкоселективный индекс, скорее всего все таки индекс не будет использоваться.
15. user1636469 14.03.24 16:42 Сейчас в теме
(11)
Типзначения

где на ИТС про это написано?
21. ivanov660 4343 15.03.24 14:00 Сейчас в теме
(10)Не будет, как уже написали, то селективность крайне низкая.
Например в таблице в 10 млн строк, у вас три документа (допустим равномерно). Поэтому, даже если будет использоваться индекс, то он даст выборку в 3 млн строк. А далее будет скан по всей этой выборке с условием по заказу.
24. s22 19 18.03.24 18:29 Сейчас в теме
(21)
Например в таблице в 10 млн строк, у вас три документа (допустим равномерно). Поэтому, даже если будет использоваться индекс, то он даст выборку в 3 млн строк. А далее будет скан по всей этой выборке с условием по заказу.

Круто )
вот только при разборе строки скл при фулскане проверить второе поле в строке и выкинуть 2/3 строк недочитывая дальше тоже кайф )
в 2х кратный рост вполне можно поверить )
25. ivanov660 4343 18.03.24 19:30 Сейчас в теме
(24)Это все равно медленно будет в сравнении с предложенным вариантом, поэтому все равно не ахти какой вариант.
26. s22 19 19.03.24 12:43 Сейчас в теме
(25) так там ускорение в 2.5 раза... Тут раза в 2-3 )
27. Rasylit 29.03.24 06:56 Сейчас в теме
я правильно понимаю, что эффект оптимизации запроса получен с учётом key lookup ?
потому что
ВыручкаИСебестоимостьПродаж
кластерный индекс начинается с поля Период (в кластерном индексе "на листьях дерева" есть поле Количество)

а соединение таблиц только по регистратору, без учета периода

в ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица
ПО (ВыручкаИСебестоимостьПродаж.Регистратор = ВременнаяТаблица.Ссылка)

Для поля Регистратор есть отдельный некластерный индекс, но в нём нет поля Количество

и в данном случае автору на его данных оптимизатор выдал, что поиск в некластерном индексе с обращением (key lookup) к кластерному будет быстрей
Оставьте свое сообщение