Подсказки оптимизатору запросов — до 15 мин

Вы можете изменять метод доступа к данным и план исполнения, модифицируя сам оператор T-SQL, но в случае ошибок этот метод может нарушить функциональные требования к данному оператору T-SQL. Более надежным методом оптимизации операторов T-SQL является использование подсказок. С помощью подсказок вы можете указывать оптимизатору запросов, какие операции он должен выполнять и какие объекты он должен использовать.

Подсказки операций связывания (join hints) используются, чтобы указывать оптимизатору запросов типы операций связывания, которые он должен выполнять. (Если в запросе не указано никакого типа, то оптимизатор запросов выбирает тип самостоятельно.) В SQL Server вы можете выполнять связывание вложенных цепочек, хеш-связывание, связывание слиянием и удаленное связывание. Вы указываете метод связывания с помощью следующих подсказок.

· LOOP. Указывает связывание вложенных цепочек. При этом типе связывания для каждой строки внешней таблицы проверяется каждая строка внутренней таблицы на совпадение значений указанных полей.

· HASH. Указывает хеш-связывание. При этом типе связывания одна таблица преобразуется как хеш-таблица. Другая таблица сканируется по одной строке, и для поиска совпадений используется хеш-функция.

· MERGE. Указывается связывание с сортировкой слиянием. При этом типе связывания сортируется каждая таблица, и затем каждая строка каждой таблицы сравнивается с соответствующей строкой в убывающем порядке.

· REMOTE. Указывает удаленное связывание. При этом типе связывания хотя бы одна их участвующих таблиц является удаленной.

Рассмотрим пример использования подсказки для операции связывания.

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,

LastName, OrderDate

FROM Orders, Employees

WHERE Orders.EmployeeID = Employees.EmployeeID

OPTION (HASH JOIN)

Подсказки для связывания являются взаимоисключающими – можно одновременно указывать только одну подсказку.

Если вы решили использовать для связывания синтаксис SQL-92, то можете задать тип связывания, также используя подсказку. Применяя синтаксис SQL-92, вы могли бы переписать предыдущий запрос следующим образом:

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,

LastName, OrderDate

FROM Orders INNER HASH JOIN Employees

ON (Orders.EmployeeID = Employees.EmployeeID)

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

Подсказки для запросов используются для указания того, как следует выполнять определенные операции запроса. Подсказки для запросов разбиты на три категории: GROUP BY, UNION и остальные.

Подсказки GROUP BY. Следующие подсказки указывают, как следует выполнять операции GROUP BY или COMPUTE.

· HASH GROUP BY. Указывает, что для выполнения операции GROUP BY будет использоваться хеш-функция

· ORDER GROUP BY. Указывает, что для выполнения операции GROUP BY будет использоваться операция сортировки.

Используя пример GROUP BY из раздела "Просмотр плана для операции агрегирования" выше в этой лекции, мы можем указать с помощью подсказки, как должна выполняться операция HASH GROUP BY:

SELECT CustomerID, SUM(OrderDetails.UnitPrice)

FROM Orders, OrderDetails

HASH GROUP BY CustomerID

OPTION (HASH GROUP)

Подсказки типа GROUP BY являются взаимоисключающими – можно одновременно использовать только одну подсказку.

Подсказки UNION. Следующие подсказки используются для указания того, как следует выполнять операции UNION.

· MERGE UNION. Для выполнения операции UNION используется операция MERGE.

· HASH UNION. Для выполнения операции UNION используется хеш-функция.

· CONCAT UNION. Для выполнения операции UNION используется функция конкатенации.

Вот пример использования подсказки CONCAT UNION:

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM orders

WHERE CustomerID = 'TOMSP'

UNION

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM orders

WHERE EmployeeID = '4'

OPTION (CONCAT UNION)

Подсказки типа UNION являются взаимоисключающими.

К сожалению, не существует какой-либо "секретной формулы", которую можно использовать для определения наиболее подходящей операции UNION в вашей ситуации. И здесь лучше всего использовать Query Analyzer для опробования различных подсказок UNION, чтобы посмотреть, какая из них дает наилучшую стоимость. Обычно наилучшую стратегию для операций UNION определяет оптимизатор запросов SQL Server.

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

· FORCE ORDER. Указывает, что доступ к таблицам должен выполняться в том же порядке, как они представлены в запросе. По умолчанию SQL Server может изменять порядок следования таблиц.

· ROBUST PLAN. Указывает, что оптимизатор запросов должен быть готов к максимально возможному размеру строк. Вот пример использования этой подсказки:

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,

LastName, OrderDate

FROM Orders, Employees

WHERE Orders.EmployeeID = Employees.EmployeeID

OPTION (ROBUST PLAN)

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

· FAST n. Заменяет FASTFIRSTROWS, сохраненную для обратной совместимости. Эта подсказка указывает SQL Server, что нужно оптимизировать выборку n первых строк данных.

· INDEX = имя_индекса. Указывает оптимизатору запросов, что нужно использовать указанный индекс, когда имеется такая возможность. В одном из первых примеров этой лекции показано, как использовать подсказку INDEX. Мы повторяем здесь этот пример:

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM orders WITH (INDEX = EmployeeID)

WHERE EmployeeID = 5

OPTION (FAST 10)

Квалификатор WITH не является обязательным.

Подсказка INDEX = EmployeeID указывает, что должен использоваться индекс EmployeeID. Если указана подсказка FAST 10, то SQL Server будет оптимизировать выборку первых 10 строк (если возможно) и затем будет возвращать остальные строки.

Дата добавления: 2014-01-11