Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
invm
Nitro_Junkie
Потому как это мина замедленного действия.
А все время агрегировать таблицы целиком не мина?

Если исходить из корректности predicate pushdown, то нет. Но у нас он свой, пессимистичный, уже отлаженный на других СУБД, так что за эту часть не переживаю.

invm
Nitro_Junkie
Да и с ограничениями в INDEXED VIEW ничего особо не материализуешь.
Не увидел в ваших подзапросах препятствий для материализации.


Таких подзапросов 5%, в остальных 95%, где хоть одна фича из заявленного списка есть что делать? Вручную материализовать? Заколебешься в случаях чуть более сложных чем элементарный. У задач инкрементальности экспоненциальный рост сложности.
3 июн 14, 12:48    [16111978]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Гавриленко Сергей Алексеевич
Материализовывать можно не только индексированными представлениями.
Хотите выбирать быстро - держите данные в готовом виде.


Тут вопрос баланса скорости чтения \ записи. Если делать синхронно, транзакции начнут страдать (а запрос может использоваться раз в час). А асинхронно еще тяжелее чем синхронно реализовывать (про последнее ответил выше)
3 июн 14, 12:49    [16111993]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Я привел два плана демонстрируя, что если бы MS SQL считал что у CASE WHEN t0.k0 IS NOT NULL THEN t1.e0-t0.e0 ELSE NULL END IS NOT NULL cardinality такое же как у db.xx(t0.k0, t1.e0,t0.e0) (что соответствует реальности)

Вы про первое сообщение ?
Это в нем один запрос выполняется 2 минуты, а второй 1.5 секунды ?


Издеваетесь? В первом сообщении я декомпозированные запросы до проблем со статистикой привел.

Я про 2-е и 3-е сообщение с запросами и планами.
3 июн 14, 12:51    [16112000]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Nitro_Junkie
Glory
пропущено...

Вы про первое сообщение ?
Это в нем один запрос выполняется 2 минуты, а второй 1.5 секунды ?


Издеваетесь? В первом сообщении я декомпозированные запросы до проблем со статистикой привел.

Я про 2-е и 3-е сообщение с запросами и планами.


ЗЫ: Да я там немного заменил функцию, не db.xx(t0.k0, t0.e0, t1.e0) а db.notZero(t0.e0-t1.e0) использовал, но специально для вас проверил на db.xx - те же яйца.
3 июн 14, 12:52    [16112008]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Я про 2-е и 3-е сообщение с запросами и планами.

Во 2-ом и 3-ем ваших сообщений нет планов
Я об этом и говорю, что информация размазана манной кашей по столу - это здесь посмотрите, то - там, правда это уже для другого запроса и тп
3 июн 14, 12:55    [16112032]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Я про 2-е и 3-е сообщение с запросами и планами.

Во 2-ом и 3-ем ваших сообщений нет планов
Я об этом и говорю, что информация размазана манной кашей по столу - это здесь посмотрите, то - там, правда это уже для другого запроса и тп


Во 2-м и 3-м в которых есть планы.

Но вообще забудьте про 3-й
16111165

Вы согласны, что этот достаточно простой запрос можно выполнить за 1,5 секунды а не 2 минуты? То есть сервер ошибается?
3 июн 14, 13:03    [16112093]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Вы согласны, что этот достаточно простой запрос можно выполнить за 1,5 секунды а не 2 минуты? То есть сервер ошибается?


0	0	1	  |--Compute Scalar(DEFINE:([Expr1011]=CASE WHEN abs(CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END)>(0.0005) THEN CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END ELSE NULL END))
390	1	1	       |--Filter(WHERE:(CASE WHEN abs(CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END)>(0.0005) THEN CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END ELSE NULL END IS NOT NULL))


53011	1	925,401	  |--Compute Scalar(DEFINE:([Expr1011]=[mothercare].[dbo].[notZero](CONVERT_IMPLICIT(numeric(18,5),CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.00000) END-CASE WHEN [Expr1005] IS NOT NULL THEN [Expr1005] ELSE (0.00000) END,0))))
53011	1	925,401	       |--Filter(WHERE:([mothercare].[dbo].[notZero](CONVERT_IMPLICIT(numeric(18,5),CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.00000) END-CASE WHEN [Expr1005] IS NOT NULL THEN [Expr1005] ELSE (0.00000) END,0)) IS NOT NULL AND [Expr1005] IS NOT NULL))


Фильтрация в обоих случаях делается на самом последнем шаге. Т.е. это вычисление никак не влияет на оценку выбираемых из таблиц данных
3 июн 14, 13:16    [16112218]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Вы согласны, что этот достаточно простой запрос можно выполнить за 1,5 секунды а не 2 минуты? То есть сервер ошибается?


0	0	1	  |--Compute Scalar(DEFINE:([Expr1011]=CASE WHEN abs(CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END)>(0.0005) THEN CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END ELSE NULL END))
390	1	1	       |--Filter(WHERE:(CASE WHEN abs(CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END)>(0.0005) THEN CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.000) END-CASE WHEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL THEN CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END ELSE (0.000) END ELSE NULL END IS NOT NULL))


53011	1	925,401	  |--Compute Scalar(DEFINE:([Expr1011]=[mothercare].[dbo].[notZero](CONVERT_IMPLICIT(numeric(18,5),CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.00000) END-CASE WHEN [Expr1005] IS NOT NULL THEN [Expr1005] ELSE (0.00000) END,0))))
53011	1	925,401	       |--Filter(WHERE:([mothercare].[dbo].[notZero](CONVERT_IMPLICIT(numeric(18,5),CASE WHEN [Expr1010] IS NOT NULL THEN [Expr1010] ELSE (0.00000) END-CASE WHEN [Expr1005] IS NOT NULL THEN [Expr1005] ELSE (0.00000) END,0)) IS NOT NULL AND [Expr1005] IS NOT NULL))


Фильтрация в обоих случаях делается на самом последнем шаге. Т.е. это вычисление никак не влияет на оценку выбираемых из таблиц данных


Так ошибается она в предыдущем фильтре:

53011(!)	1	1	            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t0].[Picking_skuPickingDetail], [t1].[Picking_pickingOrderPicking]))
53011	1	1(!)	                 |--Filter(WHERE:(CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL))
0	0	5573,791	                 |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1042]=(0) THEN NULL ELSE [Expr1043] END))


И это приводит к nested loop.
3 июн 14, 13:22    [16112274]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Так ошибается она в предыдущем фильтре:

53011(!)	1	1	            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t0].[Picking_skuPickingDetail], [t1].[Picking_pickingOrderPicking]))
53011	1	1(!)	                 |--Filter(WHERE:(CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL))
0	0	5573,791	                 |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1042]=(0) THEN NULL ELSE [Expr1043] END))



И это приводит к nested loop.

Только этот фильтр во вложенных запросах !
И даже не фильтр, а вычисляемое поле с агрегатом
CASE WHEN ABS(SUM(t0."Picking_quantityPickingDetail"))>0.0005 THEN SUM(t0."Picking_quantityPickingDetail") ELSE NULL END AS e0
3 июн 14, 13:28    [16112326]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Так ошибается она в предыдущем фильтре:

53011(!)	1	1	            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t0].[Picking_skuPickingDetail], [t1].[Picking_pickingOrderPicking]))
53011	1	1(!)	                 |--Filter(WHERE:(CASE WHEN abs([Expr1004])>(0.0005) THEN [Expr1004] ELSE NULL END IS NOT NULL))
0	0	5573,791	                 |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1042]=(0) THEN NULL ELSE [Expr1043] END))



И это приводит к nested loop.

Только этот фильтр во вложенных запросах !
И даже не фильтр, а вычисляемое поле с агрегатом
CASE WHEN ABS(SUM(t0."Picking_quantityPickingDetail"))>0.0005 THEN SUM(t0."Picking_quantityPickingDetail") ELSE NULL END AS e0


Так в первом сообщении я просто вытащил этот вложенный запрос на поверхность, чтобы показать проблемы со статистикой. Я действительно настолько непонятно объясняю, или это Glory словить суть не может?
3 июн 14, 13:34    [16112371]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Так в первом сообщении я просто вытащил этот вложенный запрос на поверхность, чтобы показать проблемы со статистикой. Я действительно настолько непонятно объясняю, или это Glory словить суть не может?

Офигеть.
Вы серьезно считаете, что оптимизатор создает план частями ?
Типа сначала для подзапросов, а потом для внешних запросов ?
Вы не только вытащили вложенный запрос. Вы еще и переместили CASE из вычисления столбца в where
3 июн 14, 13:40    [16112433]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Так в первом сообщении я просто вытащил этот вложенный запрос на поверхность, чтобы показать проблемы со статистикой. Я действительно настолько непонятно объясняю, или это Glory словить суть не может?

Офигеть.
Вы серьезно считаете, что оптимизатор создает план частями ?
Типа сначала для подзапросов, а потом для внешних запросов ?
Вы не только вытащили вложенный запрос. Вы еще и переместили CASE из вычисления столбца в where


Ну ошибка же сохранилась. Понятно что при cost-based'е она могла уйти, но не ушла же... В любом случае если вам так нравится, забудьте про первое сообщение. Во втором явная ошибка в достаточно простом случае налицо. У вас есть какая нибудь идея почему она происходит, и как с ней бороться кроме оборачивания в scalar-valued функцию?
3 июн 14, 13:45    [16112485]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
немного теории
3 июн 14, 13:45    [16112487]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Во втором явная ошибка в достаточно простом случае налицо.

Ага, рука на лицо.
3 июн 14, 13:47    [16112512]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Во втором явная ошибка в достаточно простом случае налицо.

Ага, рука на лицо.


Ясно, по существу сказать нечего :(
3 июн 14, 15:27    [16113459]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Maxx
немного теории


Там конечно много интересной информации. Но проблему определения cardinality для CASE WHEN не освещает :(
3 июн 14, 15:27    [16113464]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Ясно, по существу сказать нечего

Честно ?
На доказательство глупых предположений никогда не будет что сказать по существу.
С таким же успехом вы можете провести свзять плана выполнения с фазой луны на вашей широте.
3 июн 14, 15:29    [16113476]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
Но проблему определения cardinality для CASE WHEN не освещает :(

Потому что наверное только вы знаете о существовании такой проблемы
3 июн 14, 15:29    [16113483]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
Но проблему определения cardinality для CASE WHEN не освещает :(

Потому что наверное только вы знаете о существовании такой проблемы


А то что запрос можно за 1,5 секунды выполнить (например добавив хинт), вместо 2 минут, по вашему не проблема? Какой-то толстый троллинг...
3 июн 14, 15:32    [16113499]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Maxx
Member [скрыт]

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

там море полезной информации о ...
куда смотреть
как смотреть
откуда считать
3 июн 14, 15:33    [16113507]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nitro_Junkie
А то что запрос можно за 1,5 секунды выполнить (например добавив хинт), вместо 2 минут, по вашему не проблема? Какой-то толстый троллинг...

А еще запрос можно переписать и он тоже будет выполняться быстрее.
А на пустых таблицах вообще обой запрос будет выполняться меньше секунды
3 июн 14, 15:35    [16113525]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Glory
Nitro_Junkie
А то что запрос можно за 1,5 секунды выполнить (например добавив хинт), вместо 2 минут, по вашему не проблема? Какой-то толстый троллинг...

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


А можно вообще не использовать SQL Server и что? Вопрос в том что затыкать решето руками глупая затея. То есть важно разобраться почему такая проблема у MS SQL, и где она еще может возникнуть...
3 июн 14, 16:05    [16113767]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1078
Maxx
Nitro_Junkie,

там море полезной информации о ...
куда смотреть
как смотреть
откуда считать


Я просмотрел по диагонали насколько мог. Но копать настолько глубоко ради одной проблемы (пока, тьфу-тьфу-тьфу) убиться можно...
3 июн 14, 16:06    [16113776]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Nitro_Junkie
Но копать настолько глубоко ради одной проблемы (пока, тьфу-тьфу-тьфу) убиться можно...

т.е. копаться сами не хотим - покопайте кто-то за меня и расскажите,прикольно однако
3 июн 14, 16:15    [16113852]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с cardinality оператора CASE WHEN  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Приведу репро, иллюстрирующее проблему ТС, для желающих покопаться. Использую БД opt для демонстрации, взять можно отсюда.

Небольшие модификации, чтобы воспроизвести проблему:
+
set nocount on;
go
alter table t1 alter column b int null;
alter table t2 add e int;
alter table t3 drop pk_c;
go
update t1 set b = null where b = 10;
update t2 set e = c;
go
insert t3 select * from t3
go 10
update statistics t1 with fullscan;
create statistics s_e on t2(e) with fullscan;
go


Репро:
set statistics io, time on;

-- default
with cte as (
	select 
		*
	from
		(select t1.b, s1 = sum(t1.c) from t1 group by t1.b) t1
		left join (select t2.c, s2 = sum(t2.e) from t2 group by t2.c) t2 on t1.b = t2.c
	where 
		(case when t1.b is not null then t1.s1-t2.s2 else null end) is not null
)
select *
from	
	cte 
	left join t3 on t3.c = cte.b
;
-- forced hash join
with cte as (
	select 
		*
	from
		(select t1.b, s1 = sum(t1.c) from t1 group by t1.b) t1
		left join (select t2.c, s2 = sum(t2.e) from t2 group by t2.c) t2 on t1.b = t2.c
	where 
		(case when t1.b is not null then t1.s1-t2.s2 else null end) is not null
)
select *
from	
	cte 
	left hash join t3 on t3.c = cte.b
;
set statistics io, time off;

Статистика выполнения:
автор
Table 't2'. Scan count 5, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't3'. Scan count 4, logical reads 163053, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 22076 ms, elapsed time = 6399 ms.

Table 't1'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't2'. Scan count 5, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't3'. Scan count 5, logical reads 1647, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 155 ms, elapsed time = 724 ms.


Планы:
Картинка с другого сайта.

Причина медленного выполнения:
В выделенном куске фильтр оценивается в 1 строку, тогда как реально там 99 строк, это приводит к выбору неэффективной стратегии соединения и помещению на внутреннюю сторону соединения вложенными циклами сканирования таблицы, которое производит в 98 раз больше чтений и замедляет выполнение почти в 10 раз.

Если посмотреть, какой предикат оценивает сервер, то можно увидеть такое выражение:
CASE WHEN [opt].[dbo].[t1].[b] IS NOT NULL THEN [Expr1003]-[Expr1009] ELSE NULL END IS NOT NULL


Если раскрыть все выражения (Expr1003, Expr1009, …) до базовых столбцов и таблиц, то получится, что оценивается вот такой монстрик:
(
		CASE WHEN 
			[opt].[dbo].[t1].[b] IS NOT NULL 
		THEN 
			CASE WHEN COUNT_BIG([opt].[dbo].[t1].[c])=(0) THEN NULL ELSE SUM([opt].[dbo].[t1].[c]) END
			-
			CASE WHEN COUNT_BIG([opt].[dbo].[t2].[e])=(0) THEN NULL ELSE SUM([opt].[dbo].[t2].[e]) END
		ELSE 
			NULL 
		END 
	) IS NOT NULL

Как оцениваются вложенные кейсы я немного писал вот в этом посте Немного необычный CASE. Читайте под спойлером «Математика Cardinality Estimation». В описанном в посте случае, для оценки использовались формулы суммы прогрессии и теории вероятности.

Кроме того, оценка меняется от версии к версии, там описано поведение для 2008R2, для 2012 по-другому, для 2014 совсем по-другому.

Не вдаваясь в сложности конкретных формул оценки, которые, в общем, неизвестны для всех возможных случаев (по крайней мере, лично мне), можно сказать, что могут использоваться различные подходы. В данном случае, поведение похоже на баг, но это не обязательно так, возможно это простое ограничение модели и ваше выражение оказалось слишком сложным, чтобы комбинируя базовые селективности, оптимизатор мог его корректно оценить. Лучше всего, конечно, ответят сами Майкрософт, напишите в Connect.

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

Но вам оно не подходит. В таком случае, вы сами уже нашли какие-то подходы. Например, скалярная функция, черный ящик для оптимизатора, там используются просто догадки, которые отличаются от оценки в 1-у строку и вам больше подходят. Минус такого подхода – что это пляски с бубном, т.к. сегодня работает - завтра нет, не говоря о минусах самой скалярной функции. Надежнее всего – простые решения.

Можно попробовать переписать условие. Вот это:
where (case when t1.b is not null then t1.s1-t2.s2 else null end) is not null

Имхо, странное условие, зачем вычислять разность и сравнивать с NULL? И еще все это оборачивать в кейс?
Я, конечно, не знаю вашей логики, а может вы просто упростили, но условие выше, это не то же самое что и:
where t1.b is not null and t1.s1 is not null and t2.s2 is not null

?

Если нет, то подумайте, может еще как-то можно переписать/упростить.

Кстати, в 2014 мое репро не работает, там оценка получается нормальной. У вас, я так понял, в 2014 тоже такой косяк. Убедитесь, что в 2014 используется новый Cardinality Estimator, проверив в плане свойство CardinalityEstimationModelVersion=120, т.к. если вы податтачили, например, БД из предыдущей версии сиквела, и не поменяли уровень совместимости, то будет использоваться старая версия оценщика CardinalityEstimationModelVersion=70.

Если используется новая версия, а оценка по-прежнему одна строка, значит такая статистика, и скорее всего это ограничение модели, приводите репро для 2014, будет время - посмотрим.

Кстати, я смотрю у вас там в других запросах много где COALSECE используется, имейте ввиду, что он тоже раскрывается как CASE, возможно лучше использовать ISNULL (только помните про типы данных).

П.С.
Планы, сложнее нескольких строчек, лучше выкладывать как .sqlplan - удобнее смотреть.
3 июн 14, 16:22    [16113964]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить