Главная страница » Как установить forceplan для таблицы sql

Как установить forceplan для таблицы sql

  • автор:

Как установить forceplan для таблицы sql

Параметр FORCEPLAN иллюстрирует старый метод настройки в Microsoft SQL Server и Sybase. Он требует отдельного оператора SQL: SET FORCEPLAN ON

Этот параметр действует на весь SQL-код, который выполняется в текущем соединении, пока вы не выполните оператор: SET FORCEPLAN OFF

Когда значение параметра FORCEPLAN равно ON, база данных выполняет только простейшую оптимизацию SQL. Обычно она использует планы вьшолнения с вложенными циклами, которые работают при помощи индексов и соединяют таблицы в том же порядке, в каком они перечислены в разделе FROM. Если вы хотите получить план именно такого типа, то SET FORCEPLAN будет идеальным вариантом, поскольку не только включает нужный план, но и экономит время разбора, которое в противном случае было бы потрачено на выбор из большого диапазона планов, особенно для соединений множества таблиц. Это, образно говоря, обоюдоту-пой меч, поэтому применяйте его только когда знаете, что в разделе FROM указан правильный порядок соединения, и хотите использовать вложенные циклы.

5Диаграммное изображение простых запросов SQL

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

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

Зачем нужен новый метод?

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

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

(8+(0.25 X 8) -1) + (0.75 х 8/2) + (0.75 х 8) — ? Естественно, вторую задачу решать проще.

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

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

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

SET FORCEPLAN (Transact-SQL)

When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.

Syntax

Remarks

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact\-SQL SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way in which Transact\-SQL SQL Server processes the tables to satisfy the query.

Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement.

SET FORCEPLAN is applied at execute or run time and not at parse time.

Permissions

SET FORCEPLAN permissions default to all users.

Examples

The following example performs a join of four tables. The SHOWPLAN_TEXT setting is enabled, so SQL Server returns information about how it is processing the query differently after the SET FORCE_PLAN setting is enabled.

SET FORCEPLAN

Makes the Microsoft® SQL Server™ query optimizer process a join in the same order as tables appear in the FROM clause of a SELECT statement only.

Syntax
Remarks

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way SQL Server processes the tables to satisfy the query.

Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement.

The setting of SET FORCEPLAN is set at execute or run time and not at parse time.

Permissions

SET FORCEPLAN permissions default to all users.

Examples

This example performs a join between three tables. The SHOWPLAN_TEXT setting is enabled so SQL Server returns information about how it is processing the query differently after the SET FORCE_PLAN setting is enabled.

Override the Query Optimizer for your T-SQL Joins with FORCEPLAN

The SET FORCEPLAN statement overrides the logic used by the SQL Server query optimizer to process a T-SQL SELECT statement.

More specifically, when FORCEPLAN is set to ON , the query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.

This also forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.

Example

To demonstrate how FORCEPLAN works, I’m going to run two SELECT queries, first with FORCEPLAN set to ON , then with FORCEPLAN set to OFF .

Both queries are identical, with the exception that the join tables are in listed a different order.

In this example I use SHOWPLAN_XML to show the estimated query plan, but you could just as easily use another method (such as the Explain button in Azure Data Studio, or the Include Actual Execution Plan icon in SSMS to display the actual query plan).

SET FORCEPLAN ON

Screenshot of query plan with FORCEPLAN set to ON

We can see that the query plan for each query reflects the order in which I included the table names in the FROM clause.

SET FORCEPLAN OFF

Screenshot of query plan with FORCEPLAN set to OFF

This time, both queries result in an identical query plan. The query optimizer ignored the order in which I listed them in the FROM clause and determined its own order.

Note that the FORCEPLAN setting does not change the data returned by the SELECT statement. The actual results are the same regardless of whether FORCEPLAN is set to ON or OFF . The only difference is the way in which tables are processed (which could impact on performance).

You can use SET FORCEPLAN in conjunction with query optimizer hints to further affect how the query is processed.

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

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