Технологические вопросы крупных внедрений
08.02.2023
К нам обратился клиент с жалобой на скорость расчета потребностей. Клиент торгует запчастями, количество позиций порядка 20 тыс. Расчет потребности считает на несколько месяцев для примерно 1-1,5 тыс. позиций.
Перед тем как производить замеры, проверим, стоит ли признак параллелизм расчетов по ядрам процессора. Если стоит, его надо отключить (см. рис. 1)*.
*мы рекомендуем отключать параллелизм для OLTP-нагрузки (оперативный учет у нас всегда OLTP).
Рис. 1. Параметры SQL server
Далее подключаем отладчик 1C:Предприятие на сервере (как подключать смотри здесь https://its.1c.ru/db/freshpub/content/3549/hdoc).
Итак, все готово для замеров производительности. Запускаем расчет потребностей, расчет строим по продажам за полгода, потребность формируем на два месяца вперед.
Рис 2. Обработка Расчет потребности
Расчет формируется достаточно долго. По секундомеру вышло порядка 12 минут.
Потребности выводятся в виде дерева значений, для каждого дня выводится своя колонка (всего получается 60 колонок на 2 месяца). Получается достаточно внушительное дерево, где порядка 1,5 тысяч строк дефицитных запасов и больше 60 колонок с расчетом потребностей.
Вернемся к оптимизации.
Останавливаем замер производительности. По данным замеров определяем, что основное время тратится на исполнение кода и построение дерева значений.
Дерево потребностей строится следующим способом:
Для оптимизации построения дерева план работы понятен:
Перед тем как начнем оптимизацию, включаем расчет замера производительности через подсистему БСП Оценка производительности. Это даст возможность объективно оценить результаты оптимизации расчета и не сидеть с секундомером.
Так как расчет потребности считается фоновым заданием, расчет потребности встраивает через две процедуры:
Как именно встраивать, смотри здесь: https://its.1c.ru/db/bsp319doc#content:1536:hdoc
Перестроить формирование дерева на схему компоновки было достаточно тривиальной задачей. Дополнительно:
Для вывода дерева значений добавили несколько параметров:
Первый параметр необходим для группировки потребностей в колонках. У него стандартные значения: День, Неделя, Декада, Месяц.
Последние два параметра решили добавить бонусом для простоты расчета потребностей. Параметров стало больше – места для фильтров меньше, поэтому разбили панель настойки на две закладки: Настройки и Фильтры (см. рис. 3).
Рис. 3. Новый интерфейс расчета потребностей
По результатам контрольного замера скорость расчета увеличилась в несколько раз: итоговый расчет выполнился за 59 секунд.
Результаты оптимизации впечатлили, поэтому решили добавить новый запрос для точного расчета потребностей. Решили выполнить задачу расчета средних продаж, с учетом остатков товаров. Основная идея в том, чтобы исключить из расчета средних продаж дни, по которым нет остатков, чтобы не планировать дни без остатков в будущем.
Пример запроса взяли на примере одного из типовых решений 1С. Там такой расчет средних продаж достаточно долго используют, и он себя хорошо зарекомендовал.
Как считают, смотри здесь https://its.1c.ru/db/erp25doc#bookmark:ExtendedProvision:ConsumptionRateCalculation1.
Но при замере производительности выяснилось, что на базе клиента этот расчет считается примерно 10-15 секунд, а это примерно 30% всего времени расчета.
Запрос на получение продаж с учетом остатков выполняется в пакетном запросе, поэтому для удобства мы разбили его на несколько выполнений запросов, чтобы в отладчике определить какой запрос дольше всего выполняется (картинка кода) и дальше отловить проблемный запрос в трассировке (см. рис. 4).
Рис. 4. Пример выполнения пакетного запроса для анализа скорости выполнения каждого запроса
По замеру производительности, дольше всего выполнялись запросы временных таблиц ДатыПризнаковНаличияОстатковДляДня и КоличествоДнейНаПродажеСУчетомОстатков (нижние два на рис. 4)
Трассировкой определяю планы запроса.
Для таблицы ДатыПризнаковНаличияОстатковДляДня 92% времени выполняются операторы (см. рис. 5):
Рис. 5. План запроса для ДатыПризнаковНаличияОстатковДляДня
Для таблицы КоличествоДнейНаПродажеСУчетомОстатков 83% времени выполняются операторы (см. рис. 6):
Рис. 6. План запроса для КоличествоДнейНаПродажеСУчетомОстатков
Проблемы у запросов схожие:
Т.е. чтобы оптимизировать работу запроса нужно сократить таблицы для оператора Hash Match.
Задача получается нетривиальной. Потому что для расчета продаж по остаткам мы строим таблицу остатков на каждый день анализа. Таблица остатков это 16 тыс. позиций. Таблица дней недели это 158 дней анализа. Таким образом, понятно, что итоговая таблица будет соразмерно таблице с записями 16.000 * 158, т.е. порядка 1-2 млн. строк. И только после этого таблица группируется, помещается во временную таблицу и далее соединяется для получения итоговой скорости продаж. Т.е. надо искать другой подход получения итоговых данных.
Одной из первых идей была отказаться от помещения итоговой таблицы во временную таблицу, вместо нее использовать подзапрос (см. рис. 7).
Рис. 7. Запрос вычисления скорости продаж, без записи итоговой таблицы во временные таблицы
Но время выполнения запроса не дало существенного выигрыша, а трассировка показала, что MS SQL Server для выполнения запроса сам создал точно такую же временную таблицу, как и в предыдущем запросе.
Так как от перебора итоговой таблицы нельзя отказаться была реализована другая идея – избавиться от итоговой таблицы:
Таким образом, получаем те же самые результаты, но уже без использования таблиц с сотнями тысяч записей (см. рис. 8)
Рис. 8. Оптимизация запроса получения количество дней с остатками
В итоге скорость выполнения запроса увеличилась в 5 раз. А сам итоговый запрос выполняется за 2 секунды.
Пора подвести итоги. По замерам производительности удалость оптимизировать скорость расчета потребностей с 795 до 51 секунды.
В целом хорошие результат оптимизации насторожили тем, что при основной работе не было ни одного проблемного запроса (проблемой оказался собственный запрос).
Поэтому для дополнительного анализа тонких мест выполнения запросов расчет был запущен на файловой базе. Параметры оборудования см на рис. 9.
Рис. 9. Параметры оборудования
После замеров времени, выяснилось, запросы стали выполняться дольше, а время выполнения выросло с 51 секунды до 400 секунд (см. рис. 10).
Рис. 10. Замеры расчета потребности на файловой базе
Возможно, расчет на несколько тыс. позиций не предназначен для файловой базы. Но здесь могут быть проблемы с построением запроса и индексов.
По данным монитора, загруженность процессора составила 25%. Так как, с учетом моего оборудования, считал только один процессор из 4-х (особенности работы 1С на файловой базе). Он и был загружен под 100%. Скорость диска и памяти хватало для расчетов.
Можно сделать вывод, что при выполнении расчетов необходимо было вычислять много операций, что, скорее всего, было связано с проблемами в запросе или построению индексов.
Использование технологического журнала для вывода времени выполнении запросов и построению планов запросов здесь не совсем подойдет. Планы запросов не пишутся для файловой базы, а скорость работы запросов удобнее замерить в конфигураторе.
Итак, судя по замерам времени проблемных запросов два:
Используем такой же подход, что и при оптимизации запросов в базе SQL. Разбили пакетный запрос на части, и замерили каждый запрос, как было описано выше. Замер производительности показал проблемы чтения данных по номенклатуре поставщика (см. рис. 11)
Рис. 11. Запрос получение остатка на складах поставщика
В запросе выполняется соединение:
Рис. 12. Формирование временной таблицы ТаблицаНоменклатуры
Рис. 13. Реквизиты справочник Номенклатура поставщиков
Трассировка и построения плана запроса на файловой базе недоступны**
** Почти недоступны. Как вариант остается Автономный сервер (подробнее здесь https://its.1c.ru/db/v8323doc/bookmark/adm/TI000000894). Но этот вариант не понадобился.
Первое, что приходит в голову - это создать индекс для таблиц, чтобы индексы были покрывающими. Создать индекс для временной таблицы труда не составит. Но для справочника такой индекс создать нельзя.
Просто проиндексировать временную таблицу недостаточно, скорость работы запроса при этом не меняется. Причина в том, что в запросе есть другие поля таблицы, также поля через выбор условия. Поэтому индекс временной таблицы не будет покрывать соединение со справочником.
Попробовав разные варианты, нашелся вариант, когда запрос работает на порядок быстрее.
Для этого необходимо:
В этой схеме индекс таблицы «ТаблицаНоменклатуры» стал покрывать соединение справочника «Номенклатура поставщиков». Имея покрывающий индекс, не нужно было выполнять множество операций для получения результирующей таблицы. При оптимизации запросов в SQL это не было заметно, потому что оптимизатор запросов выполнял операции на порядок быстрее файловой базы. Разница в количестве операций не давала существенного замедления.
Рис. 14. Помещаем справочник во временную таблицу для соединения
По этой же причине индексация временной таблицы продаж помогла ускорить получение данных из виртуальной таблицы остатков и оборотов. Этого оказалось достаточно для ускорения работы запроса в несколько раз (см. рис. 15).
Рис. 15. Увеличение скорости получения остатков и оборотов, если проиндексировать временную таблицу Продажи
Итак, после проведения оптимизации в несколько этапов удалось увеличить скорость расчета потребностей в разы с 12 минут до 40 секунд (в 18 раз).
Основные моменты в оптимизации стали: