Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Доброе время суток!

Есть запрос вида

;
wite
cteResult1 as
(
 select ...
    from ....
),

cteResult2 as
(
 select ...
    from ....
)
,

cteSum as
(
select * from cteResult1

union all --я ненавижу этот юнион, но структура базы такая кривая

select * from cteResult2
)

select ...
  from cteSum
group by grouping set


Вопрос даже не в том "почему там этот юнион". Это не воркэраунд, не костыль и т. п. для запроса - это тупо разные раблицы для одной сущности по факту а пользователю отображаются "как одна" и количество считается.

Проблема в том что в cteSum есть результат только с первого cte, а второй "без результата" и когда я коментирую
union alll select 

Запрос работает быстрее и та часто что в первом ЦТЕ имеет явно более хороший план. Как только возвращаю второе ЦТЕ - план портится даже для первой части (помогает только top 0).

Я уже пытался использовать и другие костыли и шаманства вроде

select coalesce(c1.field, c2.field) as field
  from (selec 1 as variant union all select 2 variant) t
 inner
  join cte1 c1
    on c1.variant = 1
  inner
  join cte2 c2
    on c1.variant = 2


Но, не помогает.

Хотел бы "зафиксировать план" только для одного СТЕ. Гонять такое через временные таблички будет накладно!

Заранее спасибо!
9 июл 13, 19:23    [14543439]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
Проблема в том что в cteSum есть результат только с первого cte, а второй "без результата"

У вас всегда во втором CTE нет данных? Попробуйте (TOP @i) OPTIMIZE FOR @i=1[/quot]
NIIIK
Запрос работает быстрее и та часто что в первом ЦТЕ имеет явно более хороший план. Как только возвращаю второе ЦТЕ - план портится даже для первой части (помогает только top 0).
Мне интересно, а вы планы ко всем своим запросам принципиально не выкладываете?
9 июл 13, 19:37    [14543466]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
нет, не всегда,
выкладываю
не не принципиально
1) в данном случае действительно нет необходимости
2) так проще объяснить - поверте, если вы будите видеть много лишней хрени которая там "как наследие" вам она тольк мешать будет
9 июл 13, 19:43    [14543473]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
нет, через переменную вообще не срабатывает, только хард код top 0
9 июл 13, 19:54    [14543508]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А вы выяснили причину плохого плана?
Не хватает времени для полной компиляции?

Стандартный набор.
OPTIMIZE FOR - один из, подбираем такие параметры входных параметров при котором план хороший. И тем самым его фиксируем.

Можно добавлять дополнительные параметры типа "AND @StupidVar = 0", а оптимизить для @StupidVar = 1. Попробуйте.
Наглядный пример, посмотрите на сколько разнятся планы одинаковых запросов:
DECLARE	@True Bit = 1

SELECT	*
FROM	sys.objects
WHERE	@True = 1
OPTION (OPTIMIZE FOR (@True = 0))

SELECT	*
FROM	sys.objects
WHERE	@True = 1
OPTION (OPTIMIZE FOR (@True = 1))
9 июл 13, 20:57    [14543631]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior,

Да, идею я понял. Тот же мой "select Top (@fakeTop)" было аналогичной попыткой,
но мой то случай - это не разные параметры, а разные заросы в принципе.

Запрос начинает работать быстро как только я делаю

;
wite
cteResult1 as
(
 select ...
    from ....
),

cteResult2 as
(
 select ...
    from ....
)
,

cteSum as
(
select * from cteResult1

/*
union all --я ненавижу этот юнион, но структура базы такая кривая

select * from cteResult2*/
)

select ...
  from cteSum
group by grouping set


При этом второй (закоментированный) select в том случае (и в большенстве тест-кейсов) ничего не возвращает (хотя это не означает что и "не будет". И при этом та часто плана выполнения что отвечает за cteResult1 меняется.
А какой-нить KEEPFIXED PLAN использовать тут варианта не будет
1) его нельзя делать на уровне подзапроса/СТЕ
2) правильно выполняется только когда "другой запрос без одного СТЕ".

В целом я проверил и

 --where @fakeTop = 1
 where 1 = 0


работает только хард-код вариант, с переменными - нет (независимо от значений).

Кстати я бы не сказал что планы этих частей cteResult1 сильно отличаются, но отличается
1) быстрый (хоть и менее красивый) использует Параллелизм
2) Index scan ключевой таблицы занимает 62% в медленом варианте и 72% в быстром (с параллелизмом)
3) Могу добится Index seek методом "создать индекс который предложил СКЛ сервер", но запрос дюрейшн запроса растёт значительно (хотя план действительно красивее). Этот индекс мне не нравится тем что по сути одно поле содержится в основном списке, которое "ссылка на родителя" (а оно даже не ограничивает результат поиска и у каждого есть) при этом все остальные поля в include, даже те которые реально ограничивают результат поиска в том числе и первичный ключ (который джойнится с containsTable).

Сорри если "грузанул лишним". Ну не знаю как ещё упростить.
10 июл 13, 00:09    [14544321]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
aleks2
Guest
NIIIK
Проблема в том что когда я коментирую
union alll select 

Запрос работает быстрее и та часто что в первом ЦТЕ имеет явно более хороший план. Как только возвращаю второе ЦТЕ - план портится даже для первой части (помогает только top 0).



Страдалец.
Проще надо быть

;with
cteResult1 as
(
 select ...
    from ....
),

cteResult2 as
(
 select ...
    from ....
)

select ...
from
(select ...
  from cteResult1
group by grouping set) R1
union all --я ненавижу этот юнион, но структура базы такая кривая
(select ...
  from cteResult2
group by grouping set) R2

group by grouping set
10 июл 13, 08:47    [14544734]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
aleks2,

Ну GROUPING SETS нелинеен, нужно ещё будет удалить из результата строку.
;WITH GS AS (
	SELECT	type, name, schema_id, Count(*)	AS Cnt
	FROM	sys.objects O
	GROUP BY GROUPING SETS (type,name,schema_id)
)	SELECT	type, name, schema_id, Sum(Cnt) AS Cnt
	FROM GS
	GROUP BY GROUPING SETS (type,name,schema_id)
/*
HAVING	   type		IS NOT NULL
	OR name		IS NOT NULL
	OR schema_id	IS NOT NULL
*/
EXCEPT	SELECT * FROM GS
10 июл 13, 09:58    [14544993]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вариант отдельно считать количества рассматривается,
только потом очень головняково соединять.

может быть даже full join + обычное сложение будет удобнее и в некоторых группах по несколько строковых столбцов.
Да и условия там изначально "трёхэтажные".

Изначально там идёт "одной таблицей" потому что всё равно этот юнион нужен потому что есть ещё отображение страницы с общим упорядочиванием (собственно поэтому ненавижу этот юнион в основном).
10 июл 13, 11:42    [14545754]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
aleks2,

вариант применить UNION к подсчитанным значения тоже "Не прошёл".
Причём это уже страннее. Как только коментируешь результат группировки пустого ЦТЕ - всё работает быстрее.
11 июл 13, 18:26    [14554443]     Ответить | Цитировать Сообщить модератору
 Re: Зафиксировать план запроса для одного CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
После установки SP2 вариант первичный начал работать быстро.
Вариант с "сначала сгруппировать" продолжает медленно.
14 июл 13, 12:06    [14563362]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить