Оптимизация запросов 1С посредством индексации временных таблиц. Миф? Тестируем, смотрим, считаем

13.04.20

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

Появилось свободное время, решил проверить на работе индексацию таблиц. Решил поделиться с Вами результатами исследования. Давайте порассуждаем на эту тему? Часто ли вы пользуетесь индексацией в запросах? Платформа 8.3.16.1224

ВВЕДЕНИЕ

Для разностороннего исследования взял 5 парных (с индексацией и без) примеров для замера. Каждый пример сводится к созданию трех временных таблиц (чтобы не пропасть с выводом данных навечно в результирующую таблицу). Первые две временные таблицы получают фиксированные наборы данных, третья производит их соединение. По итогу замеров были сложены временные показатели для получения результата в виде прироста секунд. В целом весь эксперимент делится на две части: связь двух физических таблиц (НовыйШаблон1) и связь физической с виртуальной таблицей (НовыйШаблон2).

 
 НовыйШаблон1
 
 НовыйШаблон2

 

Далее будет указываться какой шаблон запроса в примере используется и с какими модификациями. 

 

Физические таблицы: Товары заказов клиентов не индексируются платформой по сериям, товары реализаций индексируются по сериям но не индексируются по полю "Заказ клиента".

НАЧНЕМ!

Пример первый НовыйШаблон1

Добавлено ограничение на количество выборки записей в 800 000 элементов и использовано не левое, а полное соединение. При первом "прогоне" запроса SQL не провел внутреннее кэширование записей, а повторное выполнение этого же запроса было намного быстрее. Для статистики эти данные также были зафиксированы. Как итог получили следующие данные:
 

 
 Статистика 1

 

Пример второй НовыйШаблон1

Изменено левое соединение на те же 800 000 строк. 

 
 Статистика 2

 

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

Пример третий НовыйШаблон1

Используется Левое соединение на 400 000 строк

 
 Статистика 3

 

Пример четвертый НовыйШаблон1

Левое соединение на микронаборе данных в 10 000 строк, чтобы понимать скорость выполнения построения индекса.

 
 Статистика 4

 

Пример пятый НовыйШаблон2

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

 
 Статистика 5

 

Итоги

Как итог проделанного тестирования сформировалась таблица с записями, пригодная для анализа:

 
 Таблица для анализа

 

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

 

В чем я мог ошибиться? Что мог понять неправильно, что неправильно сделал? Буду рад ответу на эти вопросы в комментариях!

Статья на сайте 1с: https://its.1c.ru/db/metod8dev#content:5842:hdoc

Также по ходу эксперимента был отснят видеоряд:

индексы запросы sql оптимизация индексация ВиртуальныеТаблицы ФизическиеТаблицы ВременныеТаблицы

См. также

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

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

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

13.03.2024    2380    spyke    25    

38

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

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

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

1 стартмани

15.02.2024    7353    149    ZAOSTG    66    

95

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

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

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

09.01.2024    5765    doom2good    48    

63

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

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

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

20.11.2023    8586    ivanov660    6    

75

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

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

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

15.11.2023    4999    a.doroshkevich    20    

72

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

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

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

11.10.2023    15958    skovpin_sa    14    

98

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

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

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

05.08.2023    4974    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. user612295_death4321 05.04.20 08:55 Сейчас в теме
Мне кажется индексирование это прямо уж какая точечная штука.

У меня много было оптимизаций, когда вся оптимизация заключалась в отключении индексирования на таблицах 19 млн записей. Вообще на мой взгляд там бы запрос целиком переписать, но как всегда времени нет ля-ля тополя, а как быстрое решение с отключением индексирования этого давало эффект прироста выполнения запроса порядка в котором получаемый результат снижался с 60 сек до 15+.
Danil.Potapov; +1 Ответить
2. feva 513 06.04.20 18:34 Сейчас в теме
(1)Да, когда-то ходил по собеседованиям и получил отказ из-за того, что доказывал архитектору неоптимальность его методов...На что получил ссылку к статье от разработчиков 1с по оптимизации
8. skalex 10.04.20 11:16 Сейчас в теме
(2)
У меня была обратная ситуация. Я получил отказ на собеседовании, потому что доказывал архитектору, что его методы не оптимальны и сам ссылался на статью фирмы 1С по оптимизации запросов. Не знал я тогда, как я сильно заблуждался!
Чтобы раз и на всегда разобраться с этим вопросом, рекомендую пройти онлайн курс Виктора Богачева по подготовке к экзамену на 1С Эксперта (это не реклама!). Все сразу встанет на свои места. Самостоятельное копание в теме оптимизации приведет к тому, что можно нахвататься чужих заблуждений. А самое главное потратите впустую кучу драгоценного времени. Поверьте, моему горькому опыту.
programmer_87; +1 Ответить
3. soft_wind 06.04.20 21:44 Сейчас в теме
есть следующее соображение.
сама суть индексирования в чем? - вы тут же ответите что бы быстро что-то найти!
... и это только половина задачи
основная цель индексирования (применительно к реквизитам как нас учили) когда что-то БЫСТРО и ЧАСТО надо искать!!!
суть в ЧАСТО.

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

Предложение: попробуйте поэксперементировать(замерить производительность), когда временная таблица с индексацией и без, используется в запросе 10-20 (n?) раз?

При каком числе обращений к ВремТаб становится оптимальнее использовать Индексацию?
Рамзес; Bassgood; feva; +3 Ответить
4. feva 513 07.04.20 08:07 Сейчас в теме
(3) Интересная мысль! Нужно будет сделать на досуге! Спасибо
14. SeiOkami 3418 15.04.20 22:44 Сейчас в теме
(4) обязательно выложите продолжение. Очень интересно
13. sevushka 302 13.04.20 11:29 Сейчас в теме
Не увидел, в т.ч. на видео.
1. Сброс кэша перед каждым выполнением. Если данные есть в кэше, то на индекс реально плевать, это и так понятно.
2. План запроса, где видно хотя бы table scan, index scan, index seek и пр (logical reads, cpu cost).

ну и соглашусь с (3)

а вообще - brentozar.com, более "разжеванных" примеров сложно найти.
15. dmurk 14.09.20 02:23 Сейчас в теме
(3) Столкнулся при размещении temdb на накопителе Intel Optane - индекс временной таблицы строится быстрее на порядок, вследствие чего индексирование ВТ приобретает смысл в большинстве случаев, в том числе единичные применения
5. skalex 10.04.20 10:18 Сейчас в теме
Здравствуйте.
В вашей статье ничего не сказано про поля физических таблиц. По полю Ссылка индекс есть всегда. Если поля Серия и ЗаказКлиента проиндексированы средствами платформы, то ваше дополнительное индексирование в запросе чревато только дополнительной нагрузкой на создание индексов, которые и так уже есть.
feva; aexeel; +2 Ответить
11. feva 513 13.04.20 08:21 Сейчас в теме
(5)Добрый день!
Первая ФТ: Серия - не индексируется
Вторая ФТ: Серия индексируется, заказ клиента - не индексируется
6. skalex 10.04.20 10:26 Сейчас в теме
В случае, если поля проиндексированы средствами платформы, лишней нагрузкой будет создание временных таблиц. Т.к. временная таблица будет создана в TempDB, а это операция с диском. Соединение двух физических таблиц, в этом случае, будет работать быстрее. Да! И я помню про рекомендации фирмы 1С – все выносить во временные таблицы и индексировать. Но необходимо все проверять на практике.
Рамзес; aexeel; acanta; +3 Ответить
12. feva 513 13.04.20 08:27 Сейчас в теме
(6) Да, не предусмотрел это сразу
наш случай фити/фифти)
7. skalex 10.04.20 10:32 Сейчас в теме
Еще один момент. В вашей статье нет ни слова о плане запроса. С вероятностью в 99% планы запросов для с индексированием и без, будут одинаковыми! Сталкивался с этим в 90% своих запросов. Оптимизатор запросов SQL – дико умная штука. Кроме того, сама 1С научилась качественно тюнинговать свои запросы, перед тем, как отдать их SQL-серверу. К сожалению, не имея у себя вашей тестовой базы, я не смогу получить планы запросов. А было бы интересно на них глянуть.
SerVer1C; alest; aexeel; +3 Ответить
9. v25i85 1 12.04.20 01:16 Сейчас в теме
Плиз дайте тексты запросов с индексами. Какие поля в индексе?
10. feva 513 13.04.20 08:18 Сейчас в теме
(9) Добрый день! К сожалению запросы писал в реальном времени и не сохранил. На видео они все видны
Оставьте свое сообщение