Заметки: Оптимизация

Ладно, загадка Жана Фреско. У вас есть таблица, скажем, на 50 тысяч строк. Как прочитать из неё полмиллиарда?

Раз плюнуть, Nested Loops + Clustered Index Seek:

От Clustered Index Seek тут одно название, конечно. Фактически оператор при каждом исполнении пробегает по всей таблице (всему кластерному индексу) и сверяет каждую запись с Predicates. И так — 10 730 раз для 51 391 записей. В итоге 551 425 430 строк прочитали, 13 343 вернули.

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

For Each Table1Row In Table1 Do
    For Each Table2Row In Table2 Do
        ...

Это ОК для мелких таблиц, но СУБД может его применить и для таблиц поболбше — например, если ей не хватает времени на построение плана.

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

Некоторые регистры и сами по себе были здоровенными, а виртуальные таблицы дополнительно поддали жару (каждая превращается в 2+ вложенных запроса). СУБД честно пыталась придумать эффективный алгоритм, но в какой-то момент решала, что хреновый план запроса всё же лучше, чем вообще никакого.

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

Короче, по поводу виртуальных таблиц в динамических списках. В английском есть выражение «desire path», «протоптанная дорожка». Часто прицепить виртуальную таблицу к основной — и в самом деле самый простой, быстрый и привычный способ решить задачу. Но он не эффективен.

Есть, например, обработчик ПриПолученииДанныхНаСервере(). Он дольше в реализации, но позволяет хорошо затюнить виртуальную таблицу и избежать сценария выше. На каждую прокрутку списка получится больше запросов, но они будут быстрее и эффективнее, чем один, но гигантский.

Регулярно слышу тейк: запросом больше, запросом меньше — без разницы. Мол, главное, чтобы запрос был дешевым: не читал лишнего, попадал в индекс и так далее.

Эта точка зрения имеет право на жизнь, однако бездумно пулеметить запросами — опасная затея. Даже если в моменте все выглядит хорошо, в будущем система может слегка поменяться. А потом внешне безобидный патч положит вам прод в пятницу.

Пример из недавней практики. Есть ERP, в которой лежит таблица с этапами оплаты по заказам клиентов. Один из таких этапов — предоплата; пока она не внесена, создать заказ поставщику нельзя.

Технически в заказе поставщику просто хранится ID заказа клиента; если последний заполнен (то есть, заказ поставщику создан под заказ клиента), ERP нужно прочитать этапы оплаты по заказу клиента и понять, можно ли делать закупку.

Звучит элементарно, однако мониторинг показывает: операция тормозит и жрет память, будто в последний раз. Лезем разбираться. Видим примерно такую картинку:

То есть вместо того, чтобы выдернуть два-три этапа оплаты по заказу, ERP читает без малого миллион! Как так?

Опуская детали: проблема рождалась в тех заказах поставщику, которые вообще не были связаны с заказом клиента. Разработчик посчитал, что для них можно не менять логику: ID заказа клиента пустой и запрос не найдет для него этапов оплаты. А значит, получится тот же результат, как если бы запроса вообще не было. А лишний запрос — ну... Запросом больше, запросом меньше... Тоже мне, большое дело.

Оказалось, большое. В таблице этапов оплаты оказались данные не только для заказов клиентов, но и для других видов документов. Поле с ID заказа клиента у них было пустым. В итоге ERP при попытке найти этапы оплаты по пустому ID заказа клиента находила такие записи — и, как видите, немало.

Запрос читал порядка гигабайта данных и помещал во временную таблицу. Гигабайт прочитали, гигабайт записали... История била и по диску, и по буферному кэшу СУБД, и по другим частям системы (вплоть до сети, которой этот гигабайт приходилось гонять туда-сюда без всякой пользы).

В общем, знаете, что я думаю? Если результат запроса известен — наверное, его все-таки не нужно делать.

Прочитал этот твит и внезапно вспомнил случай из практики. Прилетает запрос от клиента — мол, 1С тормозит, сделайте что-нибудь. Уточняем: тормозит всё-таки не вся 1С, а конкретная операция.

База клиента — переписанная УТ, проблемная операция — из блока доработок. Внутри — множество многоэтажных запросов, возня с таблицами значений, какие-то невнятные вычисления и пачка серверных процедур по семьсот строк каждая. Копию базы снять нельзя, ТЖ недоступен, доступ к СУБД закрыт, доступ к счётчикам — тоже. Отладка на сервере 1С выключена, включить — не вариант.

Бардак, в общем. Как понять, где тут всё вязнет? Ладно, конфигурацию можно менять — уже неплохо. Вешаю на операцию замер: ну да, думает над чем-то пару минут. Отлично! Потираю ручки и сужаю область поиска: докидываю целый ворох замеров на самые подозрительные участки.

Результат любопытный: тормозов нет! Код не то чтобы летает, но о минутах и речи нет — с учетом сложности, расходы в пределах допустимого. Может, какая-то сериализация данных между клиентом и сервером? Тщательно выверяю код серверной процедуры — нет, никакой трансляции. Всё, что было на сервере, осталось на сервере.

Перехожу к клиентской части. Что тут может тормозить-то?.. Выглядел код, упрощая, как-то так:

Смотрел я на этот код, как баран, минут десять. В конце концов спросил коллегу и он рассказал удивительную вещь: оказывается, Оповестить() не просто рассылает оповещения во все открытые формы, а ждёт, когда в них завершится обработчик оповещения.

Я знатно офигел, полез проверять и в конце концов наткнулся на форму, которая ловила событие выполнение операции и подрывалась обновлять свои динамические списки. Запрос в одном из них и давал тормоза (там подзапрос к подзапросу к подзапросу и ворох соединений «через точку» — в общем, дальше не интересно).

Такая вот яломиште :-) Оповещайте с осторожностью!

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

На первый взгляд структура запроса простая и четкая: пачка запросов к таблицам документов, соединяемых через ОБЪЕДИНИТЬ ВСЁ. Каждая из таблиц фильтруется по примерно одинаковым условиям — тип ссылки, дата и вхождение ссылки в результат подзапроса.

Однако потенциальных проблем тут сразу несколько. Во-первых, работа идет как минимум с 14-ю таблицами — по числу соединяемых запросов. Это само по себе повышает риск того, что оптимизатор не успеет подобрать хотя бы относительно приличный план выполнения. Скорее всего, он просто ткнет пальцем в небо, а дальше — как повезет.

Во-вторых, большая часть этой работы, вероятно, будет выполняться впустую. Каждый из запросов содержит примерно такую конструкцию:

WHERE VALUETYPE(AdditionalExpenses.Ref) IN (&DocumentsListSelectedTypes)

То есть в динамический список передается список типов документов, которые требуется вывести. Однако это условие будет наложено после выборки данных, и если пользователю нужны только инвойсы — СУБД все равно сначала выгребет все 14 таблиц, а потом отбросит 13 из них.

Но это всё не так критично. Если бы список проблем этим и ограничивался, мы, возможно, и не полезли бы разбираться. Главная проблема — во втором условии секций WHERE: каждый запрос проверяет вхождение ссылки на документ в результат подзапроса.

Использование вложенного запроса в условиях — само по себе почти табу, если речь идет не о временных таблицах: СУБД часто не в состоянии понять, сколько данных вернет подзапрос и, соответственно, какой способ работы с ними подойдет лучше. Однако тут это ещё и усугубляется тем, к какому источнику данных мы делаем запрос. Критерий отбора — это не таблица в базе данных, которую можно прочитать, пусть даже со сканом — это набор таблиц. В критерии отбора DocumentsByProject их тридцать!

В этом месте можно было бы сказать «занавес», но нужно добавить, что выборку из тридцати таблиц по определенному типу документа делает каждый из четырнадцати соединяемых запросов.

Вот теперь занавес :-)

Получил вводную: документ закрытия месяца у заказчика проводится без малого час. База не особенно большая, но такая продолжительность в любом случае не вариант. Даже если учесть, что закрытие месяца — это ни разу не частотная операция и её можно делать, например, ночью.

Лезу в код, делаю замер производительности и вижу, что и почти всё это время платформа тратит на одну-единственную процедуру, выполняя пакет запросов. Смотрю первый из них; ну, думаю, классика — запрос данных через точку от составного типа. Наверняка СУБД пристегивает целый вагон соединений с тяжеленными таблицами документов, вот оптимизатор и не успевает набросать адекватный план.

Проверяю теорию — так, а тип SalesDocument включает всего восемь документов. Это, условно, в пределах допустимого (считается, что оптимизатор в состоянии подобрать адекватный план выполнения запроса, если количество соединений — в пределах восьми).

Смотрю размеры таблиц документов — не особенно-то и большие. Выполняю запрос отдельно от пакета — да, работает не мгновенно (читает около 350 000 записей и отбирает примерно 200 000), но никак не час.

Ладно, первый запрос пакета тяжелый, но проблема не в нём. Лезу во второй и понимаю, что до этого прочитали весь регистр Inventory и отобрали большую часть записей, а теперь — читаем его ещё раз и склеиваем обе выборки по куче условий. Подходящего индекса в таблице движений нет — только стандартный по регистратору и он, конечно, не подходит.

Проверил — именно тут платформа и проводит большую часть времени, ожидая ответа от СУБД.

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

Это тот случай, когда общая рекомендация 1С сработала идеально — вместо 50 минут документ стал проводиться за три, а после дополнительной оптимизации кода — за минуту. То есть, в пятьдесят раз быстрее того, что я имел вначале.

Такой результат я счел достаточным (минута для закрытия месяца — это в общем случае нормально) и остановился.