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

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

Максимальные проблемы во время переноса приложения могут принести расширения языка SQL. Так, например, в SQL Server используется Transact-SQL, а в Oracle - PL/SQL, и их операторы совершенно несовместимы.

Но даже если вы переведете синтаксис с одного языка на другой, проблем будет очень много. Это связано с различными архитектурами оптимизаторов запросов, разницей в блокировках и т. д. Если код программы при смене СУБД требует незначительных изменений, то запросы SQL нужно переписывать полностью и с самого начала. Не стоит пытаться исправить их или оптимизировать. Писать надо изначально для определенной СУБД

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

1. Разбор запроса.

2. Оптимизация.

3. Генерация плана выполнения. 4 Выполнение запроса.

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

Теперь посмотрим на два запроса:

SELECT * FROM TableName WHERE ColumnName = 10 и

SELECT * FROM TableName WHERE ColumnName = 20

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

Чтобы этого не было, нужно использовать в запросах переменные:

SELECT *
FROM TableName
WHERE ColumnName = paraml

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

Буфер для хранения планов выполнения не бесконечен, поэтому в нем хранятся данные только о последних запросах (количество зависит от размера буфера). Если какой-то запрос выполняется часто, то в нем обязательно нужно использовать переменные, потому что это значительно повышает производительность. Попробуйте дважды выполнить один и тот же запрос и посмотреть на скорость выполнения. Вторичное выполнение произойдет намного быстрее и может быть даже незаметным на глаз.

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

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

В большинстве приложений баз данных присутствуют какие-либо возможности для построения отчетов. Запросы на языке SQL для их формирования могут выполняться очень долго, хотя сами отчеты делают очень редко (например, месячный, квартальный или годовой отчет). Из-за этого программисты мало внимания уделяют оптимизации. Но в действительности отчетность может формироваться несколько раз подряд. После первой попытки очень часто в данные вносятся изменения, и формирование повторяется снова. Таким образом, даже редко выполняемые, но очень медленные запросы нужно постараться оптимизировать хотя бы с помощью использования параметров.

При написании запросов старайтесь как можно меньше использовать операторы SELECT, особенно вложенные в секцию WHERE. Для повышения производительности иногда помогает вынос лишнего SELECT в секцию FROM Но иногда бывает и наоборот, быстрее выполняется запрос, в котором SELECT вынесен из FROM в тело WHERE.

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

SELECT *

FROM tbPerson р

WHERE р idStatus = (SELECT [Keyl] FROM tbStatus WHERE sName = 'Работает') Вам не обязательно полностью понимать суть этого запроса. Главное здесь в том, что в секции WHERE выполняется подзапрос. Он будет генерироваться для каждой строки в таблице tbPerson, что может стать большой нагрузкой для сервера.

Теперь разберем, как можно вынести запрос SELECT в секцию FROM. Это можно сделать следующим образом:

SELECT *
FROM tbPerson p.

(SELECT [Keyl] FROM tbStatus WHERE sName = 'Работает') s WHERE p.idStatus = s Keyl

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

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

SELECT *

FROM tbPerson p. tbStatus s WHERE p.idStatus = s.Keyl AND s.sName = 'Работает'

В более сложных примерах программисты не видят возможности решения задачи с помощью одного запроса, хотя она существует. Допустим, что у нас есть таблица А с полями:

в Kod - число, принимает значения 1 или 2; в Fami 1 - фамилия; в Fi rstName - имя;

• Otch - отчество.

В этой таблице хранится список данных о сотрудниках Для каждого сотрудника выделены две записи с кодом 1 и с кодом 0. Записи с кодом 1 могут быть связаны с таблицей Info, в которой будет храниться полная информация о сотрудниках. Нам надо получить все записи с кодом 0, для которых существует связь между таблицами А и Info. Такую задачу чаще всего решают, используя двойной запрос:

SELECT * FROM Info i.
(SELECT * FROM A. Info WHERE a.Famil = info.Famil) s
WHERE Kod = 0
AND a.Famil = s.Fami1

Существует и более простой способ решения: SELECT i2.*

FROM Info il. A. Info i2 WHERE il.Kod = 1 AND il.Fami1 = A.Famil AND il.Famil = i2.Famil AND i2.Kod = 0

Здесь в запросе мы дважды ссылаемся на одну и ту же таблицу Info и строим связь Info-A-Info. На первый взгляд связь получается сложной, но при наличии правильно настроенных индексов этот пример будет работать в несколько раз быстрее, чем с использованием подзапросов SELECT.

Для ускорения работы можно разбить один запрос на несколько. Например, для SQL Server предыдущий пример можно видоизменить так:

Declare @id int
SELECT @id = [id]
FROM tbStatus

WHERE sName = 'Работает'

SELECT *
FROM tbPerson p
WHERE p.idStatus = @id

В этом примере мы сначала объявляем переменную @id. Затем в ней сохраняем значение идентификатора, а потом уже ищем соответствующие строки в таблице tbPerson.

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

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

2.12. Оптимизация в базах данных || Оглавление || 2.12.2. Оптимизация СУБД


Delphi в шутку и всерьез: что умеют хакеры



Новости за месяц

  • Январь
    2022
  • Пн
  • Вт
  • Ср
  • Чт
  • Пт
  • Сб
  • Вс