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

Откуда: Москва
Сообщений: 895
Всем привет!
Есть таблица с индексами:

CREATE TABLE [dbo].[T_test_f]
(
	[Отделение] [varchar](100) NULL,
	[Date] [date] NULL,
	[Hh] [varchar](14) NULL,
	[ВидОперации] [varchar](32) NULL,
	[Val] [varchar](512) NULL,
	[Часы] [int] NULL,
	[Даты] [date] NULL,
	[Операции] [int] NULL,
	[Отделения] [int] NULL
)
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON dbo.T_test_f
(
	[Отделение] ASC,
	[ВидОперации] ASC,
	[Date] ASC,
	[Hh] ASC
)

CREATE NONCLUSTERED INDEX [No_ClusteredIndex] ON dbo.T_test_f
(
	[Операции] ASC,
	[Отделения] ASC
)INCLUDE ( [Val],[Часы],[Даты])

По этой таблице считаю нарастающий итог поля [Val], в разрезе: Отделения, Даты, Часы, Операциию.
Скрипт:
SELECT	F.Отделения, F.Операции, F.Даты,	F.Часы, 
		SUM(TRY_PARSE(CAST(s.Val AS VARCHAR(512)) AS REAL)) AS Val
FROM	DWH.dbo.T_test_f AS F
INNER JOIN DWH.dbo.T_test_f AS S
ON	F.Отделения = S.Отделения AND
	F.Операции = S.Операции AND 
	(
	CASE WHEN F.Даты = S.Даты AND  F.Часы >= S.Часы THEN 1 END = 1 OR
	CASE WHEN F.Даты > S.Даты THEN 1 END = 1)
GROUP BY F.Отделения,F.Даты,	F.Часы, F.Операции


В плане этого запроса есть два проблемных оператора (Sort и Merge), которые тормозят запрос.
У Merge реальное количество записей сильно больше ожидаемого. Подскажите как это поправить?
У Sort - ворнинг(spill level 2), но как я понял с ним ничего не поделаешь.

К сообщению приложен файл. Размер - 27Kb
14 дек 15, 15:39    [18558078]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yagrus2,

К сообщению приложен файл. Размер - 29Kb
14 дек 15, 15:39    [18558080]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yagrus2,
План запроса выгруженный в текст

К сообщению приложен файл (ПланЗапроса.txt - 1Kb) cкачать
14 дек 15, 15:43    [18558093]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Владислав Колосов
Member

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

странное условие... Сделайте группировку по union all вместо CASE и OR.
14 дек 15, 16:18    [18558331]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Yagrus2,

У вас сервер какой версии? Нарастающий итог давно считают через

ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:

http://www.sqlpassion.at/archive/2015/01/22/sql-server-windowing-functions-rows-vs-range/
14 дек 15, 16:32    [18558420]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yagrus2
CASE WHEN F.Даты = S.Даты AND  F.Часы >= S.Часы THEN 1 END = 1 OR
	CASE WHEN F.Даты > S.Даты THEN 1 END = 1

А это вы так дату-время проверяете на позже другой даты-времени ?
14 дек 15, 16:39    [18558466]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Glory
Yagrus2
CASE WHEN F.Даты = S.Даты AND  F.Часы >= S.Часы THEN 1 END = 1 OR
	CASE WHEN F.Даты > S.Даты THEN 1 END = 1

А это вы так дату-время проверяете на позже другой даты-времени ?


Скорее всего ТСу нужен нарастающий итог в окне OVER (PARTITION BY Даты ORDER BY Часы )
14 дек 15, 16:47    [18558506]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Glory,
Да.
14 дек 15, 16:53    [18558546]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yagrus2
Glory,
Да.

Мда уж
14 дек 15, 16:53    [18558551]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Glory,
Ваше предложение?
14 дек 15, 17:01    [18558599]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yagrus2
Glory,
Ваше предложение?

Сравнивать дату-время как дату-время
14 дек 15, 17:03    [18558611]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Glory,
Не помогло. Те же операторы и те же проблемы.
14 дек 15, 17:22    [18558713]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yagrus2
Не помогло. Те же операторы и те же проблемы.

Вы наверное поднимаете под этим "давай я прямо в заросе склею поля в дату-время " ?
14 дек 15, 17:25    [18558729]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Владислав Колосов
Member

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

а зачем использовать те же операторы? Там case не нужен и OR.
14 дек 15, 17:35    [18558773]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

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

а зачем использовать те же операторы? Там case не нужен и OR.


Изменил таблицу:
Полы [Даты]имеет формат [datetime] учитывает часы.

Запрос переписал так:
SELECT	F.Отделения, F.Операции, F.Даты,  
		SUM(TRY_PARSE(CAST(s.Val AS VARCHAR(512)) AS REAL)) AS Val
FROM	DWH.dbo.T_test_f AS F
INNER JOIN DWH.dbo.T_test_f AS S
ON	F.Отделения = S.Отделения AND
	F.Операции = S.Операции AND 
	F.Даты >= S.Даты 
GROUP BY F.Отделения,F.Даты, F.Операции
В мерже оценочное актуальное Количество сильно отличаются.
14 дек 15, 17:39    [18558788]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
SomewhereSomehow
Member

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

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

1. Версия сервера. Начиная с 2012 можно считать нарастающий итог гораздо удобнее и быстрее (судя по функции TRY_PARSE вы можете себе это позволить).
2. Преобразование типов. Влияет на оценки, влияет на производительность – избегайте.
3. Операция OR в условии соединения. Сервер может это преобразовать в два поиска, т.е. сделать то что советуют с union, но нет индексов, к тому же все это в выражении CASE. У него связаны руки.
4. Индексы по полям соединения должны быть.

В целом, остановитесь на пункте 1. Почитайте про оконные агрегаты, попробуйте сделать решения на них.

П.С.
Планы всегда лучше выкладывать в формате .sqlplan, и желательно, действительные. Хотя тут не важно, т.к. видно много недочетов по тексту запроса.

Удачи.
14 дек 15, 17:48    [18558822]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог и план запроса  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
a_voronin,
Спасибо за полезную ссылку. Запрос летает.
14 дек 15, 19:07    [18559147]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить