Оптимизация производительности выполнения запросов (SQL Server Compact)
Одним из способов повышения производительности приложений SQL Server Compact 4.0 является оптимизация используемых запросов. В следующих разделах рассматриваются конкретные методы повышения производительности при обработке запросов.
Повышение эффективности индексов
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу https://schemas.microsoft.com/sqlserver/2004/07/showplan/.
Следующие несколько разделов содержат дополнительные сведения о создании эффективных индексов.
Создание индексов с высокой избирательностью
В большинстве случаев индексирование по столбцам, используемым в предложениях WHERE важных запросов, повышает производительность при обработке таких запросов. Однако в действительности это определяется степенью избирательности индекса. Избирательность представляет собой отношение количества выбранных строк к общему количеству строк. Если это отношение мало, значит, индекс обладает высокой избирательностью. Он позволяет отбросить большую часть строк и значительно уменьшает размер результирующего набора. Подобный индекс является эффективным. Индекс, не обладающий избирательностью, не является эффективным.
Наилучшей избирательностью обладает уникальный индекс. При использовании уникального индекса возвращается только одна строка, что делает этот индекс наиболее эффективным для применения в запросах, которые должны возвращать только одну строку. Например, индексирование по столбцу, содержащему уникальный код, позволяет быстро найти требуемую строку.
Чтобы определить избирательность индекса, следует выполнить для таблиц SQL Server Compact хранимую процедуру sp_show_statistics. Например, чтобы оценить избирательность столбцов Customer ID и Ship Via, можно выполнить следующие хранимые процедуры.
sp_show_statistics_steps 'orders', 'customer id';
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
------------------------------------------------------------
ALFKI 0 7 0
ANATR 0 4 0
ANTON 0 13 0
AROUT 0 14 0
BERGS 0 23 0
BLAUS 0 8 0
BLONP 0 14 0
BOLID 0 7 0
BONAP 0 19 0
BOTTM 0 20 0
BSBEV 0 12 0
CACTU 0 6 0
CENTC 0 3 0
CHOPS 0 12 0
COMMI 0 5 0
CONSH 0 4 0
DRACD 0 9 0
DUMON 0 8 0
EASTC 0 13 0
ERNSH 0 33 0
(90 rows affected)
и
sp_show_statistics_steps 'orders', 'reference3';
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
------------------------------------------------------------
1 0 320 0
2 0 425 0
3 0 333 0
(3 rows affected)
Результаты показывают, что столбец Customer ID имеет значительно более низкую степень повторяемости. Это означает, что индекс по данному столбцу будет иметь большую избирательность, чем индекс по столбцу Ship Via.
Дополнительные сведения об использовании этих хранимых процедур см. в разделах sp_show_statistics (SQL Server Compact 3.5), sp_show_statistics_steps (SQL Server Compact 3.5) и sp_show_statistics_columns (SQL Server Compact).
Создание многостолбцовых индексов
Многостолбцовые индексы являются дальнейшим развитием одностолбцовых индексов. Они используются для вычисления критериев фильтра, соответствующих набору префиксов ключевых столбцов. Например, комбинированный индекс CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) помогает исполнять следующие запросы.
... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'
Однако при исполнении следующего запроса этот индекс не поможет.
- ... WHERE "First Name" = 'John'
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
SQL Server Compact хранит данные на страницах размером по 4 КБ. Количество страниц можно приблизительно оценить по следующей формуле (фактическое число страниц может быть несколько больше, поскольку ядро хранилища данных использует дополнительные ресурсы).
<суммарный размер столбцов в байтах> * <число строк>
<число страниц> = -----------------------------------------------------------------
4096
Предположим, что таблица имеет следующую структуру.
Имя столбца |
Тип (размер) |
|---|---|
Order ID |
INTEGER (4 байта) |
Product ID |
INTEGER (4 байта) |
Цена единицы |
MONEY (8 байт) |
Количество |
SMALLINT (2 байта) |
Скидка |
REAL (4 байта) |
В таблице 2820 строк. В соответствии с формулой, для хранения данных этой таблицы необходимо приблизительно 16 страниц:
<число страниц> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 страниц
Выбор столбцов для индексирования
Поскольку первичные и внешние ключи часто применяются для объединения таблиц, рекомендуется всегда создавать индексы для первичных ключей. Кроме того, во многих случаях создание индексов для внешних ключей также повышает быстродействие. Индексирование по этим ключам позволяет оптимизатору использовать более эффективные алгоритмы объединения индексов. Если в запросах для объединения таблиц используются другие столбцы, в большинстве случаев создание индексов для этих столбцов также позволяет повысить производительность.
После создания ограничений для первичных и внешних ключей SQL Server Compact автоматически создает для них индексы и позволяет использовать при оптимизации запросов все преимущества, предоставляемые индексами. Не следует забывать, что чем меньше длина первичных и внешних ключей, тем быстрее выполняется объединение.
Использование индексов и предложений фильтров
Применение индексов позволяет ускорить вычисление некоторых типов предложений фильтров. Хотя использование любых предложений фильтров уменьшает результирующий набор, возвращаемый запросом, некоторые предложения фильтров позволяют уменьшить объем анализируемых данных.
Аргумент поиска (SARG) ограничивает область поиска, поскольку он указывает точное соответствие, диапазон значений или пересечение двух или более элементов, объединенных инструкцией AND. Аргумент поиска может принимать одну из следующих форм.
- Оператор столбца <переменная или значение>
- <переменная или значение> оператор столбца
В качестве операторов SARG могут использоваться следующие операторы: =, >, <, >=, <=, IN, BETWEEN и (в некоторых случаях) LIKE (при поиске на основе совпадения префиксов, например "LIKE 'John%'"). SARG может включать несколько условий, объединенных инструкцией AND. В качестве аргумента поиска могут выступать запросы, задающие конкретное значение, как показано ниже.
"Customer ID" = 'ANTON'
'Doe' = "Last Name"
Также в качестве SARG могут выступать запросы, задающие диапазон значений, как показано ниже.
"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')
Выражение, не использующее операторы SARG, не повышает производительность, поскольку обработчик запросов SQL Server Compact должен проанализировать каждую строку, чтобы определить, соответствует ли она предложению фильтра. Поэтому для выражений, не использующих операторы SARG, использование индексов неэффективно. Следующие операторы не являются операторами SARG: NOT, <>, NOT EXISTS, NOT IN, NOT LIKE и внутренние функции.
Использование оптимизатора запросов
При определении метода доступа к базовым таблицам оптимизатор SQL Server Compact определяет, существует ли индекс для предложения SARG. Если индекс существует, оптимизатор оценивает индекс, определяя, сколько строк будет возвращено, а затем определяет затраты на поиск подходящих строк путем применения индекса. Если окажется, что использование индекса требует меньше затрат, чем просмотр таблицы, будет применяться доступ с использованием индекса. Индекс является потенциально эффективным, если его первый столбец или набор префиксов столбцов используется в SARG или если SARG задает нижнее, верхнее или оба предельных значения для ограничения поиска.
Время отклика и общее время
Время отклика — это интервал времени, по истечении которого запрос возвращает первую запись. Общее время — это интервал времени, необходимый запросу, чтобы вернуть все записи. Время отклика является важным параметром для интерактивных приложений, поскольку получение первой строки показывает пользователю, что запрос обрабатывается. Общее время характеризует общую производительность приложений, работающих в пакетном режиме. При создании приложений и запросов необходимо определить критерии производительности и выполнять разработку, исходя из этих критериев.
Например, предположим, что запрос возвращает 100 записей и используется для внесения первых пяти записей в список. В этом случае общее время, необходимое, чтобы вернуть все 100 записей, является несущественным, и следует уделить внимание уменьшению времени, необходимого для возврата первых 5 записей, попадающих в список.
Многие операции запросов могут выполняться без сохранения промежуточных результатов. Такие операции называют конвейерными. Примерами конвейерных операций являются проекции, выборки и объединения. Запросы, основанные на этих командах, могут сразу возвращать результаты. Другие операции (такие как SORT и GROUP-BY), прежде чем вернуть результат родительским операциям, должны обработать все входные данные. Про такие операции говорят, что они требуют материализации. Из-за необходимости материализации при выполнении запросов, основанных на подобных операциях, как правило, присутствует начальная задержка. После начальной задержки подобные запросы, как правило, очень быстро возвращают результаты.
В запросах, требующих малого времени отклика, следует избегать материализации. Например, выполнение команды ORDER-BY с использованием индекса обеспечивает меньшее время отклика, чем с использованием сортировки. Более подробно этот вопрос рассматривается ниже.
Уменьшение времени отклика путем индексирования столбцов, используемых в операциях ORDER-BY, GROUP-BY, DISTINCT
Операции ORDER-BY, GROUP-BY, и DISTINCT представляют собой различные типы сортировки. Обработчик запросов SQL Server Compact использует при сортировке два способа. Если записи уже отсортированы с помощью индекса, обработчику необходимо использовать только индекс. В противном случае обработчик должен сначала отсортировать записи, используя временную рабочую таблицу. Подобная предварительная сортировка может вызвать значительную начальную задержку на устройствах с медленными процессорами и ограниченным объемом памяти, поэтому ее следует избегать в запросах и приложениях, требующих малого времени отклика.
В контексте многостолбцовых индексов при выборе индексов для операций ORDER-BY или GROUP-BY, столбцы, для которых выполняются операции ORDER-BY или GROUP-BY, должны соответствовать набору префиксов столбцов индексов и следовать в том же порядке. Например, индекс CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) поможет оптимизировать следующие запросы.
... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...
Данный индекс не поможет в оптимизации следующих запросов.
... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...
Чтобы выбрать многостолбцовый индекс для операции DISTINCT, список проекции должен включать все столбцы индексов, хотя порядок столбцов может отличаться. Индекс, рассмотренный в предыдущем примере, поможет оптимизировать следующие запросы.
... DISTINCT "Last Name", "First Name" ...
... DISTINCT "First Name", "Last Name" ...
Данный индекс не поможет в оптимизации следующих запросов.
... DISTINCT "First Name" ...
... DISTINCT "Last Name" ...
Примечание
Если запрос самостоятельно возвращает уникальные строки, не следует использовать ключевое слово DISTINCT, поскольку это всего лишь вызовет дополнительную нагрузку на систему.
Изменение подчиненных запросов путем включения команды JOIN
В некоторых случаях для повышения производительности можно изменить подчиненные запросы путем включения в них команды JOIN. Применение команды JOIN позволяет обрабатывать таблицы в порядке, отличающемся от указанного в запросе. Использование подчиненных запросов вызвано тем, что во многих случаях для определения значения выражения, содержащего подчиненный запрос, нет необходимости просматривать все строки, возвращаемые этим запросом. Например, подчиненный запрос EXISTS возвращает значение TRUE, как только будет возвращена первая подходящая строка.
Примечание
Обработчик запросов SQL Server Compact всегда изменяет подчиненный запрос IN таким образом, чтобы использовалась команда JOIN. Поэтому с запросами, содержащими подчиненные запросы с предложением IN, данный подход использовать не нужно.
Например, чтобы найти все заказы, содержащие хотя бы один товар, на который предоставлена скидка 25 процентов и более, можно использовать следующий подчиненный запрос EXISTS.
SELECT "Order ID" FROM Orders O
WHERE EXISTS (SELECT "Order ID"
FROM "Order Details" OD
WHERE O."Order ID" = OD."Order ID"
AND Discount >= 0.25)
Данный запрос можно изменить, используя команду JOIN, как показано ниже.
SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"
OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25
Ограничение использования команд Outer JOIN
Команды OUTER JOIN обрабатываются не так, как команды INNER JOIN, поскольку оптимизатор не пытается изменить порядок объединения таблиц, указанных в команде OUTER JOIN, как это происходит при обработке команды INNER JOIN. Сначала выполняется обращение к внешней таблице (левая таблица в команде LEFT OUTER JOIN и правая таблица в команде RIGHT OUTER JOIN), а затем — к внутренней таблице. Использование фиксированного порядка объединения может приводить к созданию неоптимальных планов выполнения.
Дополнительные сведения о запросах, содержащих INNER JOIN, см. в статье базы знаний Майкрософт.
Использование параметризованных запросов
Если приложение выполняет несколько запросов, отличающихся только значениями некоторых постоянных величин, для повышения производительности можно использовать параметризованные запросы. Например, чтобы получить список заказов разных клиентов, можно использовать следующий запрос.
SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?
Применение параметризованных запросов повышает производительность, поскольку соответствующий запрос компилируется только один раз, а затем многократно используется откомпилированный план. С точки зрения программы необходимо продолжать использовать командный объект, который содержит находящийся в кэше план запроса. Удаление предыдущего командного объекта и создание нового приводит к удалению плана из кэша и требует повторной компиляции запроса. Чтобы поочередно выполнять несколько параметризованных запросов, можно создать несколько командных объектов, каждый из которых будет содержать кэшированный план выполнения для соответствующего параметризованного запроса. Это позволит избежать перекомпиляции всех запросов.
Выполнение запросов только в тех случаях, когда это необходимо
Обработчик запросов SQL Server Compact — это мощное средство извлечения данных, хранящихся в реляционной базе данных. Однако для работы любого обработчика запросов необходимы ресурсы — перед исполнением запроса обработчик должен откомпилировать и оптимизировать запрос, а затем создать план выполнения. Это особенно заметно при исполнении небольших, быстро выполняющихся запросов. Поэтому в некоторых случаях отказ от использования запроса и самостоятельное выполнение каких-либо действий позволяет значительно улучшить производительность. Если при работе важного компонента учитывается каждая миллисекунда, рекомендуется отказаться от использования простых запросов и самостоятельно выполнять требуемые действия. Если запросы являются большими и сложными, их выполнение лучше возложить на обработчик запросов.
Например, предположим, что требуется определить код клиента для нескольких заказов, упорядоченных по номеру заказа. Существует два пути решения этой задачи. Первый способ. При каждом поиске необходимо выполнить следующие действия.
Открыть базовую таблицу Orders.
Найти требуемую строку, используя нужное значение поля Order ID.
Получить значение поля Customer ID.
Второй способ. При каждом поиске необходимо выполнить следующий запрос.
SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>
Вариант с использованием запроса является более простым, но более медленным, поскольку обработчик запросов SQL Server Compact преобразует декларативные инструкции SQL в приведенные выше три операции. Затем эти операции выполняются в указанной последовательности. Выбор метода, используемого для решения конкретной задачи, зависит от того, что является более важным для работы приложения — простота или производительность.