Исследование эффективности способов написания SQL запросов с использованием СТЕ и подзапросов



Любая база данных существует для хранения данных и предоставления доступа к этим данным пользователю. Информацию из базы пользователь получает с помощью запроса.

Запрос — средство поиска данных в базе из одной или нескольких таблиц по определенному пользовательскому условию.

Вложенный запрос— это запрос, который используется внутри другой запроса.

Чтобы улучшить читабельность запросов, включающих в себя подзапросы, применяются обобщенные табличные выражения или commontableexpression— CTE.

[ WITH [ ,...n ] ]

::=

expression_name [ ( column_name [ ,...n ] ) ]

AS

( CTE_query_definition )

После объявления CTE может применяться в тех же секциях, что и вложенные запросы (SELECT, FROM, WHERE, JOIN).

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

Использование CTE позволяет выполнять рекурсивные запросы, что может быть также удобно при работе с базой данных.

Целю работы является выяснить, влияет ли использование CTE на время выполнения запроса.

Для проведения исследования будет использована база данных, таблицы которой содержат не менее 1000000 записей. Будет учитываться усредненное время многократно выполнения аналогичных запросов без применения и с применением CTE.

Диаграмма базы данных база данных представлена на рис.1.

Исследуется время выполнения следующих запросов:

1. Фамилии всех директоров и школьников, связанных с олимпиадами по самому популярному предмету.
2. Список всех школ, учащиеся которых заняли первое место.
3. Вывести все предметы, по которым не проводились олимпиады.
4. Призеров районных олимпиад из школ, в которых меньше 20 учеников.

Для примера на рис.2 и рис.3. приведены листинги первого запроса с использованием и без использования обобщенных табличных выражений.

Рис. 1. ER-диаграмма базы данных

Рис. 2. Первый запрос с применением CTE

Рис. 3. Первый запрос без применения CTE

Результаты усредненного измерения времени запросов представлены в табл.1.

Таблица 1

Результаты

№запроса	Без использования CTE, мс	С использованием CTE, мс
1	86	98
2	90	100
3	40	45
4	516	550
Среднее	183	198.25

Визуально время выполнения запросов представлено на рис.4.

Рис. 4. Время выполнения запросов

Определить, насколько процентов в среднем выполнение запроса без CTE быстрее, чем с ним, можно следующим образом:

(98−8698∗100 %+ 100−90100∗100 %+ 45−4045∗100 % +

+ 550−516550∗100 %)/4 = 9,84 %

Таким образом, по результатам проведенных тестов, в среднем, применение CTE увеличивает время выполнения запроса на 9,84 %. Таким образом, применение СTE упрощает читаемость запроса, но незначительно увеличивает время его выполнения.

Литература:

1. Язык запросов SQL [Электронный ресурс]. — Режим доступа: https://sql-language.ru/
2. Вложенные запросы (SQL Server) [Электронный ресурс]. — Режим доступа: https://docs.microsoft.com/ru-ru/sql/relational-databases/performance/
3. WITH обобщенное_табличное_выражение (Transact-SQL) [Электронный ресурс]. — Режим доступа: https://docs.microsoft.com/ru-ru/sql/t-sql/queries/with-common-table-expression-transact