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."

Чтобы понять почему, нужно посмотреть на конструкцию


Когда вы пишите "столбец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.

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


И не может получить такое же условие, когда имеется параметр и переменное условие соединения:

Любопытный гость,

Я объяснил выше почему, потому что оптимизатор не знает какое будет значение параметра во время выполнения и должен гарантировать "универсальный план" под все возможные значения, а предикат соединения может меняться из-за параметра, а hash-у обязательно нужен один equi join, вот простой пример:


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

В первом случае, я ограничил выбор только хэш соединением и в качестве значения параметра задал 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С-ник, так что на этом умолкаю =)

Любопытный гость,

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

По данной проблеме далее в комментариях:
hackace April 6, 2012 at 8:11 pmHi 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 2


Craig Freedman April 10, 2012 at 9:53 amHi,

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:




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, скажите что требуете доработать алгоритм и почему, вам ответят с большими подробностями почему это так или наоборот, что доработают (в чем я сомневаюсь).

SomewhereSomehow,
опечатка: "лучше чем у него у меня не получится"