Исследование эффективности способов написания SQL запросов с использованием СТЕ и подзапросов
Авторы: Коптенок Елизавета Викторовна, Сухарев Евгений Александрович, Савенко Арсений Витальевич, Трунников Максим Владиславович, Лагерева Наталья Валерьевна
Рубрика: Информатика и кибернетика
Опубликовано в Техника. Технологии. Инженерия №1 (15) февраль 2020 г.
Дата публикации: 28.01.2020
Статья просмотрена: 249 раз
Библиографическое описание:
Исследование эффективности способов написания SQL запросов с использованием СТЕ и подзапросов / Е. В. Коптенок, Е. А. Сухарев, А. В. Савенко [и др.]. — Текст : непосредственный // Техника. Технологии. Инженерия. — 2020. — № 1 (15). — С. 13-18. — URL: https://moluch.ru/th/8/archive/152/4832/ (дата обращения: 14.05.2024).
Любая база данных существует для хранения данных и предоставления доступа к этим данным пользователю. Информацию из базы пользователь получает с помощью запроса.
Запрос — средство поиска данных в базе из одной или нескольких таблиц по определенному пользовательскому условию.
Вложенный запрос— это запрос, который используется внутри другой запроса.
Чтобы улучшить читабельность запросов, включающих в себя подзапросы, применяются обобщенные табличные выражения или commontableexpression— CTE.
[ WITH
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
После объявления CTE может применяться в тех же секциях, что и вложенные запросы (SELECT, FROM, WHERE, JOIN).
В результате выполнения обобщенного табличного выражения создается временная таблица, к которой можно выполнять другие запросы.
Использование CTE позволяет выполнять рекурсивные запросы, что может быть также удобно при работе с базой данных.
Целю работы является выяснить, влияет ли использование CTE на время выполнения запроса.
Для проведения исследования будет использована база данных, таблицы которой содержат не менее 1000000 записей. Будет учитываться усредненное время многократно выполнения аналогичных запросов без применения и с применением CTE.
Диаграмма базы данных база данных представлена на рис.1.
Исследуется время выполнения следующих запросов:
- Фамилии всех директоров и школьников, связанных с олимпиадами по самому популярному предмету.
- Список всех школ, учащиеся которых заняли первое место.
- Вывести все предметы, по которым не проводились олимпиады.
- Призеров районных олимпиад из школ, в которых меньше 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 упрощает читаемость запроса, но незначительно увеличивает время его выполнения.
Литература:
- Язык запросов SQL [Электронный ресурс]. — Режим доступа: https://sql-language.ru/
- Вложенные запросы (SQL Server) [Электронный ресурс]. — Режим доступа: https://docs.microsoft.com/ru-ru/sql/relational-databases/performance/
- WITH обобщенное_табличное_выражение (Transact-SQL) [Электронный ресурс]. — Режим доступа: https://docs.microsoft.com/ru-ru/sql/t-sql/queries/with-common-table-expression-transact
Похожие статьи
Асинхронное выполнение SQL-запросов на языке...
В данной работе представлены результаты исследования и использования асинхронных SQL-запросов с помощью скриптового языка PHP, которые позволят значительно ускорить выполнение массивного SQL-запроса с помощью использования логических ядер процессора.
Применение секционирования таблиц для ускорения запросов...
На Рис. 2 представлено сравнение времени выполнения запросов данных к секционированным и несекционированным таблицам. Полученные результаты показывают, что запросы к секционированным таблицам обрабатываются примерно за то же время...
Методы выполнения запросов к хранилищу данных в Hadoop...
Классификация методов выполнения запросов в Hadoop и Spark. В реляционных базах данные хранятся в рамках заранее разработанной схемы, и единственный способ получить к ним доступ — это использовать язык структурированных запросов SQL.
Применение индексирования для ускорения запросов к базе...
Для начала, рассмотрим время выполнения запросов с выборкой только проиндексированного столбца. Будем рассматривать запросы на выборку по всей таблице с сортировкой, группировкой по числовому столбцу, выборку по условию (≈40 % от всей таблицы)...
Анализ производительности подходов обработки информации на...
Поток выполнения есть наименьшая единица обработки команд в приложении.
Данное время возможно было использовать на выполнение некоторой другой работы.
Другой подход для обработки запросов имеет структурные отличия в технической реализации.
Интеграция Telegram-ботов в информационных системах
– формируем запросы из базы информационных систем для отображения в боте; – экспортируем результаты запроса в формат *.csv
Язык SQL — это язык выполнения запросов для базы данных. В третьих, в структуре запросов (не только базы данных)...
Особенности реализации MVC-архитектуры в веб-приложениях
Пользователь направляет запрос в контроллер (в случае веб-приложений – это обращение по
Таким образом, запрос на получение данных предпочтительнее инициировать в контроллере, а в
Данный код эквивалентен вызову запроса: SELECT * FROM user WHERE id = ?
Использование апостериорного анализа данных для обнаружения...
Однако если рассматривать запрос уже после его выполнения, то цель запроса становится известной – СУБД возвратит данные
Следует отметить, что осуществление анализа запроса уже после его выполнения не является преградой для обеспечения предотвращения утечки...
Редактор языковых баз Wordnet с использованием гиперграфовой...
В таком случае наши запросы к такой базе данных будут содержать в себе вложенные команды select или join, и с каждой такой командой время выполнения запроса будет расти. Более наглядная иллюстрация подобной базы данных изображена на рис 4.
Аспекты написания XPath-запросов | Статья в журнале...
Запросы с относительным путем разрабатываются быстрее, и как правило, выходят более компакты чем запросы с абсолютным путем
Парсер, в свою очередь, проходит по узлам этого дерева и на каждом шаге получает данные из базы данных путем выполнения SQL...
Похожие статьи
Асинхронное выполнение SQL-запросов на языке...
В данной работе представлены результаты исследования и использования асинхронных SQL-запросов с помощью скриптового языка PHP, которые позволят значительно ускорить выполнение массивного SQL-запроса с помощью использования логических ядер процессора.
Применение секционирования таблиц для ускорения запросов...
На Рис. 2 представлено сравнение времени выполнения запросов данных к секционированным и несекционированным таблицам. Полученные результаты показывают, что запросы к секционированным таблицам обрабатываются примерно за то же время...
Методы выполнения запросов к хранилищу данных в Hadoop...
Классификация методов выполнения запросов в Hadoop и Spark. В реляционных базах данные хранятся в рамках заранее разработанной схемы, и единственный способ получить к ним доступ — это использовать язык структурированных запросов SQL.
Применение индексирования для ускорения запросов к базе...
Для начала, рассмотрим время выполнения запросов с выборкой только проиндексированного столбца. Будем рассматривать запросы на выборку по всей таблице с сортировкой, группировкой по числовому столбцу, выборку по условию (≈40 % от всей таблицы)...
Анализ производительности подходов обработки информации на...
Поток выполнения есть наименьшая единица обработки команд в приложении.
Данное время возможно было использовать на выполнение некоторой другой работы.
Другой подход для обработки запросов имеет структурные отличия в технической реализации.
Интеграция Telegram-ботов в информационных системах
– формируем запросы из базы информационных систем для отображения в боте; – экспортируем результаты запроса в формат *.csv
Язык SQL — это язык выполнения запросов для базы данных. В третьих, в структуре запросов (не только базы данных)...
Особенности реализации MVC-архитектуры в веб-приложениях
Пользователь направляет запрос в контроллер (в случае веб-приложений – это обращение по
Таким образом, запрос на получение данных предпочтительнее инициировать в контроллере, а в
Данный код эквивалентен вызову запроса: SELECT * FROM user WHERE id = ?
Использование апостериорного анализа данных для обнаружения...
Однако если рассматривать запрос уже после его выполнения, то цель запроса становится известной – СУБД возвратит данные
Следует отметить, что осуществление анализа запроса уже после его выполнения не является преградой для обеспечения предотвращения утечки...
Редактор языковых баз Wordnet с использованием гиперграфовой...
В таком случае наши запросы к такой базе данных будут содержать в себе вложенные команды select или join, и с каждой такой командой время выполнения запроса будет расти. Более наглядная иллюстрация подобной базы данных изображена на рис 4.
Аспекты написания XPath-запросов | Статья в журнале...
Запросы с относительным путем разрабатываются быстрее, и как правило, выходят более компакты чем запросы с абсолютным путем
Парсер, в свою очередь, проходит по узлам этого дерева и на каждом шаге получает данные из базы данных путем выполнения SQL...
