Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
кстати, включите, если не включён Trace Flag 4199
21 дек 16, 10:40    [20030699]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
uaggster,

автор
Потому что после DBCC FREEPROCCACHE - ничего не меняется.

С чего бы изменилось, если это процедурный кэш, а не кэш запросов.
21 дек 16, 11:00    [20030844]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 686
Владислав Колосов
uaggster,

автор
Потому что после DBCC FREEPROCCACHE - ничего не меняется.

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

Эээ... еще раз, по пунктам... Есть какой то отдельный кэш запросов?
И параметризованный запрос с точки зрения энжин - это не хранимая процедура?
21 дек 16, 11:17    [20030930]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
uaggster
Владислав Колосов
uaggster,

пропущено...

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

Эээ... еще раз, по пунктам... Есть какой то отдельный кэш запросов?
И параметризованный запрос с точки зрения энжин - это не хранимая процедура?

с точки зрения "энжин" (за что его так) ad hoc, а не хранимая процедура. Но всё равно они в PROCCACHE...
21 дек 16, 11:27    [20030974]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
uaggster,

Добрый день.
Выбор того или иного типа соединения в плане запроса обусловлен двумя главными причинами:
1) стоимость
2) принципиальная "выполнимость" логической операции соединения физическим оператором соединения

В вашем случае, играет роль второй пункт.

Попробуйте дописать в ваш запрос, вместо option(recompile) - option(hash join), вы получите ошибку:
"Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

Чтобы понять почему, нужно посмотреть на конструкцию
		T1._IDRRef IN (
		SELECT 
			CASE 
				WHEN T2._Fld2977_TYPE = 0x08 AND T2._Fld2977_RTRef = @P3
				THEN T2._Fld2977_RRRef
				ELSE 0xFF
			END AS Q_001_F_000RRef

Когда вы пишите "столбец1 in (столбец2 из подзапроса)" это означает логическую операцию полусоединения (Semi Join) по условию столбец1 = столбец2.

В вашем же запросе, в зависимости от @P3 это условие может быть либо "столбец1 = столбец2", либо "столбец1 = константа".
Соединение хэшированием (Hash Join) или слиянием (Merge Join, кроме full) - требует хотя бы одного предиката равенства в соединении (equijoin), т.е. операцию "столбец1 = константа" (можно представить себе это как некую вариацию "cross join"), выполняет только оператор Nested Loops. Который раз за разом, по числу строк из верхней таблицы, сканирует нижнюю, а точнее заполняет Spool и читает оттуда. Это очень долго.

Если же вы пишете option(recompile), то оптимизатор подставляет вместо параметров константы, так как если бы они были написаны литералами в запросе (это называется Parameter Embedding Optimization), тогда проблема с переменным условие соединения уходит и оптимизатор сразу переключается более выгодный алгоритм соединения, т.е. данном случае Hash Join.

Что тут в итоге можно сказать, причина не в стоимости и не в плохих оценках, а в том, что "универсальные запросы работают универсально плохо". Единственный вменяемый вариант, пока 1С-ники переписывают запрос, это, как уже тут советовали - руководство планов (plan guide), которое бы применяло к этому запросу хинт "recompile". В хелпе такой вариант даже указан в качестве наиболее типичных примеров использования plan guides:
Attach Query Hints to a Plan Guide
Common Query Hints Used in Plan Guides
Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed. For an example, see Plan Guides.
21 дек 16, 12:45    [20031470]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
o-o
Guest
SomewhereSomehow
Попробуйте дописать в ваш запрос, вместо option(recompile) - option(hash join), вы получите ошибку
...
Если же вы пишете option(recompile), то оптимизатор подставляет вместо параметров константы,
...
тогда проблема с переменным условие соединения уходит

...а я еще думаю, озверел он что ли, при оценках
автор
они считает, что скан Document111 обойдется в 600 000 строк, а Accum - 4,5 миллиона

выбрать NL, это воистину надо быть Мистером Хенки.
т.е. меня это смутило, но нисколько не заставило задуматься о том, что hash join может быть невозможен,
ведь он есть на соседнем плане.
спасибо!!!
21 дек 16, 13:39    [20031780]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow
проблема с переменным условие соединения уходит

Перечитал, решил уточнить на всякий случай.
Это не то, что у нас во время выполнения Has Join в столбце _Fld2977_RTRef не может быть значения <> 0x0000006F и выполнение не может пойти по ветке "столбец1 = константа", а то что условие перестает быть переменным и альтернатива "столбец1 = константа" / "столбец1 = столбец2" превращается в "столбец1 = столбец2", где в роли "столбца2" выступает выражение, известное в момент (ре)компиляции и сервер может построить экви соединение, что он и делает:
+
Картинка с другого сайта.


И не может получить такое же условие, когда имеется параметр и переменное условие соединения:
+
Картинка с другого сайта.
21 дек 16, 14:19    [20032011]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow,

А почему нельзя вычислять кейс и хешджойнить по результату?
21 дек 16, 16:46    [20032915]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
Любопытный гость
SomewhereSomehow,

А почему нельзя вычислять кейс и хешджойнить по результату?

а вот если вы посмотрите на его план с recompile, то узнаете что когда значения известны так и есть
21 дек 16, 16:50    [20032957]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любопытный гость,

Я объяснил выше почему, потому что оптимизатор не знает какое будет значение параметра во время выполнения и должен гарантировать "универсальный план" под все возможные значения, а предикат соединения может меняться из-за параметра, а hash-у обязательно нужен один equi join, вот простой пример:
use tempdb;
go
if object_id ('t1') is not null drop table t1;
if object_id ('t2') is not null drop table t2;
create table t1(a int not null, b int not null, c int check (c between 1 and 50), constraint pk_a primary key(a));
create table t2(b int not null, c int, d char(10), constraint pk_b primary key(b));
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t2(b,c) select number, number%100+1 from master..spt_values where type = 'p' and number between 1 and 1000;
go

-- 1) OK - hash join with hint
declare @p int = 1;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile, hash join);
go

-- 2) Error: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
-- No join condidition, similar to "cross join", not supported by hash
declare @p int = null;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile, hash join);
go

-- 3) Ok - no hints, same @p = null value - Loops join
declare @p int = null;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile);
go

Во всех случаях я добавил рекомпиляцию.

В первом случае, я ограничил выбор только хэш соединением и в качестве значения параметра задал 1.
При рекомпиляции условие было преобразовано в "case when t2.c = 1 then t2.b else 1 end".

Во втором случае, параметр null, при рекомпиляции условие было преобразовано в "case when t2.c = null then t2.b else 1 end", часть t2.c = null очевидно никогда не может быть true, по этому эта ветка была выкинута на этапе упрощения, осталось только проверить равенство на единицу, а условие соединения теперь фактически исчезло - нет предиката равенства, по этому хэш джойн невозможен и мы получаем ошибку.

В третьем случае, параметр тоже нулл, но я убрал ограничение на хэш и в плане вы увидите соединение вложенными циклами. Попробуйте найти в плане, в Nested Loops условие по которому соединяются таблицы (свойства Outer Refernce или Predicate), вы увидите что их нет, идет просто соединение таблиц вложенными циклами, предикат соединения был упрощен.

Конечно, у ТС там два предиката и формально оптимизатор мог бы что-то сделать, но тут наверное уже работает "консерватизм" оптимизатора, там где потенциально возможно получить некорректные результаты оптимизатор без нужды не упрощает.

Обычно такие манипуляции с условиями в соединения говорят о не очень удачно схеме данных, но я не 1С-ник, так что на этом умолкаю =)
21 дек 16, 17:29    [20033197]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow
Я объяснил выше почему, потому что оптимизатор не знает какое будет значение параметра во время выполнения и должен гарантировать "универсальный план" под все возможные значения
Спасибо.
Возможно я не понимаю элементарщину...
Если взглянуть на план первого запроса из Вашего примера там будет Compute Scalar по формуле [Expr1008] = Scalar Operator(CASE WHEN [tempdb].[dbo].[t2].[c]=(1) THEN [tempdb].[dbo].[t2].[b] ELSE (1) END) и по результату будет Hash build.
Почему нельзя в формуле сразу написать [Expr1008] = Scalar Operator(CASE WHEN [tempdb].[dbo].[t2].[c]=(@p) THEN [tempdb].[dbo].[t2].[b] ELSE (1) END)? Почему тогда план станет не универсальным?

Возможно спросил глупость...
21 дек 16, 18:49    [20033529]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любопытный гость,

Нет, вопрос не глупый, как я писал выше, потому, что сервер не может построить корректное условие по эквисоединению (для Build или Probe), а вы видимо спрашиваете почему именно не может.
Пусть вам ответит сам разработчик оптимизатора Крейг Фридман, лучше чем у меня не получится =)
https://blogs.msdn.microsoft.com/craigfr/2009/04/28/implied-predicates-and-query-hints/

По данной проблеме далее в комментариях:
hackace April 6, 2012 at 8:11 pm
Hi craige,

can you help me ?
there are two TSQL sentences :sentence 1 and sentence 2
The sentence 1 can be excuted successfully ,but there will be throw an exception when I excuted the sentence 2,why?
--sentence 1
use [AdventureWorks]
go
DECLARE @c VARCHAR(10)
SET @c = ''
SELECT CustomerID
FROM dbo.Customers AS C
WHERE CustomerID IN (SELECT  @c + CustomerID FROM dbo.Orders)
OPTION (HASH JOIN, RECOMPILE)


--sentence 2
use [AdventureWorks]
go
DECLARE @c VARCHAR(10)
SET @c = ''
SELECT CustomerID
FROM dbo.Customers AS C
WHERE CustomerID IN (SELECT  @c + CustomerID FROM dbo.Orders)
OPTION (HASH JOIN)

Craig Freedman April 10, 2012 at 9:53 am
Hi,

The first query succeeds because as noted in the above post, with the recompile hint, the optimizer substitutes the empty string constant for the variable @c. You can see the substitution by checking the compute scalar in the plan:

 |Hash Match(Left Semi Join, HASH:([C].[CustomerID])=([Expr1006]), RESIDUAL:([Northwind].[dbo].[Customers].[CustomerID] as [C].[CustomerID]=[Expr1006]))
      |Index Scan(OBJECT:([Northwind].[dbo].[Customers].[Region] AS [C]))
      |Compute Scalar(DEFINE:([Expr1006]=N''+[Northwind].[dbo].[Orders].[CustomerID]))
           |Index Scan(OBJECT:([Northwind].[dbo].[Orders].[CustomersOrders]))


Without the recomile hint, the optimizer cannot do the substitution. I believe it then incorrectly concludes that @c is not a runtime constant and further concludes that it cannot safely use the hash join since it would need to reevaluate the orders subquery for each row from the customers table. I'm afraid that I'm not aware of any workaround other than to remove hash join hint.

Craig

Это я и имел ввиду, когда писал выше:
SomewhereSomehow
Конечно, у ТС там два предиката и формально оптимизатор мог бы что-то сделать, но тут наверное уже работает "консерватизм" оптимизатора, там где потенциально возможно получить некорректные результаты оптимизатор без нужды не упрощает.

Проще говоря - не может и все. Заведите item на microsoft connect, скажите что требуете доработать алгоритм и почему, вам ответят с большими подробностями почему это так или наоборот, что доработают (в чем я сомневаюсь).
21 дек 16, 19:32    [20033716]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,
опечатка: "лучше чем у него у меня не получится"
21 дек 16, 19:33    [20033719]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow,

Спасибо. Теперь понятно.
21 дек 16, 21:33    [20034099]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
SomewhereSomehow,

Следует ли из вышесказанного, что выгоднее использовать объединение с табличной переменной или временной таблице вместо использования констант, если есть сомнения?
22 дек 16, 10:53    [20035501]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
Переменных, конечно же, а не констант. Если оптимизатор полагает, что там может быть что-то еще, а не скаляр (что странно, на мой взгляд, пусть даже и NULL).
22 дек 16, 10:55    [20035509]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 686
SomewhereSomehow, большое спасибо!
Абсолютно исчерпывающе и понятно.
Самое обидное, я даже вроде читал об этом, и слушал лекцию (АФАИК Зайцева Дмитрия, если не путаю). А увидев в жизни - утупился.

Еще раз спасибо!
23 дек 16, 15:34    [20042168]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
AlxOlm
Member

Откуда:
Сообщений: 1
uaggster,

Эээ!?
А что спасибо? Как проблема то решилась?
10 сен 19, 17:23    [21967987]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить