Оптимизация производительности выполнения запросов (SQL Server Compact). Оптимизация запросов sql
Оптимизация производительности выполнения запросов (SQL Server Compact)
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://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'
Однако при исполнении следующего запроса этот индекс не поможет.
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
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 страниц
msdn.microsoft.com
Оптимизация производительности выполнения запросов (SQL Server Compact)
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact 3.5 поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact 3.5 использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact 3.5 не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://schemas.microsoft.com/sqlserver/2004/07/showplan/.
Следующие несколько разделов содержат дополнительные сведения о создании эффективных индексов.
Создание индексов с высокой избирательностью
В большинстве случаев индексирование по столбцам, используемым в предложениях WHERE важных запросов, повышает производительность при обработке таких запросов. Однако в действительности это определяется степенью избирательности индекса. Избирательность представляет собой отношение количества выбранных строк к общему количеству строк. Если это отношение мало, значит, индекс обладает высокой избирательностью. Он позволяет отбросить большую часть строк и значительно уменьшает размер результирующего набора. Подобный индекс является эффективным. Индекс, не обладающий избирательностью, не является эффективным.
Наилучшей избирательностью обладает уникальный индекс. При использовании уникального индекса возвращается только одна строка, что делает этот индекс наиболее эффективным для применения в запросах, которые должны возвращать только одну строку. Например, индексирование по столбцу, содержащему уникальный код, позволяет быстро найти требуемую строку.
Чтобы определить избирательность индекса, следует выполнить для таблиц SQL Server Compact 3.5 хранимую процедуру 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'
Однако при исполнении следующего запроса этот индекс не поможет.
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
SQL Server Compact 3.5 хранит данные на страницах размером по 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 страниц
msdn.microsoft.com
Оптимизация производительности выполнения запросов (SQL Server Compact)
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://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'
Однако при исполнении следующего запроса этот индекс не поможет.
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
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 страниц
msdn.microsoft.com
Оптимизация производительности выполнения запросов (SQL Server Compact)
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://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'
Однако при исполнении следующего запроса этот индекс не поможет.
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
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 страниц
msdn.microsoft.com
Оптимизация производительности выполнения запросов (SQL Server Compact)
Создание эффективных индексов является одним из наиболее действенных способов повышения производительности при обработке запросов. Применение эффективных индексов помогает находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.
Чтобы создавать эффективные индексы, необходимо знать методику использования данных, типы запросов и частоту их исполнения, а также понимать, каким образом обработчик запросов использует индексы для ускорения поиска данных.
Чтобы принять правильное решение о создании индексов, следует проанализировать важные запросы, исполнение которых оказывает наибольшее влияние на производительность, с целью создания индексов, ускоряющих выполнение именно этих запросов. После создания индекса необходимо выполнить запрос снова и оценить изменение производительности. Если производительность не повысилась, индекс следует удалить.
Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT. Однако команды управления данными (INSERT, UPDATE и DELETE) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT, создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.
SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://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'
Однако при исполнении следующего запроса этот индекс не поможет.
При создании многостолбцового индекса столбцы с наибольшей избирательностью должны находиться как можно ближе к левому краю ключа. Это позволяет повысить избирательность подобного индекса при обработке нескольких выражений.
Отказ от индексирования небольших таблиц
Небольшая таблица — это таблица, содержимое которой полностью помещается на одной странице данных (или на небольшом количестве страниц). Следует избегать индексирования небольших таблиц, поскольку, как правило, для таких таблиц более эффективно применять просмотр. Это уменьшает затраты на загрузку и обработку страниц индексов. Отказ от создания индексов для совсем небольших таблиц исключает возможность выбора соответствующего индекса оптимизатором.
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 страниц
msdn.microsoft.com
