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

Откуда:
Сообщений: 164
Есть таблица, содержащая 7 млн строк, в этой таблице по строкам записано какое-то количество по 5000 различным объектам

За приемлемое время необходимо получить нарастающее количество по строкам (нарастающее количество по каждому объекту с учетом упорядочивания) (<5 минут)


Буду рад любому дельному совету


Ниже представлен пример создания такой таблицы, в примере по умолчанию установлено малое количество строк и объектов

--select @@version 
--Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 
DECLARE @КоличествоОбъектов int
DECLARE @КоличествоСтрок int

Set @КоличествоОбъектов = 5	--! здесь должно быть 5000
Set @КоличествоСтрок = 7000 --! здесь должно быть 7000000


--Последовательность (вспомогательная таблица)
if object_id('tempdb..#Последовательность10') is not NULL
begin
    drop table #Последовательность10
end
Create Table #Последовательность10(i int)
Declare @i int
set @i = 1
Set nocount On 
While (@i<=10)
Begin
	Insert into #Последовательность10(i)Values(@i)
	set @i = @i + 1
End
Set nocount Off
--Select top * from #Последовательность10
---------------------------------------------------------------
--Создадим таблицу Объектов
if object_id('tempdb..#Объекты') is not NULL
begin
    drop table #Объекты
end
Select 
	cast(newid() as binary(16)) as ID
into #Объекты
From #Последовательность10 as t1
Cross join #Последовательность10 as t2
Cross join #Последовательность10 as t3
Cross join #Последовательность10 as t4 
Where 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i <= @КоличествоОбъектов --5000
---------------------------------------------------------------
--Создадим таблицу строк
if object_id('tempdb..#Строки') is not NULL
begin
    drop table #Строки
end
Select 
	IDENTITY(int, 1,1) as Номер,
	ID as Объект,
	1 + floor(20*rand(checksum(newid()))) as Количество
Into #Строки
From #Объекты
cross Join
(
	Select 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i as N
	From #Последовательность10 as t1
	Cross join #Последовательность10 as t2
	Cross join #Последовательность10 as t3
	Cross join #Последовательность10 as t4 
	Where 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i <=(@КоличествоСтрок/@КоличествоОбъектов)--1400
) as ttt
---------------------------------------------------------------
/*
Select top 100 * From #Объекты
Select count(*) From #Объекты
Select top 100 * from #Строки
Select count(*) from #Строки
*/

--?Запрос ниже необходимо как-то оптимизировать
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
Select	t1.Номер,
		Sum(t2.Количество) as НарастающееКоличество
Into #СтрокиСНарастающимКоличеством
From #Строки as t1
inner join #Строки as t2
	ON	t1.Объект = t2.Объект 
	and	t1.Номер >= t2.Номер 
Group by t1.Номер


Select Count(*) From #СтрокиСНарастающимКоличеством
16 янв 12, 12:49    [11910072]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ALKIR, индексов нема чтоль совсем? Ну и на счет наростающего итога в FAQ вы ессно глядели?
16 янв 12, 13:04    [11910205]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
kDnZP
ALKIR, индексов нема чтоль совсем? Ну и на счет наростающего итога в FAQ вы ессно глядели?


пробовал создавать индексы, для данного примера (для Номера, для Объекта, для Номера+Объект), но пока результат у меня отрицательный, скорость только замедляется :(


дайте пожалуйста тынц на то что конкретно вы имели ввиду - FAQ по нарастающий итог
16 янв 12, 13:23    [11910398]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Вродь так чуть быстрее:
-- CREATE INDEX idx ON #Строки(Объект,Номер)
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
SELECT  t1.Номер, t2.НарастающееКоличество
Into #СтрокиСНарастающимКоличеством
FROM    #Строки t1
CROSS APPLY ( SELECT    SUM(t2.Количество) AS НарастающееКоличество
              FROM      #Строки t2
              WHERE     t2.Объект = t1.Объект
                        AND t1.Номер >= t2.Номер
            ) t2
16 янв 12, 13:24    [11910418]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ALKIR
дайте пожалуйста тынц на то что конкретно вы имели ввиду - FAQ по нарастающий ито

Ну я знаю, что тут есть несколько методов, т.е. можно потестировать на предмет скорострельности. Сразу скажу, что сам FAQ особо не читаю, так что самый быстрый способ подсказать врядли смогу. Единственное могу предполагать, что циклы, курсоры, рекурсивное СТЕ - скорее всего будут не самыми быстрыми. Но с другой стороны группировки тоже не из легких операций. Вобщем-то тестировать надобно.
16 янв 12, 13:28    [11910452]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
я пробовал. Курсор быстрее всего у меня получался. Что и логично.
16 янв 12, 13:35    [11910542]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Ivan Durak
я пробовал. Курсор быстрее всего у меня получался. Что и логично.

Ну дык помогите ТС, заодно и потестим))). Я курсоры писать не хочу уметь, если уж совсем не прижмет))).
16 янв 12, 13:39    [11910589]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
Ivan Durak
я пробовал. Курсор быстрее всего у меня получался. Что и логично.



мой разум отказывается в это верить, но это так ...

Set nocount On 
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
Create Table #СтрокиСНарастающимКоличеством
(
	Номер int,	
	НарастающийИтог int
)

Declare @Номер int
Declare @Объект binary(16)
Declare @ПредыдущийОбъект binary(16)
Declare @Количество int

Declare @НарастающийИтог int

Set @НарастающийИтог = 0


Declare Курсор cursor For 
Select Номер,Объект,Количество From #Строки
Order by Номер
OPEN Курсор
FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
WHILE @@FETCH_STATUS = 0
BEGIN
	If @ПредыдущийОбъект <> @Объект
	Begin
		set @НарастающийИтог = @Количество
	End
	Else 
	Begin
		set @НарастающийИтог = @НарастающийИтог+@Количество	
	End

	Insert into #СтрокиСНарастающимКоличеством(Номер,НарастающийИтог)Values(@Номер,@НарастающийИтог)

	FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
END

CLOSE Курсор
DEALLOCATE Курсор
Set nocount OFF 


Select Count(*) from #СтрокиСНарастающимКоличеством
16 янв 12, 14:34    [11911095]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
kDnZP
Вродь так чуть быстрее:
-- CREATE INDEX idx ON #Строки(Объект,Номер)
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
SELECT  t1.Номер, t2.НарастающееКоличество
Into #СтрокиСНарастающимКоличеством
FROM    #Строки t1
CROSS APPLY ( SELECT    SUM(t2.Количество) AS НарастающееКоличество
              FROM      #Строки t2
              WHERE     t2.Объект = t1.Объект
                        AND t1.Номер >= t2.Номер
            ) t2



хороший пример, работоспособный, но почему-то медленнее чем используя курсор

при добавлении индекса происходит незначительное, но замедление производительности :(
16 янв 12, 14:39    [11911139]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
iap
Member

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

почему отказывается?
Это же логично.
Каждая строка участвует в вычислениях минимальное число раз.
Тогда как в остальных случаях она входит также и в вычисления всех последующих сумм.
16 янв 12, 14:40    [11911144]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
ALKIR,

почему отказывается?
Это же логично.
Каждая строка участвует в вычислениях минимальное число раз.
Тогда как в остальных случаях она входит также и в вычисления всех последующих сумм.
Кроме, пожалуй, рекурсивного CTE.
Но у него другой недостаток - до самого конца хранятся все промежуточные результаты,
хотя в данном случае было бы достаточно оставлять только последние для каждой строки.
Что и реализуется в цикле по курсору.
16 янв 12, 14:45    [11911193]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
ALKIR
Ivan Durak
я пробовал. Курсор быстрее всего у меня получался. Что и логично.



мой разум отказывается в это верить, но это так ...

--ЗДЕСЬ была ошибка, но на скорость не влияет... 



исправленный вариант:
Set nocount On 
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
Create Table #СтрокиСНарастающимКоличеством
(
	Номер int,	
	НарастающийИтог int
)

Declare @Номер int
Declare @Объект binary(16)
Declare @ПредыдущийОбъект binary(16)
Declare @Количество int

Declare @НарастающийИтог int

Set @НарастающийИтог = 0


Declare Курсор cursor For 
Select Номер,Объект,Количество From #Строки
Order by Объект,Номер --Здесь была ошибка: Order by Номер
OPEN Курсор
FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
WHILE @@FETCH_STATUS = 0
BEGIN
	If @ПредыдущийОбъект <> @Объект
	Begin
		set @НарастающийИтог = @Количество
	End
	Else 
	Begin
		set @НарастающийИтог = @НарастающийИтог+@Количество	
	End

	Insert into #СтрокиСНарастающимКоличеством(Номер,НарастающийИтог)Values(@Номер,@НарастающийИтог)

	FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
END

CLOSE Курсор
DEALLOCATE Курсор
Set nocount OFF 


Select Count(*) from #СтрокиСНарастающимКоличеством
16 янв 12, 14:54    [11911274]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
iap
iap
ALKIR,

почему отказывается?
Это же логично.
Каждая строка участвует в вычислениях минимальное число раз.
Тогда как в остальных случаях она входит также и в вычисления всех последующих сумм.
Кроме, пожалуй, рекурсивного CTE.
Но у него другой недостаток - до самого конца хранятся все промежуточные результаты,
хотя в данном случае было бы достаточно оставлять только последние для каждой строки.
Что и реализуется в цикле по курсору.




Я предполагал, что операция последовательного добавления записей в базу сама по себе не быстрая , но проведенные мною тесты говорят, что это предположение было ошибочным
16 янв 12, 14:56    [11911293]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
Всем спасибо,

останавился на курсоре.
16 янв 12, 14:57    [11911302]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
курсор нормального типа сделай (только чтение, последовательный), ещё быстрей будет
16 янв 12, 15:03    [11911359]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ALKIR
исправленный вариант

Точно?
А то ведь у меня:
SELECT * FROM #СтрокиСНарастающимКоличеством
EXCEPT
SELECT * FROM #СтрокиСНарастающимКоличеством1

че-то расхождения всеж-таки дает.
Вот, например одна из сток:
Номер       НарастающийИтог
----------- ---------------
1083 41323

Номер НарастающееКоличество
----------- ----------------------
1083 11346
16 янв 12, 15:05    [11911377]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
kDnZP
ALKIR
исправленный вариант

Точно?
А то ведь у меня:
SELECT * FROM #СтрокиСНарастающимКоличеством
EXCEPT
SELECT * FROM #СтрокиСНарастающимКоличеством1

че-то расхождения всеж-таки дает.
Вот, например одна из сток:
Номер       НарастающийИтог
----------- ---------------
1083 41323

Номер НарастающееКоличество
----------- ----------------------
1083 11346




что с чем сравнивается? что в данном случае есть #СтрокиСНарастающимКоличеством и что
#СтрокиСНарастающимКоличеством1
?
16 янв 12, 15:10    [11911418]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
_djХомяГ
Guest
Вот быстрый вариант Вопрос по оптимизации
16 янв 12, 15:10    [11911422]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ALKIR
что с чем сравнивается? что в данном случае есть #СтрокиСНарастающимКоличеством и что
#СтрокиСНарастающимКоличеством1

А че, в этой теме мало вариантов?
Берете один - вставляете в одну таблицу, другой в другую, третий в еще одну... А затем между собой глядите. Одно скажу, что мой вариант с вашим первый сходится, специально проверял.
16 янв 12, 15:13    [11911452]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Кстати, а чего вы не сохраняете Объект, при создании времянки с нарастающим итогом? Т.е. как вы далее планируете опознать какие номера к каким объектам относятся?
16 янв 12, 15:25    [11911620]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
aleks2
Guest
Ну, курсор то обскакать ничо не стоит

DECLARE @КоличествоОбъектов int
DECLARE @КоличествоСтрок int

Set @КоличествоОбъектов = 5	--! здесь должно быть 5000
Set @КоличествоСтрок = 7000 --! здесь должно быть 7000000


--Последовательность (вспомогательная таблица)
if object_id('tempdb..#Последовательность10') is not NULL
begin
    drop table #Последовательность10
end
Create Table #Последовательность10(i int)
Declare @i int
set @i = 1
Set nocount On 
While (@i<=10)
Begin
	Insert into #Последовательность10(i)Values(@i)
	set @i = @i + 1
End
Set nocount Off
--Select top * from #Последовательность10
---------------------------------------------------------------
--Создадим таблицу Объектов
if object_id('tempdb..#Объекты') is not NULL
begin
    drop table #Объекты
end
Select 
	cast(newid() as binary(16)) as ID
into #Объекты
From #Последовательность10 as t1
Cross join #Последовательность10 as t2
Cross join #Последовательность10 as t3
Cross join #Последовательность10 as t4 
Where 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i <= @КоличествоОбъектов --5000
---------------------------------------------------------------
--Создадим таблицу строк
if object_id('tempdb..#Строки') is not NULL
begin
    drop table #Строки
end
Select 
	IDENTITY(int, 1,1) as Номер,
	ID as Объект,
	1 + floor(20*rand(checksum(newid()))) as Количество
Into #Строки
From #Объекты
cross Join
(
	Select 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i as N
	From #Последовательность10 as t1
	Cross join #Последовательность10 as t2
	Cross join #Последовательность10 as t3
	Cross join #Последовательность10 as t4 
	Where 1000*(t4.i-1)+100*(t3.i-1)+10*(t2.i-1)+ t1.i <=(@КоличествоСтрок/@КоличествоОбъектов)--1400
) as ttt
---------------------------------------------------------------

declare @СтрокиСНарастающимКоличеством table(obj binary(16), q int, n int identity primary key clustered)

insert @СтрокиСНарастающимКоличеством
select Объект, Количество FROM #Строки ORDER BY Объект,Номер


declare @t table(obj binary(16) primary key clustered, n int)
declare @rc int

insert @t
select obj, MIN(n) FROM @СтрокиСНарастающимКоличеством GROUP BY obj
set @rc=@@ROWCOUNT

while @rc>0 begin
 update T SET q=T.q+TT.q
 FROM 
 @СтрокиСНарастающимКоличеством T 
 inner join @t X On T.obj=X.obj AND T.n=X.n+1
 inner join @СтрокиСНарастающимКоличеством TT
 On TT.obj=X.obj AND TT.n=X.n
 set @rc=@@ROWCOUNT
 
 update @t Set n=n+1
end

select * from @СтрокиСНарастающимКоличеством
16 янв 12, 15:34    [11911738]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

Откуда:
Сообщений: 164
kDnZP
Кстати, а чего вы не сохраняете Объект, при создании времянки с нарастающим итогом? Т.е. как вы далее планируете опознать какие номера к каким объектам относятся?



Когда переменные объявлял, подразумевал, что нужно сохранять, но потом забыл...

Спасибо что подсказали :) (ткнули носом)


Set nocount On 
if object_id('tempdb..#СтрокиСНарастающимКоличеством') is not NULL
begin
    drop table #СтрокиСНарастающимКоличеством
end
Create Table #СтрокиСНарастающимКоличеством
(
	Номер int,	
	НарастающийИтог int
)

Declare @Номер int
Declare @Объект binary(16)
Declare @ПредыдущийОбъект binary(16)
Declare @Количество int

Declare @НарастающийИтог int

Set @НарастающийИтог = 0


Declare Курсор cursor READ_ONLY  For 
Select Номер,Объект,Количество From #Строки
Order by Объект,Номер

OPEN Курсор
FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
WHILE @@FETCH_STATUS = 0
BEGIN
	If @ПредыдущийОбъект <> @Объект
	Begin
		set @НарастающийИтог = @Количество
	End
	Else 
	Begin
		set @НарастающийИтог = @НарастающийИтог+@Количество	
	End

	set @ПредыдущийОбъект = @Объект --Забыл сохранить

	Insert into #СтрокиСНарастающимКоличеством(Номер,НарастающийИтог)Values(@Номер,@НарастающийИтог)

	FETCH NEXT FROM Курсор INTO @Номер,@Объект,@Количество
END

CLOSE Курсор
DEALLOCATE Курсор
Set nocount OFF 
16 янв 12, 16:08    [11912133]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
ALKIR
Member

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

очень интересный вариант... не понимаю пока как работает, но работает очень быстро....
16 янв 12, 16:10    [11912157]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Итог: по поводу циклов и курсоров я был не прав. Для данной задачи они показывают куда лучшие результаты нежели единичный запрос с агрегированиями.
16 янв 12, 16:34    [11912428]     Ответить | Цитировать Сообщить модератору
 Re: нарастающее значение(количество) по строкам  [new]
nub_2012
Guest
проверь по скорости скриптик (будет пошустрее курсоров):

if object_id('tempdb..#stroki') is not NULL     drop table #stroki
create table #stroki(number int,obj binary(16),Kolvo int)

insert into #stroki values(1,0x012,5)
insert into #stroki values(2,0x012,5)
insert into #stroki values(3,0x012,5)
insert into #stroki values(1,0x012,5)
insert into #stroki values(1,0x013,5)
insert into #stroki values(2,0x013,5)
insert into #stroki values(3,0x032,5)
insert into #stroki values(1,0x032,5)

if object_id('tempdb..#strokiAdd') is not NULL     drop table #strokiAdd

Select identity(int,1,1) as idx, 0 as tag,
       number,obj,Kolvo, 0 as KolvoAdd into #strokiAdd From #stroki
Order by obj,number

create unique clustered index idx_idx on #strokiAdd(idx)

  update f set f.tag = 1
    from #strokiAdd f
    join #strokiAdd f2 on f2.idx + 1 = f.idx
     and f2.obj = f.obj

declare @Kolvo int
  update f set
         @Kolvo = isnull(@Kolvo, 0) * tag + Kolvo,
         KolvoAdd = @Kolvo
    from #strokiAdd f with(index (idx_idx))

select * from #strokiAdd
16 янв 12, 16:55    [11912590]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить