Продолжаем серию статей по вопросам оптимизации производительности. В этот раз у нас один интересный случай, который по праву заслуживает пристального рассмотрения.
Оптимизация в данном примере возведена в абсолют абсурд, по моему скромному мнению. Давайте рассмотрим данный пример, который мне подкинул коллега разработчик. Скажем спасибо Светлане, она сделала мой день) возможно, и Ваш тоже.
Мы рассмотрим эту проблему в классическом подходе:
- Выявление проблемы
- Объяснение ситуации
- Поиск решения
I) Выявление проблемы.
Как было сказано выше, то с данной ситуацией столкнулся разработчик. Он выполнял перенос изменений из одной версии ERP 2.4 в новую 2.5. И при попытке проверить решение на рабочем примере увидел неожиданное поведение платформы - база зависала. Конечно же она работала, но выглядело это не очень обнадеживающе.
Давайте найдем точку возникновения проблемы. Примерная позиция нам известна, но как говорится точность позволяет повысить скорость решения проблемы. Открываем нашу конфигурацию мониторинга производительности и ищем долгие запросы. Этот запрос виден сразу, он один единственный длительностью более 6 тысяч секунд.
На рисунке мы видим, что во временную таблицу помещается почти 11 миллионов строк. Как мне рассказал разработчик, то таблица товаров в тестируемом документе содержала около 1000 строк. Обратите внимание, что специалисты нашей команды проверяют не на 2-3х строчках, а с достаточным количеством данных. Если бы мы тестировали тяп-ляп, то этой проблемы не обнаружили.
Ниже приведен контекст рассматриваемой ситуации.
Сам запрос в представлении языка SQL выглядит следующим образом:
А вот это мы поймали в журнале СУБД Postgres. Тоже самое, но с текстовым планом запроса. Этот запрос мы чуть позже посмотрим в графическом представлении.
Теперь откроем код конфигурации и посмотрим что под капотом. Это модуль менеджера документа "Корректировка назначения товаров" -> функция ТаблицаПомещенияЯчейкиПоТоварам.
В этой функции большой пакетный запрос. И мы видим, что это один из запросов пакета. Давайте преобразуем текст SQL запроса в представления 1С и определим виновника данной ситуации (используем обработку - Конвертер для преобразования текстов запросов и планов SQL в представления языка 1С ):
Под этот отпечаток подходит следующий запрос из пакета:
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
Таблица.Назначение КАК Назначение,
Таблица.Номенклатура КАК Номенклатура,
Таблица.Характеристика КАК Характеристика,
Ячейки.Ссылка КАК Ячейка
ПОМЕСТИТЬ ТоварыВЯчейкахОтбор
ИЗ
ВтТоварыЗаказа КАК Таблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.СкладскиеЯчейки КАК Ячейки
ПО Ячейки.Владелец = Таблица.Склад
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СкладыИПомещения КАК СкладыИПомещения
ПО СкладыИПомещения.Помещение = Ячейки.Помещение
И СкладыИПомещения.Склад = Ячейки.Владелец
ГДЕ
ЕСТЬNULL(Таблица.Назначение.ДвиженияПоСкладскимРегистрам, ИСТИНА)
И СкладыИПомещения.ИспользоватьАдресноеХранение
ИНДЕКСИРОВАТЬ ПО
Назначение, Номенклатура, Характеристика, Ячейка
;
////////////////////////////////////////////////////////////////////////////////
Мы нашли проблемную точку, продолжаем анализ этой проблемы и переходим к следующей части.
II) Анализ текущей ситуации.
Давайте преобразуем план запроса и посмотрим его графическое представление с помощью обработки конвертора SQL в 1С. Готовую схему запроса можно посмотреть по следующей ссылке:
https://explain.tensor.ru/archive/explain/a361999e59afee1396450cc2de78405f:0:2023-03-29#visio
Проанализируем представленный план запроса и посмотрим где проблема и что можно с этим сделать. Как мы видим на рисунке ниже происходит следующая ситуация. Будем двигаться слева на право и сверху вниз:
- Изначально соединяется временная таблица товаров с назначениями, вопросов к такому соединению у меня нет;
- Затем происходит соединение таблицы складских ячеек с таблицей "товары + назначения" (результат первого шага). Условие соединения по владельцу ячейки (это склад) и склада из предыдущей таблицы. Вот тут на картинке с выделенной позицией 1 показана первая проблема. На каждую строку таблицы товаров присоединяется порядка 12 тысяч записей таблицы ячеек. В результате перемножения 11 611*932 мы получаем 10 821 452 строк данных. На реальном складе может быть 12 тысяч ячеек, а не как в демонстрационной 10-20 штук.
- Далее начинается "веселье". Еще одно соединение с временной таблицей СкладыИПомещения, это требуется для определения адресности по складу в зависимости от даты начала использования. И в этом случае у нас получается почти 10 миллиардов строк. Выделено на картинке под индексом 2.
(!) Внимательный читатель посмотрит на запрос выше и скажет: "Откуда появляются 10 миллиардов строк. Происходит внутреннее соединение по условию Склад и Помещение. У ячейки владелец - это Склад, а реквизит Помещение. Должна быть одна запись на запрос, т.е. остаться 10 миллионов строк.". Ответ на этот вопрос можно получить, если посмотреть как получается временная таблица СкладыИПомещения.
В запросе ее создания мы видим, что соединяется таблица складов с помещениями. А затем с таблицей товаров, по условию склада.
Мысль понятна, оставить только входящие склады в таблицу товаров. Но вот из-за этого нелогичного соединения у нас появляется размножение. На мой взгляд, стоит поставить оператор "РАЗЛИЧНЫЕ" или использовать условие для фильтра склада с оператором "В". От таблицы товаров не выбирается не одного поля, а она используется в качестве отбора. (P.S. У меня походу работы накопилось достаточно много вопросов к автору сего шедевра) - И в конце концов у нас выполняется выборка различных. Как мы все знаем, то это обычно оператор группировки. См. индекс 3. В данном случае для таблицы 10 миллиардов записей не хватило места в оперативной памяти выделенного процессу (это около 1ГБ). И Postgress решил выполнять сортировку на жестком диске.
Есть ли проблемы в плане запроса? Может, планировщик ошибается? Как вы думаете?
На самом деле ответ - нет. Планировщик не ошибается, а делает то что мы от него просим. В данном случае происходит и должно происходить перемножение с помощью Nested Loop.
(!) Подобный подход с перемножением мне встречался ранее - при проверке остатков товаров организаций, там тоже получаются дикие промежуточные временные таблицы. В том случае происходит что-то подобное - умножение всех доступных видов запасов на таблицу остатков товаров организаций.
Давайте разбираться дальше. Вспомним название временной таблицы - ТоварыВЯчейкахОтбор. Это данные для фильтра. Теперь посмотрим где она используется. А используется она только в одном запросе пакета - при получении временной таблицы ТоварыВЯчейках:
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Остатки.Назначение КАК Назначение,
Остатки.Номенклатура КАК Номенклатура,
Остатки.Характеристика КАК Характеристика,
Остатки.Ячейка КАК Ячейка,
Остатки.Упаковка КАК Упаковка,
Остатки.Серия КАК Серия,
Остатки.ВНаличииОстаток - Остатки.КОтборуОстаток КАК ВНаличии
ПОМЕСТИТЬ ТоварыВЯчейках
ИЗ
РегистрНакопления.ТоварыВЯчейках.Остатки(,
(Назначение, Номенклатура, Характеристика, Ячейка) В(
ВЫБРАТЬ
Фильтр.Назначение КАК Назначение,
Фильтр.Номенклатура КАК Номенклатура,
Фильтр.Характеристика КАК Характеристика,
Фильтр.Ячейка КАК Ячейка
ИЗ
ТоварыВЯчейкахОтбор КАК Фильтр)) КАК Остатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Движения.Назначение КАК Назначение,
Движения.Номенклатура КАК Номенклатура,
Движения.Характеристика КАК Характеристика,
Движения.Ячейка КАК Ячейка,
Движения.Упаковка КАК Упаковка,
Движения.Серия КАК Серия,
ВЫБОР
КОГДА Движения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ТОГДА -Движения.ВНаличии
ИНАЧЕ Движения.ВНаличии
КОНЕЦ КАК ВНаличии
ИЗ
РегистрНакопления.ТоварыВЯчейках КАК Движения
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТоварыВЯчейкахОтбор КАК Фильтр
ПО Фильтр.Назначение = Движения.Назначение
И Фильтр.Номенклатура = Движения.Номенклатура
И Фильтр.Характеристика = Движения.Характеристика
И Фильтр.Ячейка = Движения.Ячейка
ГДЕ
Движения.Регистратор = &Регистратор
И Движения.Активность
ИНДЕКСИРОВАТЬ ПО
Ячейка
;
////////////////////////////////////////////////////////////////////////////////
В итоге, на мой взгляд, - это попытка многогранной оптимизации. Одна из целей - учесть все поля индекса для максимального быстродействия. Пояснения ниже:
(!) Как мы прекрасно знаем, то при использовании виртуальной таблицы остатков регистра накопления создается таблица итогов на конец каждого месяца + таблица итогов оперативных остатков. В нашем случае используется таблица оперативных остатков, т.к. период не задан.
Для этой таблицы итогов создается набор индексов (на рис. ниже последняя таблица). Один из них - кластерный и содержит в себе набор измерений по порядку их расположения в структуре реквизитов группы измерения регистра.
Однако, как мы видим, то разработчики явно пропустили одно измерение - упаковку. Добавим все возможные варианты упаковок и сделаем еще один цикл? Итого у нас получится 100 миллиардов записей и это полный мрак, зато все поля индекса используются максимально.
Что будем делать? Смотрим ниже.
III) Пути решения.
Прежде чем начинать придумывать как исправить эту проблемную ситуацию давайте вспомним что в предыдущей версии все работало достаточно быстро. Открываем старую версию и смотрим тот же модуль и подобную функцию.
В старой версии есть такая же функция и похожий пакет запроса:
Если сравнить эти две функции для различных конфигураций, то мы увидим, что они достаточно похожи. Изменения заключаются в оптимизации:
- Избавление от вложенных запросов. Часть вынесли в отдельные временные таблицы и обозвали как таблицы отборы. Другую часть представили как таблицы остатков по разделам;
- Добавили отборы внутрь виртуальных таблиц;
- Добавили индексы.
Посмотрим как выглядит вариант получения остатков товаров в ячейках - целевая часть применения временной таблицы отборов в ячейках:
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Таблица.Назначение,
Таблица.Номенклатура,
Таблица.Характеристика,
Таблица.Серия,
Таблица.Склад,
Таблица.Помещение,
Таблица.Упаковка,
Таблица.Ячейка,
СУММА(Таблица.ВНаличии) КАК ВНаличии,
МАКСИМУМ(Таблица.СвободныйОстаток) КАК СвободныйОстаток,
МАКСИМУМ(Таблица.Количество) КАК Количество
ПОМЕСТИТЬ ТоварыВЯчейкахОстатки
ИЗ
(ВЫБРАТЬ
ТоварыВЯчейкахОстатки.Назначение КАК Назначение,
ТоварыВЯчейкахОстатки.Номенклатура КАК Номенклатура,
ТоварыВЯчейкахОстатки.Характеристика КАК Характеристика,
ТоварыВЯчейкахОстатки.Серия КАК Серия,
ТоварыВЯчейкахОстатки.Ячейка.Владелец КАК Склад,
ТоварыВЯчейкахОстатки.Ячейка.Помещение КАК Помещение,
ТоварыВЯчейкахОстатки.Упаковка КАК Упаковка,
ТоварыВЯчейкахОстатки.Ячейка КАК Ячейка,
ТоварыВЯчейкахОстатки.ВНаличииОстаток - ТоварыВЯчейкахОстатки.КОтборуОстаток КАК ВНаличии,
Т.СвободныйОстаток КАК СвободныйОстаток,
Т.Количество КАК Количество
ИЗ
РегистрНакопления.ТоварыВЯчейках.Остатки КАК ТоварыВЯчейкахОстатки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтТоварыЗаказа КАК Т
ПО ТоварыВЯчейкахОстатки.Назначение = Т.Назначение
И ТоварыВЯчейкахОстатки.Номенклатура = Т.Номенклатура
И ТоварыВЯчейкахОстатки.Характеристика = Т.Характеристика
И ТоварыВЯчейкахОстатки.Ячейка.Владелец = Т.Склад
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТоварыВЯчейках.Назначение,
ТоварыВЯчейках.Номенклатура,
ТоварыВЯчейках.Характеристика,
ТоварыВЯчейках.Серия,
ТоварыВЯчейках.Ячейка.Владелец,
ТоварыВЯчейках.Ячейка.Помещение,
ТоварыВЯчейках.Упаковка,
ТоварыВЯчейках.Ячейка,
ВЫБОР
КОГДА ТоварыВЯчейках.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ТОГДА -ТоварыВЯчейках.ВНаличии
ИНАЧЕ ТоварыВЯчейках.ВНаличии
КОНЕЦ,
Т.СвободныйОстаток,
Т.Количество
ИЗ
РегистрНакопления.ТоварыВЯчейках КАК ТоварыВЯчейках
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтТоварыЗаказа КАК Т
ПО ТоварыВЯчейках.Назначение = Т.Назначение
И ТоварыВЯчейках.Номенклатура = Т.Номенклатура
И ТоварыВЯчейках.Характеристика = Т.Характеристика
И ТоварыВЯчейках.Ячейка.Владелец = Т.Склад
ГДЕ
ТоварыВЯчейках.Регистратор = &Регистратор) КАК Таблица
СГРУППИРОВАТЬ ПО
Таблица.Назначение,
Таблица.Номенклатура,
Таблица.Характеристика,
Таблица.Серия,
Таблица.Склад,
Таблица.Помещение,
Таблица.Упаковка,
Таблица.Ячейка
; ////////////////////////////////////////////////////////////////////////////////
Делаем выводы по изменениям в версии ERP 2.5:
- Вложенный запрос был вынесен во временную таблицу - ТоварыВЯчейках;
- Соединение фильтр также было вынесено во временную таблицу - ТоварыВЯчейкахОтбор;
- Дополнительно добавлять в таблицу отборов ячейку не имеет практической ценности.
Фактически таблицу отборов в ячейках можно смело выкинуть из запроса и вместо нее брать временную таблицу заказов, а сам код получится чуть проще (учитываем, что таблица товаров должна быть свернута в рамках назначения, номенклатуры, характеристики, склада). Смотрим ниже:
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Остатки.Назначение КАК Назначение,
Остатки.Номенклатура КАК Номенклатура,
Остатки.Характеристика КАК Характеристика,
Остатки.Ячейка КАК Ячейка,
Остатки.Упаковка КАК Упаковка,
Остатки.Серия КАК Серия,
Остатки.ВНаличииОстаток - Остатки.КОтборуОстаток КАК ВНаличии
ПОМЕСТИТЬ ТоварыВЯчейках
ИЗ
РегистрНакопления.ТоварыВЯчейках.Остатки(,
// (Назначение, Номенклатура, Характеристика, Ячейка) В(
// ВЫБРАТЬ
// Фильтр.Назначение КАК Назначение,
// Фильтр.Номенклатура КАК Номенклатура,
// Фильтр.Характеристика КАК Характеристика,
// Фильтр.Ячейка КАК Ячейка
// ИЗ
// ТоварыВЯчейкахОтбор КАК Фильтр)) КАК Остатки
(Назначение, Номенклатура, Характеристика, Ячейка.Владелец) В(
ВЫБРАТЬ
Фильтр.Назначение КАК Назначение,
Фильтр.Номенклатура КАК Номенклатура,
Фильтр.Характеристика КАК Характеристика,
Фильтр.Склад КАК Склад
ИЗ
ВтТоварыЗаказа КАК Фильтр)) КАК Остатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Движения.Назначение КАК Назначение,
Движения.Номенклатура КАК Номенклатура,
Движения.Характеристика КАК Характеристика,
Движения.Ячейка КАК Ячейка,
Движения.Упаковка КАК Упаковка,
Движения.Серия КАК Серия,
ВЫБОР
КОГДА Движения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ТОГДА -Движения.ВНаличии
ИНАЧЕ Движения.ВНаличии
КОНЕЦ КАК ВНаличии
ИЗ
РегистрНакопления.ТоварыВЯчейках КАК Движения
// ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТоварыВЯчейкахОтбор КАК Фильтр
// ПО Фильтр.Назначение = Движения.Назначение
// И Фильтр.Номенклатура = Движения.Номенклатура
// И Фильтр.Характеристика = Движения.Характеристика
// И Фильтр.Ячейка = Движения.Ячейка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтТоварыЗаказа КАК Фильтр
ПО Фильтр.Назначение = Движения.Назначение
И Фильтр.Номенклатура = Движения.Номенклатура
И Фильтр.Характеристика = Движения.Характеристика
И Фильтр.Склад = Движения.Ячейка.Владелец
ГДЕ
Движения.Регистратор = &Регистратор
И Движения.Активность
ИНДЕКСИРОВАТЬ ПО
Ячейка
;
////////////////////////////////////////////////////////////////////////////////
Краткий итог:
После доработки пакетного запроса проблема "зависания" ушла, исчезла и более нас не беспокоила. И мы перешли к другим проблемным частям в коде конфигурации. А их было много, что-то вылавливаем до сих пор.
Таким образом мы с вами исключили сложный и фактически излишний кусок запроса, который самым отрицательным образом влияет на производительность "живых"/"настоящих" рабочих баз.
(!) Коллеги у меня накопилось еще с десяток интересных примеров по результатам апгрейда конфигурации ERP заказчика, если Вам интересно - пишите, и я постараюсь опубликовать их в ближайшее время.
