Виды оптимизации
В разные периоды разработки мы занимаемся разной оптимизацией. В начальной стадии разработки нам в первую очередь нужно оптимизировать скорость разработки. И значит, запросы в первую очередь должны быть наглядны с точки зрения соответствия бизнес- процессам. Они должны быть коротенькими без лишних связей, легко изменяемыми. Опять же наглядность пошаговой обработки данных на сервере приложения манит забрать побольше данных с сервера базы для более быстрой выдачи результата заказчику.
Данных немного и всё работает. В процессе эксплуатации скелет обрастает мясом, жиром и уже не так двигается, как было задумано. Приходит новый этап – оптимизации скорости работы, снижение загрузки ресурсов.
Разгружаем железо
Если сравнивать с автомобилем, то мне рисуется, что мотор, трансмиссия – это процессор, память, диски. База данных – это бездорожье. Вот задача оптимизации выборки из базы – найти правильную дорогу, где-то убрать камни, где-то построить мостик, где возможно, чтобы не объезжать. Построить мостик обычно значительно дороже, чем воткнуть более мощный двигатель, который даст прирост скорости везде, но мостик может сократить путь в тысячи раз.
В общем случае необходимо уменьшить объем обрабатываемых данных за счет точной выборки, соединений (циклов в цикле) с минимальным количеством строк, выпрямления адресации индексами.
У 1С описаны стандарты работы с запросами "Система стандартов и методик. Реализация обработки данных". Стандарты переводят различные упрощения написания текстов в разряд ошибок. Первый проход оптимизации – проверка стандартов, исправление ошибок. На мой взгляд, стандарты в первую очередь описывают, как избавиться от неявных запросов или структур в запросе. Делая невидимое – видимым и уменьшая неявный объем загрузки железа «повышая освещенность местности».
Объектная модель данных и циклы
Очень удобно найти ссылку на объект и потом в тексте напрямую обращаться к его полям по мере необходимости. Это быстро пишется и наглядно, и вполне жизнеспособно. Однако по факту в этот момент происходит неявная выборка на сервере базы всех данных объекта с его вложенными таблицами, затем передача данных на сервер приложения. Если это делается в цикле, то достаточно просто забить всю память на сервере приложений. Даже если идет присвоение ссылки объекта одной и той же переменной в цикле, кэшированный объект не отпускается, а накапливается (может, это уже исправлено или будет исправлено в какой-нибудь версии платформы). Поэтому лучше неявное обращение к базе заменить явным, нагляднее увидеть процессы и ужаснуться. И постараться сделать предварительную выборку запросом за циклом. Так мы уменьшим нагрузку на оперативную память сервера приложений, снизим количество запросов к серверу базы, и значительно повысим скорость выполнения («значительно» при больших циклах, даже если получаем всего одно поле).
Общий вывод: в циклах не стоит использовать объектный доступ к данным, даже в маленьких. Это он, может, сегодня маленький, а через пару лет станет монстром, а «их надо убивать, пока она маленькие».
Как выполняются запросы
В клиент-серверном варианте нас интересует: сервер приложения и сервер базы данных. Каждый использует свои ресурсы процессора, памяти, диска. Запрос к базе данных идет с сервера приложений, далее сервер базы данных его обрабатывает и возвращает результат. Пока результат не получен, он использует ресурсы сервера базы. После получения на сервер приложений он занимает память сервера приложения. Передача больших объемов данных на сервер базы через параметры и получение данных результата тоже затратная операция.
Сначала сервер приложения преобразует наименования таблиц, полей из терминов 1С в физические SQL. Преобразует виртуальные таблицы в наборы связанных таблиц SQL. Сюда же наши обращения к полям объектов через две и более точек. Получается менее наглядная, но уже приспособленная для выполнения сервером базы, конструкция. Далее сервер базы анализирует её, анализирует имеющиеся данные по ранее собранной статистике и строит план выполнения запроса. План запроса можно посмотреть через Консоль запросов
Поэтому, если мы хотим, чтобы сервера нас поняли так, как мы задумывали, то писать нужно максимально просто, явно делая каждый шаг, выбирая нужные данные и соединения. Опять же я не призываю делать текст запроса менее наглядным везде. Это необходимо только там, где есть проблемы, а в остальных местах пусть будет более наглядна бизнес логика. Это поможет быстрее вести доработку и разбирать вопросы пользователей.
Индексы
Нужную информацию сервер базы выбирает или простым перебором всех строк таблицы, или задействует индекс для прямой адресации к нужному набору записей. То есть при использовании индекса, выборка сначала идет из таблицы индекса, потом полученные данные соединяются с таблицей данных. То есть индекс может значительно уменьшить выборку, если в нем выбираются далеко не все данные таблицы. А если все или почти все, то это только потери от дополнительных выборок и соединений. Поэтому сервер базы и анализирует данные, собирает статистику и строит план запроса.
При правильном использовании индексов скорость выполнения может вырасти в тысячи раз по сравнению с полной переборкой. И нужно стремиться использовать индексы. Не нужно пытаться зараз указывать все отборы, сначала анализируем данные самостоятельно, выбираем нужный индекс и делаем отбор только по нему с помещением результата во временную таблицу. Для виртуальных таблиц есть правило, что все возможные отборы нужно помещать в скобки параметров виртуальной таблицы. Но по факту это 100% работает при полном переборе данных виртуальной таблицы, в других случаях может сработать, а может и не очень. Если период хорошо ограничен и в таблице итогов всего несколько тысяч или десятков тысяч строк, то да, можно достаточно быстро это сделать перебором. Но если больше, то нужен конкретный индекс, и помещение всех отборов может не позволить серверу базы выбрать нужный индекс и построить оптимальный план запроса. Возможно, в этих случаях правильнее будет как-то поменять структуру данных, чтобы количество итоговых строк было меньше, но это займет гораздо больше времени. Поэтому ищем одно поле индекса, которое максимально снизит объем выборки, и указываем его в скобках, а остальное помещаем в ГДЕ. В сложных случаях это поле в одном и том же запросе, в одной и той-же базе может быть разным, в зависимости от данных отбора. Главное, чтобы оно обеспечивало минимальных набор данных.
Таким же образом индексы помогают не только уменьшить выборку данных, но и уменьшить количество итераций при соединении таблиц. Поэтому 1С рекомендует индексировать временные таблицы по полям соединения Оптимизация запросов. Использование временных таблиц . У временных таблиц больше возможностей по созданию индексов чем у таблиц базы (мы можем самостоятельно выбирать поля, и создавать поля которых нет в базе). Ограничение только: одна таблица – один индекс. Но фактически индекс и есть отдельная таблица, поэтому если нужно несколько индексов, то создаем несколько временных таблиц.
Индексы не панацея, а лишь инструмент. Не надо фанатизма, для создания и поддержания индекса тоже нужно время и место на диске. Увешанная индексами таблица будет не поворотливой. Скорее всего, вместо такого набора индексов основной таблице нужны вспомогательные таблицы с меньшим количеством данных, более приспособленных для построения нужного отчета или обработки.
Составные типы данных
При получении реквизитов объекта поля составного типа данных идет неявное соединение уже не с одной таблицей, содержащей данные этого типа, а со всеми таблицами типов составного поля. Поэтому для снижения затрат, по возможности, в тяжелых выборках нужно приводить такие поля к нужным типам через ВЫРАЗИТЬ( КАК ).
Менеджер временных таблиц
Одним из узких мест является обмен данными между сервером приложения и сервером базы данных. Так как данные хранятся на сервере базы, то и обрабатывать их лучше на сервере базы данных. А на сервер приложения было бы хорошо передавать их не часто и только выжимку небольшого объема.
Обработка на сервере базы это у нас только запросы, весь остальной язык 1С – в топку. Для работы на сервере базы у нас есть МенеджерВременныхТаблиц («у нас» в смысле на сервере приложения). Всегда создаем МенеджерВременныхТаблиц, это по сути сеанс работы с сервером базы. И по окончании работы мы его закрываем. Без этого сам Запрос может не уничтожить временные таблицы на сервере базы и этот мусор будет болтаться и снижать скорость работы, занимая место в памяти и на дисках. Закрытие менеджера, уничтожение ненужных временных таблиц – важная часть оптимизации загрузки железа.
Проблемы отладки запросов
Важность максимальной обработки данных запросами мы вроде обсудили. Какие инструменты для отладки у нас есть? Можно ли пошагово пройти по запросам, можно ли поглядеть какие-то значения? В текстах попадаются многоэтажные запросы с десятком-другим временных таблиц. И очень тяжело представить результаты выполнения. Хотя по идее это такое же программирование, как и сотни тысяч строк обычного текста, и относиться бы к ним надо спокойнее. Но в обычном тексте ты можешь поставить точку останова и поглядеть все переменные, а тут больше напоминает черный ящик. Иметь хорошую память, уметь всё держать в голове это отличный навык. Однако ученые доказали, что мы либо помним, либо думаем. Если мы помним, то способность думать уменьшается в несколько раз (за подробностями к Максиму Дорофееву). Отсюда вывод, что нужно больше визуальной информации, чтобы не помнить, а видеть и думать. И не только когда наступает сложная ситуация, а всегда, чтобы не засорять мозг.
Пошаговая отладка запроса
Существуют отдельные обработки, позволяющие отлаживать запросы. Замечательно работает команда ОТ(Запрос) в «Инструментах разработчика». Она сохраняет запрос и его параметры и далее в режиме 1С:Предприятие «Инструменты разработчика» позволяют восстановить параметры и показать результаты в разрезе временных таблиц. Не всегда она есть под рукой, иногда попадаются просто отличные обработки для отладки запросов, но допустим без возможности записать запрос с его параметрами. И в таких ситуациях проще бы было отлаживать в конфигураторе, плюс появляется возможность подстройки под себя.
Удобно в построителе запросов визуально мышкой набросать наборчик запросов в несколько этажей, отладить его в режиме предприятия и чтобы он не мешал понимать текст закинуть его в функцию ПолучитьТекстЗапроса().
Допустим, у нас такой длинный запрос с множеством временных таблиц. Все таблицы, каждый шаг запроса разделен «;». Вместо Запрос.Выполнить(), делим текст на части функцией СтрРазделить(Запрос.ТекстЗапроса,”;”) , получаем массив и выполняем каждую строку по отдельности в цикле. Так мы получаем контроль, возможность засечь время, сделать что-то ещё на каждом «этаже»-шаге выполнения запроса уже при отладке в Конфигураторе. Это позволяет делать МенеджерВременныхТаблиц.
В разбираемых запросах мельчить надо максимально, выборка отдельно, соединения отдельно. Так лучше видно, что больше страдает: чтение данных или процессор. Тогда, когда соединение и является дополнительным отбором, можно попробовать всё-таки разбить это на два этапа. В выборе использовать «Поле1 В (вт.Поле1 из вт как вт)», а следующим запросом уже соединение. Опять же смотрим индексы, по какому полю он есть в «обрезаемой» таблице. Не нужно использовать все поля по которым есть индекс в структуре метаданных «Поле1, Поле2 В (вт.Поле1, вт.Поле2 из вт как вт)», это разные индексы, а значит использован может быть лишь один из них. Исключением тут будет, если в выборке используются уже только временные таблицы, у которых построен индекс по нескольким полям.
Возможность отладки в конфигураторе начинает перевешивать и мне удобнее такие запросы помещать в основной текст, резать по шагам получения временных таблиц и выполнять каждый по отдельности. Это позволяет на первом этапе получить скорость выполнения каждого шага с помощью «Замера производительности». Читабельность при этом не страдает, её наоборот можно повысить добавляя комментарии в текст, а не в запрос, где он может исчезнуть при использования построителя. А еще лучше помещать их в Область и можно будет сворачивать отдельные блоки запроса.
Время запроса и количество строк
Теперь у нас есть возможность засечь время выполнения каждой части с помощью ТекущаяУниверсальнаяДатаВМиллисекундах(), а также получить другие значения через дополнительный запрос, например количество строк. Для этого создаем новый запрос, устанавливаем тот же менеджер временных таблиц. ЗапросТест = Новый Запрос; ЗапросТест.МенеджерВременныхТаблиц = МенеджерВТ; ЗапросТест.Текст = «выбрать Количество(*) из ВТ_1 » (подставляем свою таблицу); ЗапросТест.Выполнить.Выгрузить();
Эти и другие данные (имя созданной временной таблицы, из каких таблиц она появилась) можно вывести сообщением или сохранить в массив, или временную таблицу для дальнейшего анализа. Результат можно сохранять в Excel или в Хранилище Значений. Количество строк надо контролировать, иногда видно как на первых выборках их в несколько десятков раз больше, чем через несколько шагов. Если полученной производительности недостаточно, то такие случаи надо разбирать и смотреть, как можно улучшить выборку у предыдущего запроса. Может, вместо общей первоначальной выборки, делать под каждый случай свою более узкую. Конечно, это требует уже более серьезной проработки набора запросов и тестов.
Просмотр данных временных таблиц
Посмотреть данные любой временной таблицы мы можем через изменение текста запроса через вычисление выражения Shift+F9 на «Выбрать первые 1000 * из вт_1» и вычисление и выгрузку в таблицу данных: ЗапросТест.Выполнить().Выгрузить(). Но если это делать слишком часто, то это не совсем удобно. Результат запроса можно через ПостроительОтчета выгрузить в ТабличныйДокумент, а тот сохранить Excel (много только выводить не надо). Сделав это в цикле пошаговой отладки, мы можем находясь в конфигураторе в режиме отладке, дополнительно на другом экране просматривать результаты пройденных запросов.
Дерево временных таблиц
Не всегда мы отлаживаем свежие запросы, часто приходится оптимизировать ранее написанные. Тут для понимания нужна наглядность. В менеджере временных таблиц накапливаются имена полученных ВТ. Если перед выполнением очередного запроса пробегаться по ним, ища их в тесте запроса, то можно наглядно сформировать дерево временных таблиц. Просматривая таблицы в МенеджереВременныхТаблиц на каждом этапе, можно отследить время жизни и моменты использования. Подчистить хвосты уничтожая лишние.
Установка дополнительных параметров и прочее
Имея возможность отрабатывать каждый запрос по отдельности, мы можем и дополнительно устанавливать параметры запроса исходя из результатов выполнения предыдущих. Например, найдя период для выполнения следующего запроса, уже можно получить их на сервер приложения и подкинуть на сервер базы в виде параметров в виртуальную таблицу, а не соединять таблицы.
Построение запроса для минимальной скорости выборки зависит от данных. И в каких-то сложных случаях в зависимости от данных уже полученных временных таблиц понадобятся разные тексты запроса. При одних условиях может прекрасно сработать выборка из виртуальной таблицы с её таблицами итогов. В других она станет колом, но при этом достаточно быстро отработает запрос к физическим таблицам.
Думаю, по своим вопросам Вы найдете ещё варианты использования такого последовательно исполнения запросов из массива и для отладки, и для работы.
Сохранение временных таблиц в Хранилище
Не всегда есть возможность оптимизировать запрос до комфортного времени выполнения. Иногда нужно менять структуру данных, иногда нужно сосредоточить внимание на другом. В этих случаях можно полученный результат запроса или промежуточный результат сохранить в ХранилищеЗначения. Для хранения можно найти подходящий справочник или регистр, или создать свой.
Это тоже вариант изменения структуры хранения данных, но со своими особенностями использования. Просто вытащить готовый результат быстрее, чем собирать его с кучи данных и это также снимает нагрузку с железа. Понятно, что подходить к этому тоже нужно осознанно, с головой и использовать с небольшими наборами данных. Может подходить для хранения результатов отчета. Большой объем данных так хранить невыгодно, так как сохранение и восстановление идет через обмен сервера приложения и сервера базы и это может быть не быстрее чем сама выборка.
Было бы удобно сохранять и восстанавливать такие вещи на сервере базы, не вытягивая их предварительно на сервер приложения. Это была бы интересная альтернатива Механизму копий базы данных. Указываешь в качестве параметра объект справочника и поле из какого развернуть во временную таблицу и раз тебе сразу и результат. И аналогично команду ПОМЕСТИТЬ В ХРАНИЛИЩЕ.
Сейчас для больших объемов данных нужно создавать дополнительные регистры. Из них мы напрямую на сервере базы данных можем получать временные таблицы, но для сохранения временных таблиц в них всё равно требуется сначала получить данные на сервер приложения и потом их обратно заслать на сервер базы данных.
Тестирование
Мало сделать правильно, нужно ещё и доказать, что результат остался неизменным при повышении скорости выполнения. И это может отнимать даже больше времени, чем сама доработка запроса. Но это повысит и личное спокойствие, и доверие со стороны заказчика, его желание использовать изменения. Взяться за это бывает тяжело, вроде уже всё сделал красиво, но иногда прям надо.
Построчно сравнивать таблицы не всегда нужно, для начала достаточно сравнить хеш. Снимаем с таблиц-результатов сумму хеша строк. С оригинала, с оптимизированного запроса и сравниваем их между собой. Конечно если есть проблемы, то проще сравнить две таблицы в запросе построчно, но для накопления результатов тестов удобнее краткая запись.
Не всегда нужно сравнивать сами результаты запроса. Если работаем с документами, то нагляднее сравнивать результаты проведения. Можно создать обработку по проведению таких документов с сохранением сумм хеша нужных регистров. В документе оставляем два варианта и новый и старый. И через дополнительные свойства объекта определяем, какой будет выполняться. Проводим по старому, сохраняем нужные результаты и проводим по новому, и сравниваем. Результаты можно сохранять в табличку, а табличку сохранять вместе с параметрами обработки в ХранилищеЗначений. Так за несколько дней и ночей можно накопить достаточно доказательств.
На этом пока всё. Не претендую на истину, каждому своё, мне пока удобнее так.
