Яковлев М.В., Обломов И.А.
ИССЛЕДОВАНИЕ СПОСОБОВ ОПТИМИЗАЦИИ КОДА ПРИ ПЕРЕВОДЕ ХРАНИМЫХ ПРОЦЕДУР НА POSTGRESQL В РАМКАХ ИМПОРТОЗАМЕЩЕНИЯ *
Аннотация:
в работе представлен обзор некоторых способов оптимизации SQL кода, которые будут полезны при переводе хранимых процедур на PostgreSQL. Статья включает обзор типовых ошибок, допускаемых разработчиками, и вариантов их устранения при создании SQL запросов по импортозамещению. Представлены примеры, подтверждающие необходимость замены существующих алгоритмов.
Ключевые слова:
оптимизация, SQL запросы, импортозамещение, СУБД, базы данных, хранимые процедуры
На сегодняшний день всё больше компаний переходят на отечественное программное обеспечение. Это связано как с политическими изменениями, так и с санкциями, введенными по отношению к Российской Федерации. Такой переход называется импортозамещением. Одним из мероприятий по импортозамещению является переход от импортных систем управления базами данных (СУБД) на СУБД с открытым исходным кодом, которое позволит обеспечить безопасность и стабильность баз данных (БД). В состав реляционной базы данных входят не только таблицы с данными, а еще и хранимые процедуры – объекты БД, содержащие набор SQL-инструкций и выполняющие аналогичные функции как в других языках программирования. Рассмотрим наиболее полезные приёмы оптимизации кода в хранимых процедурах на PostgreSQL, обнаруженные методами анализа, экспериментов и сравнения в процессе импортозамещения хранимых процедур в базе данных.По возможности стоит использовать тип «date» вместо «timestamptz». Тип «date» занимает 4 байта фиксированного размера и не содержит информации о времени суток и часовых поясах. Тип данных «timestamptz» занимает 8 байт и хранит значения даты и времени с точностью до микросекунды и обеспечивает корректную работу с данными в различных часовых поясах.Создание скалярных INLINE функций, подставляющих максимальную (минимальную) дату, позволит унифицировать подстановку конкретных значений, что позволит исключить ошибки, когда при ручном заполнении будет введено 2009 вместо 2079. Сами значения выбраны из типа «smalldatetime» в Microsoft SQL Server, который имеет именно такие границы значений. Ниже приведен код функции подставляющей максимальное значение даты:CREATE OR REPLACE FUNCTION globals.max_date ()RETURNS dateLANGUAGE 'sql'AS $$SELECT'20790606'::date$$,Тогда вместо COALESCE (t.d_date1, '20790606') нужно использовать COALESCE (t.d_date1, globals.max_date())Объявлять переменные следует через колоночные типы. Например: DECLARE _C_Cash dbo.t_sale_items. link%type, к преимуществам данного решения относятся:невозможно ошибиться с типом,не нужно вспоминать тип колонки,если тип переменной будет отличен от типа колонки, то в запросе будет неявное приведение типов, что существенно замедлит запрос,если будет меняться тип колонки (тип «int» поменяется на «smallint»), то не придется менять типы переменных повсюду.При инициализации переменной из какого-либо справочника и если она точно должна быть присвоена, то используем «STRICT» присвоение. Часто бывает так, что при переносе процедуры, функции или операции на другую базу, там отсутствуют необходимые значения в каких-то справочниках. Тогда при выполнении этой процедуры, функции или операции немедленно возникает критическая ошибка с указанием переменной, где не выполнилось присвоение и можно сразу найти источник ошибки. Если для нашей логики корректно «NULL» значение, то «STRICT» указывать не нужно.Коррелирующие подзапросы с TOP/LIMIT 1 лучше переделать на LATERAL. Тогда имеем в два раза меньше логических чтений, а значит сокращение времени выполнения запроса. Также исключается вероятность ошибки, когда условия соединения отличаются во внутреннем и внешнем запросе.При соединении или фильтрации к секционированным таблицам по ключу секционирования поля нужно указывать как константу к этим таблицам, тогда отбрасывание лишних секций произойдет на этапе разбора запроса, а не планирования, что существенно увеличит скорость выполнения запроса.Если скалярная функция возвращает одно значение определенного типа, то следует сделать её INLINE. Такая функция уже на этапе разбора запроса «встраивается» в сам запрос. Тогда не возникает накладных расходов на работу со стеком вызова функций, на порождение frame'а и так далее.Табличную функцию также можно сделать INLINE. При использовании в запросе табличной функции, которая не умеет «INLINE'иться», она будет выполнена полностью изолировано, её результат будет помещен в рабочую память и затем уже этот результат будет «присоединен» к основному запросу, который может состоять из множества фильтраций и соединений. Таким образом, оптимизатор планов не будет иметь никакой возможности как-то повлиять на выполнение этой функции, так как она будет выглядеть как «черный ящик», из которого выходят данные. Если функция написана корректно, то оптимизатор будет иметь возможность пробросить внутрь какие-то фильтры, условия соединений, вытащить статистику таблиц, используемых в функции, либо вообще переставить местами порядок соединений – иными словами, тело функции станет просто частью самого запроса. Ниже приведён запрос с вызовом INLINE функции:EXPLAIN ANALYZE VERBOSESELECTdbo.c_month_date_end_n_period(t.n_period),t.n_periodFROM pe.fd_payments tWHERE f_division = 1 AND s_archive = 0LIMIT 500,На рисунке 1 показан план выполнения запроса, где видно «тело» функции, а не вызов напрямую.Рисунок. 1. План выполнения запроса с вызовом INLINE функции.При переводе хранимых процедур в рамках импортозамещения разработчиками могут допускаться следующие виды ошибок: создание реальных таблиц для временных нужд, изменение типов параметров функции, указание лишних условий в соединениях и фильтрациях, применение каскадных (вложенных) соединений без прямой необходимости. Приведём обоснование необходимости замены этих алгоритмов.Применение временных таблиц вместо реальных даёт ряд преимуществ: нет блокировок, так как мы единственные пользователи таблицы, получение гарантированно пустой таблицы (если иное не предусмотрено логикой) – вследствие чего вставки в таблицу происходят быстрее, отсутствует расширение таблицы, за счет ненужных строк, ввиду постоянных модификаций.Если не требуется сохранять данные между сессиями, то использование временных таблиц будет оптимальным решением. Создание временной таблицы может быть осуществлено следующим SQL кодом: CREATE TEMP TABLE temp_table (…) ON COMMIT DROP.Особое внимание стоит уделить при изменении одного или нескольких параметров функции, удалении или добавлении их. В этом случае при компиляции создается новая копия функции с таким же именем, но другим набором параметров. Таким образом, в базе данных могут существовать множество функций с одинаковым именем, но разными параметрами – перегрузка функции. Поэтому важно знать и помнить об этом, что избежать проблем с данными.Каскадные (вложенные) соединения можно вынести в обычное соединение, при условии, что смысл запроса никак не изменится. Тогда у планировщика появляется возможность полноценно «переставлять» эти соединения на этапе поиска оптимального плана.Вышеописанные приёмы помогут ускорить выполнение запросов, уменьшить потребление программных ресурсов и избежать возможных ошибок при переводе хранимых процедур на СУБД PostgreSQL в рамках импортозамещения.
Номер журнала Вестник науки №6 (75) том 2
Ссылка для цитирования:
Яковлев М.В., Обломов И.А. ИССЛЕДОВАНИЕ СПОСОБОВ ОПТИМИЗАЦИИ КОДА ПРИ ПЕРЕВОДЕ ХРАНИМЫХ ПРОЦЕДУР НА POSTGRESQL В РАМКАХ ИМПОРТОЗАМЕЩЕНИЯ // Вестник науки №6 (75) том 2. С. 1653 - 1658. 2024 г. ISSN 2712-8849 // Электронный ресурс: https://www.вестник-науки.рф/article/15737 (дата обращения: 16.05.2025 г.)
Вестник науки © 2024. 16+