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

Откуда:
Сообщений: 12
Пытаюсь посчитать накопительный итог для 74 млн записей. Все это дело считалось больше часа, пришлось остановить процесс.
Запрос следующий:
select Strana
	,Tv
	,Unit
	,(Select sum(Vl)
	from [TEI_DATA_14151_PART] b
	where b.Strana = a.Strana
	and b.Tv = a.Tv
	and b.Unit = a.Unit
	and YEAR(b.date) =  YEAR(a.date)
	and MONTH(b.date) <= MONTH(a.date)
	and b.Dl = a.Dl
	and b.Strana_type = a.Strana_type) as Vl
	,Date
	,Dl
	,Strana_type
	,2 as DType
into #TEMP_14151_dtype2
from [TEI_DATA_14151_PART] a
where dl = 4

MsSQL 2008

В плане выполнения 93% берет на себя Index Spool(Eager Spool)
В точно такой же таблице, отличающейся лишь наименованием, эта операция заменена на clustered index seek и расчет проходит за 20 мин, но и данных там меньше в 1.5 раза
На обоих таблицах кластерный индекс по всем полям, кроме Vl.
Подскажите, в чем может быть дело и как можно ускорить этот расчет?
10 мар 15, 16:47    [17365858]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
Видимо SQL Server неправильно выбирает индекс.
Можно обновить статистику или захардкодать какой индекс использовать(плохой вариант)

Попробуй так переписать запрос
select Strana
,Tv
,Unit
,sum(Vl) over (partition by a.Strana, a.Tv, a.Unit, convert(varchar(7),a.date,121), a.Dl, a.Strana_type) as Vl
,Date
,Dl
,Strana_type
,2 as DType
into #TEMP_14151_dtype2
from [TEI_DATA_14151_PART] a
where dl = 4
10 мар 15, 16:59    [17365958]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
a_voronin
Member

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

Джойн сам на себя это каменный век для нарастающих итогов. Изучайте аналитические функции и принцип скользящего окна. Версия сервера какая?

https://technet.microsoft.com/en-us/library/ms189461(v=sql.110).aspx
10 мар 15, 17:14    [17366062]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
ToastedHippy
Member

Откуда:
Сообщений: 12
SHok_by, не происходит агрегации.
Думал уже о таком варианте, но не знаю, как заменить условие
YEAR(b.date) =  YEAR(a.date)
and MONTH(b.date) <= MONTH(a.date)

Т.е. например на март нужно январь + февраль + март
10 мар 15, 17:14    [17366063]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
вот так
  ,sum(Vl) over (partition by a.Strana, a.Tv, a.Unit, convert(varchar(7),a.date,121), a.Dl, a.Strana_type order by a.date) as Vl
10 мар 15, 17:15    [17366079]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
тогда
  ,sum(Vl) over (partition by a.Strana, a.Tv, a.Unit, YEAR(a.date), a.Dl, a.Strana_type order by a.date) as Vl
10 мар 15, 17:16    [17366091]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
не, тоже не то, но в этом ключе думаю можно сделать.
надо на небольшом примере по экспериментировать.
10 мар 15, 17:18    [17366106]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
ToastedHippy
Member

Откуда:
Сообщений: 12
a_voronin, находил тут такую тему:
Из нее я подумал, что OVER Clause не поможет мне в MSSQL 2008
версия: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
10 мар 15, 17:24    [17366163]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
~~~
Guest
ToastedHippy,

Попробуй так, если я правильно понял задачу

вместо:
YEAR(b.date) =  YEAR(a.date)
and MONTH(b.date) <= MONTH(a.date)



поставить
BETWEEN CAST(DATENAME(YEAR,GETDATE()) as DATETIME) AND DATEADD(DAY,-1, CONVERT(char(6), DATEADD(MONTH,1,GETDATE()),112)+'01');
10 мар 15, 17:26    [17366169]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
хмхмхм
Guest
ToastedHippy,

http://sqlperformance.com/2012/07/t-sql-queries/running-totals
10 мар 15, 17:35    [17366221]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
o-o
Guest
ToastedHippy
я подумал, что OVER Clause не поможет мне в MSSQL 2008
версия: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)

в 2008 накопительный итог эффективнее курсором. тот самый редкий случай, когда оно лучше
10 мар 15, 17:48    [17366278]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
ToastedHippy
Member

Откуда:
Сообщений: 12
o-o, судя по статье
хмхмхм
ToastedHippy,

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

кажется да, придется использовать курсор. Даже в голову не пришло, использовать курсор на таком кол-ве записей.
10 мар 15, 17:51    [17366304]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
хмхмхм
Guest
ToastedHippy
o-o, судя по статье
хмхмхм
ToastedHippy,

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

кажется да, придется использовать курсор. Даже в голову не пришло, использовать курсор на таком кол-ве записей.


update тоже неплох, особенно если важно CPU.
10 мар 15, 18:07    [17366398]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
хмхмхм
ToastedHippy
o-o, судя по статье пропущено...

кажется да, придется использовать курсор. Даже в голову не пришло, использовать курсор на таком кол-ве записей.


update тоже неплох, особенно если важно CPU.


А ещё лучше перейти на SQL 2012/2014
10 мар 15, 19:50    [17366806]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
ToastedHippy
Member

Откуда:
Сообщений: 12
a_voronin
А ещё лучше перейти на SQL 2012/2014
К сожалению это не мне решать((
Написал такой курсор. (в таблице #test 192734 строк из полной таблицы)
if OBJECT_ID ('tempdb..#TEMP_14151_dtype2') is not null
			drop table #TEMP_14151_dtype2;
			
		create table #TEMP_14151_dtype2 (
			[STRANA] [nvarchar](50) NOT NULL,
			[TV] [nvarchar](50) NOT NULL,
			[UNIT] [int] NOT NULL,
			[VL] [float] NOT NULL,
			[date] [datetime] NOT NULL,
			[dl] [int] NOT NULL,
			[STRANA_TYPE] [int] NOT NULL,
			[DType] [int] NOT NULL)
		
		declare @Strana nvarchar(50)
		,@Tv nvarchar(10), @Unit int, @Vl float
		,@Date date, @Strana_type int
		
		declare curs cursor for
		select Strana
			,Tv
			,Unit
			,Vl
			,Date
			,Strana_type
		from #test
			
		open curs
		fetch next from curs
		into @Strana, @Tv, @Unit, @Vl, @Date, @Strana_type
		
		while @@FETCH_STATUS = 0
		begin
			insert into #TEMP_14151_dtype2
			select Strana
				,Tv
				,Unit
				,SUM(Vl) as Vl
				,@Date as Date
				,Dl
				,Strana_type
				,DType
			from #test
			where Strana = @Strana
				and Tv = @Tv
				and Unit = @Unit
				and year(Date) = year(@Date)
				and MONTH(date) <= MONTH(@Date)
				and dl = 4
				and Strana_type = @Strana_type
			group by Strana
				,Tv
				,Unit
				,Dl
				,Strana_type
				,DType
		end
		
		close curs
		deallocate curs

за 40 мин импортировалось только 99396. Что я не так делаю?
11 мар 15, 10:46    [17368354]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
ToastedHippy

			insert into #TEMP_14151_dtype2
			select Strana
				,Tv
				,Unit
				,SUM(Vl) as Vl
				,@Date as Date
				,Dl
				,Strana_type
				,DType
			from #test
			where Strana = @Strana
				and Tv = @Tv
				and Unit = @Unit
				and year(Date) = year(@Date)
				and MONTH(date) <= MONTH(@Date)
				and dl = 4
				and Strana_type = @Strana_type
			group by Strana
				,Tv
				,Unit
				,Dl
				,Strana_type
				,DType


за 40 мин импортировалось только 99396. Что я не так делаю?


не приводите план запроса и скрипт таблицы #test
11 мар 15, 10:51    [17368393]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
Glory
Member

Откуда:
Сообщений: 104751
ToastedHippy
Что я не так делаю?

Зачем внутри цикла по курсору запрос ?
Ваш цикл по курсору должен суммировать в переменные значения из текущей записи курсора
11 мар 15, 10:56    [17368429]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

а почему бы не бежать курсором по уже посчитанному (вместо целой таблицы)

select sum(...) from #test... group by Strana
				,Tv
				,Unit
				,Dl
				,Strana_type
				,DType
                               , year(Date)
				, MONTH(date)


и не накапливать сумму... строчек обрабатываемых курсором то меньше будет...
11 мар 15, 10:58    [17368440]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
aleks2
Guest
Какие курсоры?
1. Индексированное представление на
Select b.Strana, b.Tv, b.Unit, m.Start, b.Dl, b.Strana_type, sum(Vl) sumVI
from [TEI_DATA_14151_PART] b inner join Months m on m.Start <=b.date and b.date < m.StartNext
group by b.Strana, b.Tv, b.Unit, b.Dl, b.Strana_type, m.Start

2. Выбирать и суммировать из представления.
11 мар 15, 11:29    [17368654]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
А если заменить подзапрос функцией ? Пробовали ?
11 мар 15, 12:48    [17369221]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Вы сначала сделайте

			group by Strana
				,Tv
				,Unit
				,Dl
				,Strana_type
				,DType

загоните результат во временную таблицу, а потом запускайте по ней курсор.
11 мар 15, 12:53    [17369264]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
ToastedHippy
Member

Откуда:
Сообщений: 12
aleks2
Какие курсоры?
1. Индексированное представление на
Select b.Strana, b.Tv, b.Unit, m.Start, b.Dl, b.Strana_type, sum(Vl) sumVI
from [TEI_DATA_14151_PART] b inner join Months m on m.Start <=b.date and b.date < m.StartNext
group by b.Strana, b.Tv, b.Unit, b.Dl, b.Strana_type, m.Start

2. Выбирать и суммировать из представления.


LSV
если заменить подзапрос функцией ? Пробовали ?

Остановился на варианте написать курсор т.к. задача срочная и времени на эксперименты нет. Да и коллеги постарше тоже голосуют за курсор в этом конкретном случае.

a_voronin
Вы сначала сделайте

group by Strana
	,Tv
	,Unit
	,Dl
	,Strana_type
	,DType

загоните результат во временную таблицу, а потом запускайте по ней курсор.

Не понял зачем мне группировать по всем строкам, кроме дат? Это же даст абсолютное значение по строке за весь период?!
11 мар 15, 13:07    [17369377]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
Okmor
Member

Откуда:
Сообщений: 132
ToastedHippy,
select Strana
	,Tv
	,Unit
	,(Select sum(Vl)
	from [TEI_DATA_14151_PART] b
	where b.Strana = a.Strana
	and b.Tv = a.Tv
	and b.Unit = a.Unit
	and YEAR(b.date) =  YEAR(a.date) --здесь теряется связь с индексом!!!
	and MONTH(b.date) <= MONTH(a.date)  --здесь теряется связь с индексом!!!
	and b.Dl = a.Dl
	and b.Strana_type = a.Strana_type) as Vl
	,Date
	,Dl
	,Strana_type
	,2 as DType
into #TEMP_14151_dtype2
from [TEI_DATA_14151_PART] a
where dl = 4

Сначала сгруппируйте по всему кроме дат во временную таблицу. Проиндексируйте ее и после считайте накопительный итог.
11 мар 15, 13:16    [17369456]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго считает накопительный итог  [new]
aleks2
Guest
ToastedHippy
Остановился на варианте написать курсор т.к. задача срочная и времени на эксперименты нет. Да и коллеги постарше тоже голосуют за курсор в этом конкретном случае.


Если задача разовая - вместо индексированного представления - просто сгруппировать по

b.Strana, b.Tv, b.Unit, b.Dl, b.Strana_type, YEAR(b.date), MONTH(b.date)

и сунуть во временную таблицу. Курсор, все равно, просрет.
11 мар 15, 13:23    [17369546]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить