Как изменять критерии запросов сгенерированных мастером sql
Перейти к содержимому

Как изменять критерии запросов сгенерированных мастером sql

  • автор:

Изменение задания

В Управляемом экземпляре Azure SQL в настоящее время поддерживается большинство функций агента SQL Server (но не все). Подробные сведения см. в статье Различия в T-SQL между Управляемым экземпляром SQL Azure и SQL Server.

В этой статье описано, как изменять свойства заданий агента Microsoft SQL Server в SQL Server с помощью среды SQL Server Management Studio, Transact-SQL или управляющих объектов SQL Server.

Перед началом

Ограничения

Главное задание агент SQL Server не может быть предназначено как на локальных, так и на удаленных серверах.

Безопасность

Если пользователь не является членом предопределенной роли сервера sysadmin , он может изменять только свои собственные задания. Дополнительные сведения см. в разделе Обеспечение безопасности агента SQL Server.

Использование среды SQL Server Management Studio

Изменение задания
  1. В обозреватель объектов подключитесь к экземпляру ядро СУБД SQL Server, а затем разверните этот экземпляр.
  2. Разверните узел Агент SQL Server, выберите раздел Задания, щелкните правой кнопкой мыши задание, которое нужно изменить, и выберите пункт Свойства.
  3. В диалоговом окне Свойства задания обновите свойства, шаги, расписание, предупреждения и уведомления задания, воспользовавшись соответствующими страницами.

Использование Transact-SQL

Изменение задания
  1. В обозревателе объектов подключитесь к экземпляру компонента Database Engine и разверните его.
  2. На панели инструментов нажмите кнопку Создать запрос.
  3. В окне запроса используйте следующие системные хранимые процедуры для изменения задания.
    • Выполните процедуру sp_update_job (Transact-SQL) , чтобы изменить атрибуты задания.
    • Выполните процедуру sp_update_schedule (Transact-SQL) , чтобы изменить сведения о планировании для определения задания.
    • Выполните процедуру sp_add_jobstep (Transact-SQL) , чтобы добавить новые шаги к заданию.
    • Выполните процедуру sp_update_jobstep (Transact-SQL) , чтобы изменить существующие шаги задания.
    • Выполните процедуру sp_delete_jobstep (Transact-SQL) , чтобы удалить шаг задания.
    • Дополнительные хранимые процедуры для изменения любого главного задания агента SQL Server.
      • Выполните процедуру sp_delete_jobserver (Transact-SQL) , чтобы удалить сервер, сопоставленный с заданием.
      • Выполните процедуру sp_add_jobserver (Transact-SQL) , чтобы сопоставить сервер с текущим заданием.

Использование управляющих объектов SQL Server

Изменение задания

Воспользуйтесь классом Job на любом языке программирования, таком как Visual Basic, Visual C# или PowerShell. Дополнительные сведения см. в статье Управляющие объекты SQL Server (SMO).

Как изменять критерии запросов сгенерированных мастером sql

Как изменять критерии запросов сгенерированных мастером sql

Практически любой разработчик приложений баз данных сталкивается с необходимостью переделки ранее написанных SQL-запросов. При этом обычно преследуются две цели: во-первых — оптимизация времени выполнения запроса, во-вторых — улучшение дизайна запроса. Этот процесс подпадает под определение одной из основных практик экстремального программирования — рефакторинга (улучшения качества кода без изменения его функциональности). Основная масса литературы по рефакторингу посвящена переделке кодов программ, написанных на алгоритмических языках, и касается, как правило, объектно-ориентированных аспектов программирования. Целью данной статьи является попытка описания практики рефакторинга для SELECT-запросов языка SQL.

Признаки необходимости модификации запросов

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

1-ый признак

Код может быть «плохо оформлен», в этом случае его сложно понимать, соответственно — сложно переделывать. Для разработчика это должно означать несоответствие оформления запроса некоторым правилам, стандарту кодирования. Если не рассматривать правила именований объектов баз данных (считается, что схему данных изменить нельзя), то стандарт кодирования должен описывать, как должен быть отформатирован запрос (отступы, пробелы, длина строк…) и как должны именоваться объявляемые в его рамках переменные и алиасы (псевдонимы). Сюда же можно отнести требование использования только стандартных ключевых слов языка SQL и только в несокращенной форме.

Наглядно пользу форматирования кода можно продемонстрировать на следующем примере — листинг 1.

Листинг 1. Пример неформатированного запроса.

Тот же запрос после обработки может выглядеть так:

Листинг 2. Запрос после проведения форматирования.

2-ой признак

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

Устранение этих двух недостатков (1-го и 2-го признака), скорее всего не изменит скорость выполнения запроса, но поможет лучшему пониманию его структуры и упростит внесение дальнейших изменений.

Следующие признаки «плохих» запросов будут связаны уже с их структурой. Поскольку все они определяются через наличие некоторых конструкций языка SQL, то воспринимать их нужно с оговоркой, что использование данных конструкций в запросе не является безальтернативным и от них можно избавиться.

3-ий признак

Невозможность визуального представления запроса. В качестве инструмента проверки на соответствие данному признаку можно предложить Query Designer из MS SQL Server. Наверное очевидно, что визуальное представление запроса помогает пониманию его структуры. Состав критичных конструкций языка SQL для этого признака можно взять из документации по SQL Server (Books Online, статья «Query Designer Considerations for SQL Server Databases»). В частности, таковой является конструкция UNION.

4-ый признак

Наличие в запросе медленно работающих конструкций. В качестве примера можно привести использование связанных подзапросов. Избавление от подзапросов помимо увеличения скорости выполнения запроса, в большинстве случаев улучшит его читаемость. Предложения UNION также относятся к медленно работающим, т.к. в них требуется отбрасывать избыточные дубликаты [3].

5-ый признак
  • предложение TOP;
  • предложение ORDER BY
  • внешние соединения
  • использование ключевого слова DISTINCT

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

Методы модификации запросов

Для минимизации риска Мартин Фаулер[2] рекомендует проводить рефакторинг «маленькими шажочками», осуществляя проверку результата после каждого из них. Часть его книги составляет каталог методов рефакторинга — попытка классифицировать и описать наиболее часто встречающиеся модификации кода (предназначенные, прежде всего, для объектно-ориентированного программирования). Подобный каталог можно составить и для SQL. Ниже, в качестве примера, приведены четыре метода рефакторинга для наиболее простых ситуаций.

1. Выделение пользовательской функции

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

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

Такая запись уже не требует написания комментария (лучшая документация для исходного кода — это он сам).

2. Выделение представления

Это преобразование может быть оправдано как для повторяющегося участка кода, встречающегося в группе запросов, так и для отдельного запроса. Информативное имя представления также повысит читаемость кода. Особенностью данной модификации является то, что выделяемый участок кода не будет неразрывным в исходном запросе. В листинге 3 цветовым выделением обозначены фрагменты кода, которые необходимо перенести в представление, содержащее информацию таблиц Order и OrderDetails.

Листинг 3. Фрагменты кода, формирующие представление.

Эта особенность требует проведения значительного количества мелких действий при осуществлении подобного преобразования вручную.

3. Избавление от подзапросов

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

Листинг 4. Простейший пример использования связанного подзапроса

Того же результата можно добиться и без использования подзапроса:

Листинг 5. Вариант модификации запроса из листинга 4.

При рассмотрении планов выполнения запросов, видно, что планы выполнения обоих запросов практически одинаковые. План для исходного запроса на одно действие короче, но в процентном соотношении различие составляет менее 1%. Здесь нужно принимать во внимание, что оптимизаторы современных СУБД достаточно эффективно «разгоняют» простые запросы со связанными подзапросами. Для более сложных запросов разница может быть существеннее.

Большинство ситуаций, встречающихся в реальных запросах, сложнее, тем не менее, в модифицированном запросе должен будет появиться JOIN с таблицей (таблицами) из подзапросов. При использовании агрегатных функций в подзапросе — они перейдут в основной запрос, к которому будет добавлено предложение GROUP BY (см. листинги 6,7).

Листинг 6. Пример SELECT-команды с агрегатной функцией в подзапросе

Листинг 7. Вариант модификации запроса из листинга 6.

4. Избавление от UNION

Наиболее простым примером использования UNION, от которого можно избавиться, является цепочка предложений UNION, основанных на одной и той же базовой таблице. Примеры исходного запроса и преобразованного приведены в листингах 8,9.

Листинг 8. Запрос с UNION на основе одной базовой таблице

Листинг 9. Модифицированный запрос (замена UNION на OR)

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

Рис. 5. План выполнения запроса после замены UNION на OR

В некоторых ситуациях в использовании UNION нет необходимости и достаточно более производительного предложения UNION ALL (при его выполнении не происходит объединения повторяющихся строк).

Программные средства рефакторинга SQL

Действия программиста, выполняемые при проведении рефакторинга, можно поделить на 3 группы: форматирование кода, непосредственное применение методов рефакторинга — преобразований кода и проверку того, что конечный запрос не отличается от исходного по набору возвращаемых данных. Частично эти действия могут быть автоматизированы, точно так же, как они автоматизируются для ряда объектно-ориентированных языков программирования. Ниже приведен краткий обзор средств, которые могут быть использованы для рефакторинга SQL-запросов. Обзор не претендует на полноту и ориентирован, в основном, на программные инструменты, входящие в состав средств разработки компании Microsoft или дополняющие их.

1. Форматирование кода и визуальное представление

Существует довольно большой набор средств форматирования SQL-кода. В их числе: Query Designer, входящий в состав SQL Server Management Studio — для форматирования достаточно скопировать запрос и активизировать панель визуального представления. Из средств сторонних разработчиков можно привести в качестве примера SQL Refactor компании Red Gate Software и QueryCommander — бесплатный инструмент с открытым кодом. Основные различия указанных средств — в разных стандартах форматирования, к которым они приводят исходные запросы и в возможности изменить это параметрами настроек.

2. Программы для рефакторинга запросов

Автору статьи не удалось найти средства, автоматизирующие непосредственно проведение рефакторинга SQL-кода. Единственный программный продукт, который претендует на эту роль (по крайней мере — по названию) — SQL Refactor, предлагает только возможности форматирования кода, выделение части SQL-кода в качестве хранимой процедуры и разбиение предложения CREATE TABLE на две части (с разделением уже существующих колонок между двумя таблицами). Никаких сервисов по модификации структуры запросов не предлагается.

3. Unit-тестирование для SQL-запросов

В экстремальном программировании практика проведения рефакторинга тесно связана с практикой написания тестов модулей, или unit-тестов. Основная аргументация связи этих практик — невозможность смело переделывать работающий код, если нет механизмов быстрой проверки того, что внесенные изменения его не испортили. В принципе, вполне возможно написание unit-тестов средствами, приспособленными для тестирования программ алгоритмических языков (jUnit, csUnit, встроенными механизмами написания unit-тестов в MS Visual Studio 2005). В случаях, если запросы пишутся для серверных компонентов, обеспечивающих доступ к базам данных, эти тесты могут вполне органично вписываться в общую систему тестирования этих компонентов. При этом необходимо учитывать особенность тестирования запросов: нужны механизмы сравнения наборов данных, а не отдельных значений. Удалось найти всего три средства построения unit-тестов, связанных с тестированием SQL-запросов: SQLUnit, DbUnit и TSQLUnit. Все три средства являются узкоспециализированными: DbUnit и SQLUnit исходно ориентированы на Java-разработчиков, TSQLUnit — требует написания unit-тестов на языке Python. Из них только в DbUnit есть функция сравнения наборов данных, в двух оставшихся — такая возможность отсутствует.

Заключение

Подход к проведению модификации существующего кода вполне оправдывает себя в применении к SQL-запросам. К сожалению, на сегодняшний день выбор средств автоматизации рефакторинга SQL весьма скуден, а имеющиеся средства недостаточно функциональны. Это скорее всего связано с тем фактом, что XP зародилось в среде разработчиков, программирующих преимущественно на объектно-ориентированных языках и специфичностью характера проведения рефакторинга для SQL-кода.

Гайд по оптимизации SQL запросов

Гайд по оптимизации SQL запросов

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

В этой статье хочу рассказать о некоторых приемах, позволяющих значительно ускорить работу SQL. Давайте начнем оптимизацию SQl запрсов.

1. Используйте конкретные имена столбцов после оператора select, вместо «*» – это позволит увеличить быстроту отработки запроса и уменьшению сетевого трафика.

2. Сведите к минимуму использование подзапросов.

выглядит значительно хуже на фоне аналогичного запроса:

3. Используйте оператор IN аккуратно, поскольку на практике он имеет низкую производительность и может быть эффективен только при использовании критериев фильтрации в подзапросе.

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

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

5. При соединении таблиц EXIST предпочтительнее distinct (таблицы отношения «один-ко-многим»).

6. Избыточность при работе с SQL – это критичная необходимость, используйте в разделе WHERE как можно больше ограничивающих условий.

Например, если указан

WHERE Column_А=Column_В and Column_А=425

вы сможете вывести результат, где Column_В=425, однако при задании условий

WHERE Column_А=Column_В and Column_B=Column_C

оператор не сможет определить, что Column_A=Column_C.

7. Пишите простые запросы. Больше упрощайте.

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

8. Помните, что одного и того же результата можно добиться разными способами.

Например, оператор MINUS выполняется гораздо быстрее, чем запросы с оператором WHERE NOT EXIST. Запрос с данным оператором в самом общем виде выглядит следующим образом:

Select worker_id From workers MINUS Select worker_id From orders

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

9. Оформляйте повторяющиеся коды в пользовательскую процедуру. Это может значительно ускорить работу, уменьшить сетевой трафик.

10. Выберите правильный тип данных для столбца

Каждый столбец таблицы в SQL имеет связанный тип данных. Вы можете выбирать из целых чисел, дат, переменных, логических значений, текста и т.д. При разработке важно выбрать правильный тип данных. Числа должны быть числового типа, даты должны быть датами и т.д. Это чрезвычайно важно для индексации.

Давайте посмотрим на пример ниже.

Вышеупомянутый запрос извлекает идентификатор и имя сотрудника с идентификатором 13412 . Что, если тип данных для employeeID — строка? Вы можете столкнуться с проблемами при использовании индексации, поскольку это займет много времени, когда это должно быть простое сканирование.

11: Табличные переменные и объединения

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

Давайте посмотрим на пример соединения:

Табличные переменные — это локальные переменные, которые временно хранят данные и обладают всеми свойствами локальных переменных. Не используйте табличные переменные в объединениях, как SQL видит их как одну строку. Несмотря на то, что они быстрые, табличные переменные плохо работают в соединениях.

12. Используйте условное предложение WHERE

Условные предложения WHERE используются для подмножества. Допустим, у вас есть такая ситуация:

13: используйте SET NOCOUNT O

При выполнении операций INSERT , SELECT , DELETE и UPDATE , используйте SET NOCOUNT ON . SQL всегда возвращает соответствующее количество строк для таких операций, поэтому, когда у вас есть сложные запросы с большим количеством соединений, это может повлиять на производительность.

С SET NOCOUNT ON SQL не будет подсчитывать затронутые строки и улучшить производительность.

В следующем примере мы предотвращаем отображение сообщения о количестве затронутых строк.

14: Избегайте ORDER BY, GROUP BY и DISTINCT

Использование ORDER BY , GROUP BY и DISTINCT только в случае необходимости. SQL создает рабочие таблицы и помещает туда данные. Затем он организует данные в рабочей таблице на основе запроса и затем возвращает результаты.

15. Полностью уточняйте имена объектов базы данных

Цель использования полностью определенных имен объектов базы данных — устранить двусмысленность. Полное имя объекта выглядит так:

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

Поэтому вместо использования такого оператора:

Вам следует использовать:

16. Узнайте, как полностью защитить свой код

Базы данных хранят всевозможную информацию, что делает их основными целями атак. Распространенные атаки включают SQL-инъекции, когда пользователь вводит инструкцию SQL вместо имени пользователя и извлекает или изменяет вашу базу данных. Примеры SQL-инъекций:

Допустим, у вас есть это, вы textuserIDполучите ввод от пользователя. Вот как это может пойти не так:

Поскольку 1=1 всегда верно, он будет извлекать все данные из таблицы Users.

Вы можете защитить свою базу данных от SQL-инъекций, используя параметризованные операторы, проверки ввода, очистку ввода и т. Д. Как вы защищаете свою базу данных, зависит от СУБД. Вам нужно будет разобраться в своей СУБД и ее проблемах безопасности, чтобы вы могли писать безопасный код.

17: используйте LAG и LEAD для последовательных строк

Функция LAG позволяет запрашивать более одной строки в таблице, не вступая в таблицу к себе. Он возвращает значения из предыдущей строки таблицы.

Функция LEAD делает то же самое, но и для следующей строки.

Отказ от использования самостоятельных соединений повышает производительность, поскольку уменьшается количество операций чтения. Но, вы должны проверить, как LEAD и LAG влияют на производительность запросов.

Таким образом, рассмотренные нами моменты работы с SQL операторами и запросами значительно ускоряют работу с СУБД.

Оптимизация производительности запросов SQL Server

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

Даже если сервер базы данных использует самое мощное аппаратное обеспечение на свете, горсточка плохо себя ведущих запросов может плохо отразиться на его производительности. Фактически, даже один неудачный запрос (иногда их называют «вышедшими из-под контроля») может вызвать серьезное снижение производительности базы данных.

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

Анализ планов выполнения

Обычно при настройке отдельных запросов стоит начать с рассмотрения плана выполнения запроса. В нем описана последовательность физических и логических операций, которые SQL ServerTM использует для выполнения запроса и вывода желаемого набора результатов. План выполнения создает в фазе оптимизации обработки запроса компонент ядра базы данных, который называется оптимизатором запросов, принимая во внимание много различных факторов, например использованные в запросе предикаты поиска, задействованные таблицы и условия объединения, список возвращенных столбцов и наличие полезных индексов, которые можно использовать в качестве эффективных путей доступа к данным.

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

Существует несколько способов извлечения плана выполнения запроса:

Хотя XML-представление плана выполнения не самый удобный для пользователя формат, эта команда позволяет использовать самостоятельно написанные процедуры и служебные программы для анализа, поиска проблем с производительностью и практически оптимальных планов. Представление на базе XML можно сохранить в файл с расширением sqlplan, открывать в Management Studio и создавать графическое представление. Кроме того, эти файлы можно сохранять для последующего анализа без необходимости воспроизводить их каждый раз, как этот анализ понадобится. Это особенно полезно для сравнения планов и выявления возникающих со временем изменений.

Оценка стоимости выполнения

Первое, что нужно понять — это как генерируются планы выполнения. SQL Server использует оптимизатор запроса на основе стоимости, то есть пытается создать план выполнения с минимальной оценочной стоимостью. Оценка производится на основе статистики распределения доступных оптимизатору на момент проверки каждой использованной в запросе таблицы данных. Если такой статистики нет или она уже устарела, оптимизатору запроса не хватит необходимой информации и оценка, скорее всего, окажется неточной. В таких случаях оптимизатор переоценит или недооценит стоимость выполнения различных планов и выберет не самый оптимальный.

Существует несколько распространенных, но неверных представлений о приблизительной стоимости выполнения. Особенно часто считается, что приблизительная стоимость выполнения является хорошим показателем того, сколько времени займет выполнение запроса и что эта оценка позволяет отличить хорошие планы от плохих. Это неверно. Во-первых, есть много документов касающихся того, в каких единицах выражается приблизительная стоимость и имеют ли они непосредственное отношение ко времени выполнения. Во-вторых, поскольку значение это приблизительно и может оказаться ошибочным, планы с большими оценочными затратами иногда оказываются значительно эффективнее с точки зрения ЦП, ввода/вывода и времени выполнения, несмотря на предположительно высокую стоимость. Это часто случается с запросами, где задействованы табличные переменные. Поскольку статистики по ним не существует, оптимизатор запросов часто предполагает, что в таблице есть всего одна строка, хотя их во много раз больше. Соответственно, оптимизатор выберет план на основе неточной оценки. Это значит, что при сравнении планов выполнения запросов не следует полагаться только на приблизительную стоимость. Включите в анализ параметры STATISTICS I/O и STATISTICS TIME, чтобы определить истинную стоимость выполнения в терминал ввода/вывода и времени работы ЦП.

Здесь стоит упомянуть об особом типе плана выполнения, который называется параллельным планом. Такой план можно выбрать при отправке на сервер с несколькими ЦП запроса, поддающегося параллелизации (В принципе, оптимизатор запроса рассматривает использование параллельного плана только в том случае, если стоимость запроса превышает определенное настраиваемое значение.) Из-за дополнительных расходов на управление несколькими параллельными процессами выполнения, связанными с распределением заданий, выполнением синхронизации и сведением результатов, параллельные планы обходятся дороже, что отражает их приблизительная стоимость. Тогда чем же они предпочтительнее более дешевых, не параллельных планов? Благодаря использованию вычислительной мощности нескольких ЦП параллельные планы обычно выдают результат быстрее стандартных. В зависимости от конкретного сценария (включая такие переменные, как доступность ресурсов с параллельной нагрузкой других запросов) эта ситуации для кого-то может оказаться желательной. Если это ваш случай, нужно будет указать, какие из запросов можно выполнять по параллельному плану и сколько ЦП может задействовать каждый. Для этого нужно настроить максимальную степень параллелизма на уровне сервера и при необходимости настроить обход этого правила на уровне отдельных запросов с помощью параметра OPTION (MAXDOP n).

Анализ плана выполнения

Теперь рассмотрим простой запрос, его план выполнения и некоторые способы повышения производительности. Предположим, что я выполняю этот запрос в Management Studio с включенным параметром включения реального плана выполнения в примере базы данных Adventure Works SQL Server 2005:

В итоге я вижу план выполнения, изображенный на рис. 1 . Этот простой запрос вычисляет общее количество заказов, размещенных каждым клиентом в базе данных Adventure Works. Глядя на этот план, вы видите, как ядро базы данных обрабатывает запросы и выдает результат. Графические планы выполнения читаются сверху вниз, справа налево. Каждый значок соответствует выполненной логической или физической операции, а стрелки — потокам данных между операциями. Толщина стрелок соответствует количеству переданных строк (чем толще, тем больше). Если поместить курсор на один из значков оператора, появится желтая подсказка (такая, как на рис. 2 ) со сведениями о данной операции.

Рис. 1 Пример плана выполнения

Рис. 2 Сведения об операции

Глядя на операторы, можно анализировать последовательность выполненных этапов:

В созданном на моем ноутбуке плане выполнения приблизительная стоимость равнялась 3,31365 (как видно на рис. 3 ). При выполнении с включенной функцией STATISTICS I/O ON отчет по запросу содержал упоминание о 1,388 логических операциях чтения из трех задействованных таблиц. Процентное значение под каждым оператором — это его стоимость в процентах от общей приблизительной стоимости всего плана. На плане на рис. 1 видно, что большая часть общей стоимости связана со следующими тремя операторами: сканирование кластеризованного индекса таблицы Sales.SalesOrderDetail и два оператора совпадения значений хэша. Перед тем как приступить к оптимизации, хотелось отметить одно очень простое изменение в моем запросе, которое позволило полностью устранить два оператора.

Рис. 3 Общая приблизительная стоимость выполнения запроса

Поскольку я возвращал из таблицы Sales.Customer только столбец CustomerID, и тот же столбец включен в таблицу Sales.SalesOrderHeaderTable в качестве внешнего ключа, я могу полностью исключить из запроса таблицу Customer без изменения логического значения или результата нашего запроса. Для этого используется следующий код:

Получился другой план выполнения, который изображен на рис. 4 .

Рис. 4 План выполнения после устранения из запроса таблицы Customer

Полностью устранены две операции — сканирование кластеризированного индекса таблицы Customer и слияние Customer и SalesOrderHeader, а совпадение значений хэша заменено на куда более эффективную операцию слияния. При этом для слияния таблиц SalesOrderHeader и SalesOrderDetail нужно вернуть строки обеих таблиц, рассортированные по общему столбцу SalesOrderID. Для этого оптимизатор кластера выполнил сканирование кластеризованного индекса таблицы SalesOrderHeader вместо того, чтобы использовать сканирование некластеризованного индекса, который был бы дешевле с точки зрения ввода/вывода. Это хороший пример практического применения оптимизатора запроса, поскольку экономия, получающаяся при изменении физического способа слияния, оказалась больше дополнительной стоимости ввода/вывода при сканировании кластеризованного индекса. Оптимизатор запроса выбрал получившуюся комбинацию операторов, поскольку она дает минимально возможную примерную стоимость выполнения. На моем компьютере, несмотря на то, что количество логических считываний возросло (до 1,941), временные затраты ЦП стали меньше, и приблизительная стоимость выполнения данного запроса упала на 13 процентов (2,89548).

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

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

Создав этот индекс и выполнив тот же запрос, я получил новый план, который изображен на рис. 5 .

Рис. 5 Оптимизированный план выполнения

Сканирование кластеризованного индекса таблицы SalesOrderDetail заменено некластеризованным сканированием с заметно меньшими затратами на ввод/вывод. Кроме того, я исключил один из операторов вычисления стоимости, умноженной на коэффициент, поскольку в моем индексе уже есть вычисленное значение столбца LineTotal. Теперь приблизительная стоимость плана выполнения составляет 2,28112 и при выполнении запроса производится 1,125 логических считываний.

Упражнение. Запрос заказа покупателя

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

Ответ. Я предложил рассчитать оптимальный индекс покрытия для создания таблицы Sales.SalesOrderHeader на примере запроса из моей статьи. При этом нужно в первую очередь отметить, что запрос использует только два столбца из таблицы: CustomerID и SalesOrderID. Если вы внимательно прочли эту статью, то заметили, что в случае с таблицей SalesOrderHeader индекс покрытия запроса уже существует, это индекс CustomerID, который косвенно содержит столбец SalesOrderID, являющийся ключом кластеризации таблицы.

Конечно, я объяснял и то, почему оптимизатор запроса не стал использовать этот индекс. Да, можно заставить оптимизатор запроса использовать этот индекс, но это решение было бы менее эффективным, чем существующий план с операторами сканирования кластеризованного индекса и слияния. Дело в том, что оптимизатор запроса пришлось бы принудить либо выполнить дополнительную операцию сортировки, необходимую для использования слияния, либо откатиться назад, к использованию менее эффективного оператора совпадения значений хэша. В обоих вариантах приблизительная стоимость выполнения выше, чем в существующем плане (версия с оператором сортировки работала бы особенно плохо), поэтому оптимизатор запроса не будет их использовать без принуждения. Итак, в данной ситуации лучше сканирования кластеризованного индекса будет работать только некластеризованный индекс в столбцах SalesOrderID, CustomerID. При этом нужно отметить, что столбцы должны идти именно в таком порядке:

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

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

Индекс покрытия

Индекс, созданный из таблицы SalesOrderDetail, представляет собой так называемый «индекс покрытия». Это некластеризованный индекс, где содержатся все столбцы, необходимые для выполнения запроса. Он устраняет необходимость сканирования всей таблицы с помощью операторов сканирования таблицы или кластеризованного индекса. По сути индекс представляет собой уменьшенную копию таблицы, где содержится подмножество ее столбцов. В индекс включаются только столбцы, которые необходимы для ответа на запрос или запросы, то есть только то, что «покрывает» запрос.

Создание индексов покрытия наиболее частых запросов — один из самых простых и распространенных способов тонкой настройки запроса. Особенно хорошо он работает в ситуациях, когда в таблице несколько столбцов, но запросы часто ссылаются только на некоторые из них. Создав один или несколько индексов покрытия, можно значительно повысить производительность соответствующих запросов, так как они будут обращаться к заметно меньшему количеству данных и, соответственно, количество вводов/выводов сократится. Тем не менее, поддержка дополнительных индексов в процессе модификации данных (операторы INSERT, UPDATE и DELETE) подразумевает некоторые расходы. Следует четко определить, оправдывает ли увеличение производительности эти дополнительные расходы. При этом учтите характеристики своей среды и соотношение количества запросов SELECT и изменений данных.

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

С моим примером запроса можно сделать еще кое-что. Создав индекс покрытия таблицы SalesOrderHeader, можно дополнительно оптимизировать запрос. При этом будет использовано сканирование некластеризованного индекса вместо кластеризованного. Предлагаю вам выполнить это упражнение самостоятельно. Попробуйте получить определение индекса: выясните, наличие каких столбцов превратит его в индекс покрытия данного запроса и повлияет ли порядок столбцов на производительность. Решение см. в боковой панели «Упражнение. Запрос заказа покупателя».

Индексированные представления

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

Обратите внимание на параметр WITH SCHEMABINDING, без которого невозможно создать индекс такого представления, и функцию COUNT_BIG(*), которая потребуется в том случае, если в нашем определении индекса содержится обобщенная функция (в данном случае SUM). Создав это представление, я могу создать и индекс:

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

Если перезапустить запрос, то результат будет зависеть от используемой версии SQL Server. В версиях Enterprise или Developer оптимизатор автоматически сравнит запрос с определением индексированного представления и использует это представление, вместо того чтобы обращаться к исходной таблице. На рис. 6 приведен пример получившегося плана выполнения. Он состоит из одной-единственной операции — сканирования кластеризованного индекса, который я создал на основе представления. Приблизительная стоимость выполнения составляет всего 0,09023 и при выполнении запроса производится 92 логических считывания.

Рис. 6 План выполнения при использовании индексированного представления

Это индексированное представление можно создавать и использовать и в других версиях SQL Server, но для получения аналогичного эффекта необходимо изменить запрос и добавить прямую ссылку на представление с помощью подсказки NOEXPAND, примерно так:

Как видите, правильно использованные индексированные представления могут оказаться очень мощными орудиями. Лучше всего их использовать в оптимизированных запросах, выполняющих агрегирование больших объемов данных. В версии Enterprise можно усовершенствовать много запросов, не изменяя кода.

Поиск запросов, нуждающихся в настройке

Как я определяют, что запрос стоит настроить? Я ищу часто выполняемые запросы, возможно, с невысокой стоимостью выполнения в отдельном случае, но в целом более дорогие, чем крупные, но редко встречающиеся запросы. Это не значит, что последние настраивать не нужно. Я просто считаю, что для начала нужно сосредоточиться на более частых запросах. Так как же их найти?

К сожалению, самый надежный метод довольно сложен и предусматривает отслеживание всех выполненных запросов к серверу с последующий группировкой по подписям. При этом текст запроса с реальными значениями параметров заменяется на замещающий текст, который позволяет выбрать однотипные запросы с разными значениями. Подписи запроса создать тяжело, так что это сложный процесс. Ицик Бен-Ган (Itzik Ben-Gan) описывает решение с использованием пользовательских функций в среде CLR и регулярных выражений в своей книге «Microsoft SQL Server 2005 изнутри: запросы T-SQL».

Существует еще один метод, куда более простой, но не столь надежный. Можно положиться на статистику всех запросов, которая хранится в кэше плана выполнения, и опросить их с использованием динамических административных представлений. На рисунке 7 есть пример запроса текста и плана выполнения 20 запросов из кэша, у которых общее количество логических считываний оказалось максимальным. С помощью этого запроса очень удобно быстро находить запросы с максимальным количеством логических считываний, но есть и некоторые ограничения. Он отображает только запросы с планами, кэшированными на момент запуска. Не кэшированные объекты не отображаются.

Рис. 7 Поиск 20 самых дорогих с точки зрения ввода/вывода при считывании запросов.

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

Оптимизация SQL запросов для MS SQL Server с помощью индексов

Я тут пообещал поделиться опытом оптимизации SQL запросов. Мне достаточно много приходилось работать над оптимизацией и на работе не раз просили рассказать, как я оптимизирую запросы. Тут нет какого-то секрета и, хотя я пытался поделиться, это все же такой процесс, в котором мастерство приходит с опытом.

На блоге я уже несколько раз делился некоторыми отдельными случаями, но пришла пора все же более подробно рассмотреть эту тему.

Это вводная часть, где я расскажу в статье и покажу в видео (его можно найти в конце этой статьи) базовые вещи о индексах и статистике.

О индексах и статистике можно писать очень много и возможно будет еще статья и видео. А это такой вводный курс, чтобы показать базу.

Когда я узнаю о том, что на сервере есть медленно выполняющийся запрос, первое на что я смотрю – статистику io. Это такое слабое звено, позволяет быстро определить легкие проблемные места SQL запросов.

В SQL Server есть несколько вариантов получения данных о том, как выполняется запрос – но именно статистика получается очень легко и тут же показывает проблемные места в читабельном виде.

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

Давайте займемся тем, чем любят заниматься программисты по ночам – посмотрим на код. Итак, для вводного курса рассмотрим простой пример с запросом:

У меня в тестовой базе данных почти 200 тысяч записей и поиск по ней происходит мгновенно. Если посмотреть на время выполнения, то будет ноль секунд, что очень хорошо. Но давайте включим отображение статистики, и посмотрим на нее. Я всегда включаю статистику io и время time:

Теперь после выполнения SQL запроса в SQL Management Studio внизу окна будет появляться не только результат, но и на закладке Messages будет показана статистика выполнения:

В моем случае статистика выглядела так:

Первые четыре строчки – это время компиляции запроса и для такого простого особо проблем не должно возникнуть, а я уже выполнял запрос, поэтому и 0 секунд.

После этого идет статистика выполнения и тут нужно смотреть на количество сканирований и количество чтений (logical reads, physical reads и др). У нас сейчас запрос простой, который решается одним сканированием. Судя по количеству чтений, сканировалась абсолютно вся таблица.

Казалось бы, да и черт с ним, что сканируется вся таблица, если запрос выполняется так быстро – ноль секунд. И если запрос выполняется только один раз, то можно и забыть и забить. Но если сразу тысяча человек будет выполнять этот запрос и искать по разной фамилии? Даже при такой статистике нагрузка на базу данных будет серьезная, а если в базе будет миллион записей, то сервер может серьезно затормозить.

Для оптимизации нужно создать индекс, который ускоряет поиск:

Более подробно о создании индексов можно почитать здесь: Индексы в MS SQL Server и еще немного интересной информации о индексах здесь: опции индексов.

Здесь я только скажу, что при создании индексов на таблицу, где много данных и с большим количеством выполняемых запросов может стать проблемой. Создание индекса по умолчанию потребует блокировки, что может стать препятствием. Индекс может не создаваться, потому что данные заблокированы или сайт может лечь, если индекс будет долго создаваться. Чтобы этого не произошло, нужно добавлять опцию: (online = on)

Снова выполняем запрос и смотрим на статистику:

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

Попробуем взглянуть на план выполнения – графическое представление того, как сервер реально выполнял запрос. Для этого включаем опцию отображения плана – в меню Query выбираем Include Actual Execution Plan (или нажимаем Ctrl+M). Если теперь выполнить запрос, то появится еще одна вкладка – Execution Plan:

Здесь у нас две ветки и читать их нужно справа на лево. Самый правый блок – это то, с чего началось выполнение – Index Seak по индексу IX_Member_WSEmail. Наш простой запрос ищет данные по колонке WSEmail и эта колонка есть в индексе IX_Member_WSEmail, поэтому имеет смысл использовать его. И как мы уже увидели после создания индекса, результат как говориться на io. В индексе находятся индексируемые колонки и первичный ключ. Это все, что узнает сервер, когда находит строку по индексу. Но наш запрос выводит совершенно все колонки и чтобы найти оставшиеся данные, серверу приходится по первичному ключу находить их. Этот процесс быстрый – Key Lookup, потому что это первичный ключ, но он все же отнимает немного времени. И скоро мы увидим сколько. Получается, что серверу необходимо выполнить как бы две операции – поиск по индексу основного ключа, а потом по этому ключу найти данные колонок. А если выводить на экран только колонки WSEmail и первичный ключ MemberID? Эти данные уже есть индексе и второй Key Lookup не понадобиться. Посмотрим, как это будет выглядеть в статистике:

Статистика падает с 7 чтений до 3:

А план выполнения начинает выглядеть идеально просто:

Допустим, нам необходимо вывести на экран имя человека, в моем случае это колонка WSFirstName:

Теперь запросу нужно будет снова делать два поиска – чтобы найти первичный ключи, а потом по нему найти реальную строку, чтобы выцепить имя. И это в принципе не страшно, потому что поиск по первичному ключу занял всего 4 операции чтения, но что, если выводиться 1000 строк? Тогда уже будет 4000 операций. А если запрос у нас не такой простой и в нем потом еще есть left join на какую-то другую таблицу с именами, где, по имени храниться судьба человека (такой гороскоп по имени).

Можно создать индекс, который будет индексировать по email и по имени:

И хотя запрос фильтрует данные только по e-mail, этот индекс все же будет работать и позволит нам быстро найти данные, и в индексе будет уже имя и поэтому второй поиск уже не нужен будет. Круто, но не совсем эффективно. Имя меняется не часто и если мы по нему реально не ищем, то по нему индексировать смысла нет, но есть возможность сказать серверу, чтобы он хранил вместе с индексом еще и колонку WSFirstName, для этого есть такая фишка, как include:

Теперь данные индексируются только по колонке WSEmail, что нам и нужно, но вместе с индексом храниться не только первичный ключ, но и имя.

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

Еще один пример, который может выиграть от такого индекса:

Мы выводим все колонки и от второго поиска по первичному ключу всех данных не убежать. Все колонки включать в индекс смысла нет, потому что это превратить его практически в первичный, просто не кластерный. Но.. Когда сервер отфильтрует данные по WSEmail по первому индексу и найдет допустим 1000 строк, он может тут же сократить эти данные и проверить имя и результат сократиться до (допустим) 100 строк. То есть Key Lookup может выполняться только 100 раз. Если колонка WSFirstName не включена в индекс, то эту операцию придется уже делать 1000 раз.

Чтобы индексы работали наиболее эффективно, тип данных значения и колонки должно совпадать. Как видите, в моем случае я просто передаю строку в одинарной кавычки, как varchar, потому что колонка имеет тип именно varchar. Если попробовать передать nvarchar:

Теперь строка e-mail адреса передается в качестве nvarchar и это серьезно ударит по производительности. Изменился только тип строки, которую мы сравниваем, а посмотрите как обрушилась статистика:

Или вот такой пример, так обычно делают многие движки, когда выполняют запросы и примерно так будет выполнять запросы популярный Dapper или даже .NET фреймворк:

Когда вы в .NET выполняете запрос, то создается переменная и она передается запросу.

Когда я работал над сайтом регистрации продуктов для Sony, то допустил такую ошибку. На главной странице сайте есть автокомплитер, где пользователь может ввести код товара. Пока пользователь вводит, на заднем плане происходит поиск и когда я запустил этот сайт, то он нереально затормозил, хотя этот сайт не такой уж и популярный и по посещаемости самый слабый из всех, что я делал для Sony. Начали исследовать, а оказалось, что для этого проекта я перешел на Dapper, который по умолчанию все переменные делал nvarchar, а в базе данных у нас все было просто varchar (сайт только для США). В результате, даже небольшой нагрузки на сайт хватало для серьезного падения производительности. Пришлось хакать Dapper, чтобы он не создавал переменные Unicode, а делал их простыми varchar

Если тип колонки и искомого значения совпадают, то будет использоваться Index Seek. Если не совпадают, то Index Scan – сканирование по индексу. Что используется для поиска можно увидеть в Execution Plan

Серверу придется просканировать весь индекс. За счет того, что он занимает меньше места на диске, то поиск будет быстрее, чем сканирование по данным, но все же на много медленнее, чем могло быть при правильном использовании параметров.

Если ваш фреймворк косячит и передает неверно параметры, а у вас нет доступа к исходникам, чтобы исправить, то можно использовать вот такой финт ушами:

Здесь я с помощью cast привожу email переменную к правильному типу, чтобы он совпадал с типом данных колонки.

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

Похожие публикации:

  1. Как заполнить базу данных в sql server
  2. Как запустить файл sql на выполнение
  3. Как импортировать access в sql server
  4. Как назвать поле в sql

JPA и Hibernate — критерии, JPQL и HQL-запросы

В этом руководстве мы увидим, как использовать запросы JPA и Hibernate, а также разницу между запросами Criteria, JPQL и HQL. Критериальные запросы позволяют пользователю писать запросы без использования необработанного SQL. Наряду с запросами Criteria мы рассмотрим написание именованных запросов Hibernate и способы использования аннотации @Query в Spring Data JPA.

Прежде чем мы углубимся в это, мы должны отметить, что Hibernate Criteria API устарел, начиная с Hibernate 5.2. Поэтому в наших примерах мы будем использовать JPA Criteria API , поскольку это новый и предпочтительный инструмент для написания запросов Criteria. Итак, с этого момента мы будем называть его просто Criteria API.

2. Критериальные запросы

Criteria API помогает создавать объект запроса Criteria, применяя к нему различные фильтры и логические условия. Это альтернативный способ манипулирования объектами и возврата нужных данных из таблицы СУБД.

Метод createCriteria() из сеанса Hibernate возвращает экземпляр объекта сохраняемости для выполнения запроса критериев в приложении. Проще говоря, Criteria API создает запрос критериев, который применяет различные фильтры и логические условия.

2.1. Зависимости Maven​

Давайте возьмем последнюю версию эталонной зависимости JPA , которая реализует JPA в Hibernate, и добавим ее в наш pom.xml :

 >   >org.hibernate>   >hibernate-core>   >5.3.2.Final>  > 

2.2. Использование запросов и выражений с критериями​

В соответствии с условиями пользователя CriteriaBuilder контролирует результаты запроса . Он использует метод where() из CriteriaQuery , который предоставляет выражения CriteriaBuilder .

Давайте посмотрим на объект, который мы будем использовать в этой статье:

 public class Employee     private Integer id;   private String name;   private Long salary;    // standard getters and setters   > 

Давайте рассмотрим простой запрос критериев, который извлечет все строки «Сотрудник» из базы данных:

 Session session = HibernateUtil.getHibernateSession();   CriteriaBuilder cb = session.getCriteriaBuilder();   CriteriaQueryEmployee> cr = cb.createQuery(Employee.class);   RootEmployee> root = cr.from(Employee.class);  cr.select(root);    QueryEmployee> query = session.createQuery(cr);   ListEmployee> results = query.getResultList();  session.close();   return results; 

Приведенный выше запрос Criteria возвращает набор всех элементов. Давайте посмотрим, как это происходит:

  1. Объект SessionFactory создает экземпляр Session.
  2. Сессия возвращает экземпляр CriteriaBuilder , используя метод getCriteriaBuilder ( ).
  3. CriteriaBuilder использует метод createQuery () . Это создает объект CriteriaQuery() , который далее возвращает экземпляр Query.
  4. В конце мы вызываем метод getResult() для получения объекта запроса, содержащего результаты.

Давайте посмотрим на другое выражение из CriteriaQuery :

 cr.select(root).where(cb.gt(root.get("salary"), 50000)); 

В качестве результата приведенный выше запрос возвращает набор сотрудников с окладом более 50000.

3. JPQL​

JPQL расшифровывается как Java Persistence Query Language. Spring Data предоставляет несколько способов создания и выполнения запроса, и JPQL — один из них. Он определяет запросы с использованием аннотации @Query в Spring для выполнения как JPQL, так и собственных запросов SQL. В определении запроса по умолчанию используется JPQL.

Мы используем аннотацию @Query для определения SQL-запроса в Spring. Любой запрос, определяемый аннотацией @Query , имеет более высокий приоритет по сравнению с именованными запросами , которые аннотируются с помощью @NamedQuery .

3.1. Использование запросов JPQL​

Давайте построим динамический запрос, используя JPQL:

 @Query(value = "SELECT e FROM Employee e")   ListEmployee> findAllEmployees(Sort sort); 

С запросами JPQL, имеющими параметры-аргументы, Spring Data передает аргументы метода в запрос в том же порядке, что и объявление метода. Давайте рассмотрим пару примеров, в которых аргументы метода передаются в запрос:

 @Query("SELECT e FROM Employee e WHERE e.salary = ?1")   Employee findAllEmployeesWithSalary(Long salary); 
 @Query("SELECT e FROM Employee e WHERE e.name = ?1 and e.salary = ?2")   Employee findEmployeeByNameAndSalary(String name, Long salary); 

Для последнего вышеприведенного запроса аргумент метода имени передается в качестве параметра запроса относительно индекса 1, а аргумент зарплаты передается в качестве параметра запроса индекса 2.

3.2. Использование собственных запросов JPQL​

Мы можем выполнять эти SQL-запросы непосредственно в наших базах данных, используя собственные запросы, которые ссылаются на реальные базы данных и табличные объекты. Нам нужно установить значение атрибута nativeQuery в true для определения собственного SQL-запроса . Собственный SQL-запрос будет определен в атрибуте value аннотации.

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

 @Query(   value = "SELECT * FROM Employee e WHERE e.salary = ?1",   nativeQuery = true)   Employee findEmployeeBySalaryNative(Long salary); 

Использование именованных параметров упрощает чтение запроса и делает его менее подверженным ошибкам в случае рефакторинга. Давайте посмотрим на иллюстрацию простого именованного запроса в JPQL и собственном формате:

 @Query("SELECT e FROM Employee e WHERE e.name = :name and e.salary = :salary")   Employee findEmployeeByNameAndSalaryNamedParameters(   @Param("name") String name,   @Param("salary") Long salary); 

Параметры метода передаются в запрос с использованием именованных параметров. Мы можем определить именованные запросы, используя аннотацию @Param внутри объявления метода репозитория. В результате аннотация @Param должна иметь строковое значение, совпадающее с соответствующим именем запроса JPQL или SQL. «

 @Query(value = "SELECT * FROM Employee e WHERE e.name = :name and e.salary = :salary",   nativeQuery = true)   Employee findUserByNameAndSalaryNamedParamsNative(   @Param("name") String name,   @Param("salary") Long salary); 

4. Высокий уровень качества​

HQL расшифровывается как язык запросов Hibernate. Это объектно-ориентированный язык, похожий на SQL , который мы можем использовать для запросов к нашей базе данных. Однако главный недостаток — нечитаемость кода. Мы можем определить наши запросы как именованные запросы, чтобы поместить их в фактический код, который обращается к базе данных.

4.1. Использование именованного запроса Hibernate​

Именованный запрос определяет запрос с предопределенной неизменной строкой запроса. Эти запросы безотказны, поскольку они проверяются во время создания фабрики сеансов. Давайте определим именованный запрос, используя аннотацию org.hibernate.annotations.NamedQuery :

 @NamedQuery(name = "Employee_FindByEmployeeId",   query = "from Employee where style="color:#393A34">) 

Каждая аннотация @NamedQuery присоединяется только к одному классу сущностей. Мы можем использовать аннотацию @NamedQueries , чтобы сгруппировать более одного именованного запроса для сущности:

 @NamedQueries(   @NamedQuery(name = "Employee_findByEmployeeId",   query = "from Employee where style="color:#393A34">),   @NamedQuery(name = "Employee_findAllByEmployeeSalary",   query = "from Employee where salary = :salary")   >) 

4.2. Хранимые процедуры и выражения​

В заключение, мы можем использовать аннотацию @NamedNativeQuery для хранения процедур и функций:

 @NamedNativeQuery(   name = "Employee_FindByEmployeeId",   query = "select * from employee emp where style="color:#393A34">,   resultClass = Employee.class) 

5. Преимущества запросов Criteria по сравнению с запросами HQL и JPQL​

Главное преимущество Criteria Queries перед HQL — приятный, чистый, объектно-ориентированный API . В результате мы можем обнаружить ошибки в Criteria API во время компиляции.

Кроме того, запросы JPQL и запросы Criteria имеют одинаковую производительность и эффективность.

Критериальные запросы более гибкие и обеспечивают лучшую поддержку написания динамических запросов по сравнению с HQL и JPQL.

Но HQL и JPQL обеспечивают встроенную поддержку запросов, которая невозможна с запросами Criteria. Это один из недостатков запроса Criteria.

Мы можем легко писать сложные соединения, используя собственные запросы JPQL , тогда как ими сложно управлять, применяя то же самое с Criteria API.

6. Заключение​

В этой статье мы в основном рассмотрели основы запросов Criteria, запросов JPQL и запросов HQL в Hibernate и JPA. Кроме того, мы узнали, как использовать эти запросы, а также преимущества и недостатки каждого подхода.

Как всегда, полные примеры кода, используемые в этой статье, можно найти на GitHub и здесь .

  • 1. Обзор
  • 2. Критериальные запросы
    • 2.1. Зависимости Maven
    • 2.2. Использование запросов и выражений с критериями
    • 3.1. Использование запросов JPQL
    • 3.2. Использование собственных запросов JPQL
    • 4.1. Использование именованного запроса Hibernate
    • 4.2. Хранимые процедуры и выражения

    Как устроен SQL: основы, принципы работы и примеры запросов

    SQL (Structured Query Language) — это язык программирования, используемый для управления данными в реляционных базах данных. Он предоставляет нам возможность создавать, изменять и извлекать информацию из баз данных.

    Структура SQL

    SQL состоит из нескольких частей:

    • Data Definition Language (DDL): Используется для создания и изменения структуры базы данных. Включает операторы для создания таблиц, изменения таблиц, создания индексов и других объектов.
    • Data Manipulation Language (DML): Используется для вставки, обновления и удаления данных в таблицах.
    • Data Query Language (DQL): Используется для извлечения данных из таблиц. Здесь находятся наиболее часто используемые операторы SELECT, WHERE, GROUP BY, HAVING и другие.
    • Data Control Language (DCL): Используется для управления доступом к данным. Включает операторы для предоставления и отзыва прав доступа.
    • Transaction Control Language (TCL): Используется для управления транзакциями в базе данных. Операторы COMMIT и ROLLBACK относятся к этой части SQL.
    • Data Integrity Constraints (DIC): Используются для определения правил целостности данных в таблицах. Например, ограничения на значения полей или связи между таблицами.
    • Views, indexes, and triggers: Это дополнительные элементы SQL, которые позволяют упростить и оптимизировать работу с базой данных.

    Примеры SQL-запросов

    Давайте рассмотрим несколько примеров SQL-запросов.

    -- Создание таблицы "users" CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), age INT );

    В этом примере мы создаем таблицу «users» с тремя столбцами: «id», «name» и «age». Столбец «id» имеет тип данных INT и является первичным ключом таблицы. Столбец «name» имеет тип данных VARCHAR(100), что означает, что он может хранить строку до 100 символов. Столбец «age» имеет тип данных INT.

    -- Вставка данных в таблицу "users" INSERT INTO users (id, name, age) VALUES (1, 'John', 25);

    В этом примере мы вставляем данные в таблицу «users». Мы указываем значения для столбцов «id», «name» и «age».

    -- Извлечение данных из таблицы "users" SELECT * FROM users;

    В этом примере мы извлекаем все данные из таблицы «users». Звездочка (*) означает, что мы выбираем все столбцы.

    Заключение

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *