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

Откуда: Москва
Сообщений: 29
Добрый вечер,

Никак не получается придумать запрос, который сделал бы следующее. Пусть есть некоторая таблица:

ndateinssettlchange
12013-04-18RTS-6.13129160-0.00301496111804411
22013-04-17RTS-6.13NULL-0.0189622624829902
32013-04-16RTS-6.13NULL-0.00799642160735559
42013-04-15RTS-6.13NULL-0.0293931950592121
52013-04-12RTS-6.13NULL-0.0129034048359085
62013-04-11RTS-6.13NULL-0.0150830265665363
72013-04-10RTS-6.13NULL0.00862166443581636
82013-04-09RTS-6.13NULL0.0141253429592556


, необходимо заполнить NULL по следующему принципу: для n = 2, "settl n = 1" / exp("change n = 1"). Соответственно, когда заполним settl для n = 2, та же операция повторяется для n = 3,..,8.

Пробовал запрос:
	update @table
	set settl = (select t1.settl/exp(t1.change) from @table t1 where t0.n - t1.n = 1)
	from @table t0
	where t0.settl is null


В результате заполнена только n = 2:

ndateinssettlchange
12013-04-18RTS-6.13129160-0.00301496111804411
22013-04-17RTS-6.13129550-0.0189622624829902
32013-04-16RTS-6.13NULL-0.00799642160735559
42013-04-15RTS-6.13NULL-0.0293931950592121
52013-04-12RTS-6.13NULL-0.0129034048359085
62013-04-11RTS-6.13NULL-0.0150830265665363
72013-04-10RTS-6.13NULL0.00862166443581636
82013-04-09RTS-6.13NULL0.0141253429592556


P.S. как такие запросы называются? :))

Заранее спасибо!
24 апр 13, 23:41    [14227506]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
AnaceH
Member

Откуда:
Сообщений: 109
d.guryev,

ваш запрос не работает, потому что на момент вычисления select t1.settl/exp(t1.change) from @table t1 where t0.n - t1.n = 1 для t0.n = 3 t1.settl = null. Используйте рекурсивный cte для вычисления settl, если версия сервера позволяет.
25 апр 13, 00:08    [14227591]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Exproment
Member

Откуда:
Сообщений: 416
d.guryev, вы наивно полагаете, что ваш пакет будет выполнятся линейно(если можно так выразится), т.е. произойдет сначала update n=2, потом update n=3... это не так. И update выполнится именно "пакетно", за один проход.

Следовательно, в вашем случае я бы использовал рекурсивный CTE с переменной.
25 апр 13, 00:34    [14227657]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А ты разверни императивную запись в декларативную. Хватит думать процедурно не юрский период, динозавры вымерли.

-- 2012
;WITH TForm AS (
	SELECT	T.*,Sum(T.change)OVER(ORDER BY T.N) - T.change AS SumChange
	FROM	@Table	T
)	UPDATE	T
	SET	settl = F.settl / exp(T.SumChange)
	FROM	@Table	F CROSS JOIN
		TForm	T
	WHERE	F.n = 1

-- 2005 (без оконных функций)
;WITH TForm AS (
	SELECT	*,(SELECT Sum(change) FROM @Table S WHERE S.n < T.n) AS SumChange 
	FROM	@Table	T
)	UPDATE	T
	SET	settl = F.settl / exp(T.SumChange)
	FROM	@Table	F CROSS JOIN
		TForm	T
	WHERE	F.n = 1

-- 2000 (без WITH)
UPDATE	T
SET	settl = F.settl / exp(T.SumChange)
FROM	@Table	F CROSS JOIN (
		SELECT	*,(SELECT Sum(change) FROM @Table S WHERE S.n < T.n) AS SumChange 
		FROM	@Table	T
)	T
WHERE	F.n = 1
Мелочи сами исправите.

Эта задача не имеет практического применения - практически: неправильная постановка.
И скорее первое значение заносится из переменной, тогда можно ещё упростить это недоразумение.
25 апр 13, 04:05    [14227829]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Это не говоря, что итеративная хрень (ссори за тавтологию) будет на порядки менее точна в вычислениях.
25 апр 13, 04:07    [14227830]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Мелочи сами исправите.
WHERE	F.n = 1 AND T.n != 1
25 апр 13, 15:45    [14230823]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
d.guryev
Member

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

Спасибо! Правда такое решение работает весьма долго...Как вы уже отметили, возможно, есть решение более быстрое.
30 май 13, 14:54    [14370583]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
d.guryev
Mnior,

Спасибо! Правда такое решение работает весьма долго...Как вы уже отметили, возможно, есть решение более быстрое.

Есть, конечно. Называется "цикл". У вас тот случай, когда "декларативная запись" идёт лесом из-за плохого быстродействия.
30 май 13, 15:21    [14370847]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
d.guryev
Правда такое решение работает весьма долго...
Какое? Их там три.
2012 стремится к пределу. Остальные конечно проседают.

Но мне как-то по барабану где вы там делаете неправильно.
Вы гуманитарий, вам плевать на всех, то что вы не понимаете - вы просто игнорируете. И игнорируете самое главное.
Вы как бульдозер прёте только прямо, прямо в пропасть.
Вы не привели ни версию сервера, ни итоговый скрипт, ни планы. У вас всё волшебно - всё решается "как-то так".

Я вам чётко написал:
Mnior
Эта задача не имеет практического применения - практически: неправильная постановка.
Хотите беседовать, уважайте собеседника. Отвечайте на вопросы, учитывайте его высказывания, возражайте если не согласны, задавайте вопросы если не поняли.

d.guryev
Есть, конечно. Называется "цикл".
А возьмите 2012-й и покажите разницу. Явные сравнения показательны и можно сослаться, такие "задачи" будут ещё заглядывать на форум.

В целом уже писал, нет такой задачи в скуле, там это не нужно.
А вот клиент может отобразить данные (проциклив внутрях, чуть ли не по мере поступления данных).
30 май 13, 22:48    [14372889]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Гость333
Есть, конечно. Называется "цикл".
А возьмите 2012-й и покажите разницу. Явные сравнения показательны и можно сослаться, такие "задачи" будут ещё заглядывать на форум.

Подготовка данных:
use tempdb;
go
create table dbo.test(id int primary key clustered, settl float, settl2 float, change float);
go
with num(n) as
(
  select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all
  select 5 union all select 6 union all select 7 union all select 8 union all select 9
),
numbers(n, n1) as
(
  select n1.n + 10 * (n2.n + 10 * (n3.n + 10 * (n4.n + 10 * (n5.n + 10 * n6.n)))) + 1, n1.n
  from num n1 cross join num n2 cross join num n3 cross join num n4 cross join num n5 cross join num n6
)
insert dbo.test(id, change)
select n.n,
       case n.n1
          -- сумма этих чисел равна нулю, т.е. значения settl должны повторяться каждые 10 строк
          when 0 then -0.00301496111804411
          when 1 then -0.0189622624829902
          when 2 then -0.00799642160735559
          when 3 then -0.0293931950592121
          when 4 then -0.0129034048359085
          when 5 then -0.0150830265665363
          when 6 then 0.00862166443581636
          when 7 then 0.0141253429592556
          when 8 then 0.0315486757487454
          when 9 then 0.03305758852622944
       end 
from numbers n;

update dbo.test set settl = 129160, settl2 = 129160 where id = 1;


Вариант "2012":
set statistics time on;
set statistics io on;

WITH TForm AS (
	SELECT	T.*,Sum(T.change)OVER(ORDER BY T.id) - T.change AS SumChange
	FROM	dbo.test T
)	UPDATE	T
	SET	settl = F.settl / exp(T.SumChange)
	FROM	dbo.test	F CROSS JOIN
		TForm	T
	WHERE	F.id = 1;

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.
Table 'test'. Scan count 1, logical reads 3004609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1000002, logical reads 11350271, 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 = 23931 ms,  elapsed time = 24441 ms.

(1000000 row(s) affected)

Выполнилось за 24 секунды, весьма неплохо, я приятно удивлён.

Вариант "цикл":
set nocount on;
set statistics time off;
set statistics io off;

declare @now datetime = getdate(), @id int, @settl2 float, @change float, @change_new float;

select @settl2 = settl2, @change = change
from dbo.test
where id = 1;

begin transaction;

set @id = 2;

while @id <= 1000000
begin
   update dbo.test
   set @change_new = change, @settl2 = settl2 = @settl2 / exp(@change)
   where id = @id;

   set @change = @change_new;

   set @id = @id + 1;
end;

commit transaction;

print 'Execution time = ' + cast(datediff(millisecond, @now, getdate()) as varchar(30)) + ' ms.';

Execution time = 11026 ms.

11 секунд, в два раза быстрее варианта "2012". Конечно, если поменять хоть одну из этих настроек:
set nocount on;
set statistics time off;
set statistics io off;
, то "цикл" умрёт нафиг, а "2012" будет работать при любых значениях настроек. Также "цикл" умрёт, если попытаться его оттрассировать с детализацией до стейтментов. В общем, довольно нежная конструкция.

Тестовый сервер: Intel(R) Core(TM)2 Duo CPU @3.00 GHz, 4,00Gb RAM
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
	Dec 28 2012 20:23:12 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
31 май 13, 01:27    [14373199]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
d.guryev
Mnior,

Спасибо! Правда такое решение работает весьма долго...Как вы уже отметили, возможно, есть решение более быстрое.

Ицик бен ган писал про использование курсора и итеративного подхода для такого класса задач (это все для <=2008R2). Попробуйте, сравните, вроде га больших множествах итеративный подход рвет подзапросы - стоимость в первом случае возрастает линейно, во втором квадратично
31 май 13, 10:25    [14374021]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

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

О, Спасибо!

Гость333
Table 'test'     . Scan count       1, logical reads  3004609, physical reads 0, read-ahead reads 0
Table 'Worktable'. Scan count 1000002, logical reads 11350271, physical reads 0, read-ahead reads 0
Пичаль, жаль что всякие промежуточные таблы возникли. Плана нет, а то мне очень хотелось посмотреть на 2012-й.

Гость333
то "цикл" умрёт нафиг, а "2012" будет работать при любых значениях настроек. Также "цикл" умрёт, если попытаться его оттрассировать с детализацией до стейтментов. В общем, довольно нежная конструкция.
Нетерпится ещё проверить при нативной компиляции, толь вот ждать долго новую версию скуля.

И неплохо бы проверить fast_forvard курсор for update. (Тем более он не требует чтобы дырок в нумерации не было)

автор
while @id <= 1000000
Не, это хак, надо подсчитывать количество строк динамически.

автор
-- сумма этих чисел равна нулю, т.е. значения settl должны повторяться каждые 10 строк
   set @change_new = change, @settl2 = settl2 = @settl2 / exp(@change)
Не вняли моему совету:
Mnior
Это не говоря, что итеративная хрень (ссори за тавтологию) будет на порядки менее точна в вычислениях.
Вы в конце то запустили проверку?:
SELECT *,Convert(VarChar,settl,2) , Convert(VarChar,settl2,2) FROM dbo.test WHERE settl != settl2

Меняем циферку и уже не сходится ещё больше:
          when 9 then 0.03305758852632944
Надо сумму подсчитывать в отдельной переменной.
Просто брать предыдущее значение - не решение для текущей задачи.

Если уж на то пошло, чтоб не сравнивать совершенно разные алгоритмы, то возьмите ещё попробуйте Lag() - предыдущая строка. Хотя я думаю будет примерно тоже самое.
31 май 13, 10:31    [14374051]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Плана нет, а то мне очень хотелось посмотреть на 2012-й.

План смогу нарисовать вечером из дома, на работе нет 2012. Ну или кто-то до этого возьмёт и выложит план по предоставленным скриптам. Насколько я помню, там был Table Spool для выражения TForm.

Mnior
И неплохо бы проверить fast_forvard курсор for update. (Тем более он не требует чтобы дырок в нумерации не было)

Проверю, тоже об этом подумал, просто никогда конструкцию "update where current of" не использовал, а разбираться с ней в полвторого ночи уже не хотелось :-)
Ну и согласен, если есть дырки в нумерации, вариант "цикл" рушится.

Mnior
автор
while @id <= 1000000
Не, это хак, надо подсчитывать количество строк динамически.

Согласен, хотя на времени выполнения это никак не скажется.

Mnior
Не вняли моему совету:
Mnior
Это не говоря, что итеративная хрень (ссори за тавтологию) будет на порядки менее точна в вычислениях.
Вы в конце то запустили проверку?:
SELECT *,Convert(VarChar,settl,2) , Convert(VarChar,settl2,2) FROM dbo.test WHERE settl != settl2

Я смотрел относительное расхождение:
SELECT ABS((settl-settl2)/settl), * FROM dbo.test WHERE settl != settl2
Это расхождение было для 999995 записей и равнялось чему-то вроде 1E-15.

Mnior
Меняем циферку и уже не сходится ещё больше:
          when 9 then 0.03305758852632944

И такое делал. Относительное расхождение могло увеличиться на несколько порядков и составить, например, 1E-12.

Просто в области неточных вычислений я чайник со свистком, результаты какие-то вижу, а интерпретировать их всё равно никак не смогу.

Mnior
Надо сумму подсчитывать в отдельной переменной.
Просто брать предыдущее значение - не решение для текущей задачи.

А кто сказал, что эта сумма будет точно подсчитана? :-) В ней точно так же может накапливаться ошибка округления. Всё равно в типе float значения вроде 0.03305758852632944 хранятся лишь приблизительно. Да и сама десятичная запись 0.03305758852632944 — тоже явно не точное значение, а округление какого-то иррационального числа.

Mnior
Если уж на то пошло, чтоб не сравнивать совершенно разные алгоритмы, то возьмите ещё попробуйте Lag() - предыдущая строка. Хотя я думаю будет примерно тоже самое.

Попробую и LAG, почему-то мне кажется, что он будет быстрее, чем SUM.
31 май 13, 12:09    [14374848]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
План запроса "2012" в виде картинки

К сообщению приложен файл. Размер - 41Kb
1 июн 13, 12:44    [14378793]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
План в виде .sqlplan

К сообщению приложен файл (plan2012.sqlplan - 45Kb) cкачать
1 июн 13, 12:46    [14378796]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Написание запроса с LAG не осилил, возможно, его тут вообще не применить.
1 июн 13, 13:06    [14378824]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
window spool
Guest
Гость333,

Привет, я тут в соседней темке занимаюсь онанизмом с Window Spool / Stream Aggregate. Вобщем предлагаю на рассмотрение ввести в запрос от Minor такую оптимизацию ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Вот как мне кажется почему:
1) По умолчанию Sum(T.change)OVER(ORDER BY T.N) подразумевается фрейм в RANGE нотации (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), а для него Window Spool всегда пишется на диск в tempdb.
2) Window Spool, для фреймов в ROWS нотации ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW у Window Spool и Stream Aggregate есть оптимизации, для каждой партиции он хранит нарастающий итог и выдает пары текущая строка/накопленное значение, кроме того при количестве строк (в партиции?) < 10000 spool будет создан в этом случае in-memory.
3) Так как id по которому делается сортировка окна уникален, то логической разницы между RANGE и ROWS нету, а значит можем применить ROWS.

Вот замеры скриптов на моей жестянке с разогретым кешем:

+ С циклом ~19 с

Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 3 мс.

Время работы SQL Server:
Время ЦП = 15 мс, затраченное время = 0 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.
Execution time = 19726 ms.


+ запрос от Minor с его же поправкой WHERE F.n = 1 AND T.n != 1 / 35-37 с


Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.
Таблица "test". Число просмотров 1, логических чтений 3004606, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 1000002, логических чтений 11350266, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 35709 мс, затраченное время = 37360 мс.


+ запрос от Minor с добавлением ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW / 14 с

WITH TForm AS (
	SELECT
		T.*,
		Sum(T.change)OVER(ORDER BY T.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - T.change AS SumChange
	FROM
		dbo.test T
)	UPDATE
		T
	SET
		settl = F.settl / exp(T.SumChange)
	FROM	
		dbo.test F
			CROSS JOIN
		TForm	T
	WHERE
		F.id = 1 AND T.id != 1;


Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 0 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.
Таблица "test". Число просмотров 1, логических чтений 3004606, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 1, логических чтений 2888741, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 14118 мс, затраченное время = 14119 мс.



Про Table/Window spool где-то читал - мол не смотрите на их стоимость в планах, они, как правило дорогие из-за того, что часто используется tempdb. Судя по всему в последнем варианте или Window Spool стал in-memory или все же на диске, но сильно меньшего размера. Оставшиеся логические чтения Worktable, возможно относятся к Eager Table Spool...

Интересно послушать мнения про это все, я новичек, только учусь.
1 июн 13, 16:43    [14379134]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
window spool
Guest
Похоже, что так оно и есть. Вот что происходит при использовании OVER(ORDER BY T.N) с RANGE фреймом по умолчанию.

К сообщению приложен файл. Размер - 47Kb
1 июн 13, 17:47    [14379251]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

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

Меня берёт печаль, Я никак не пойму смысл необходимости какой либо "оптимизации" в таком довольно чётком декларативном описании задачи.
Там что индекс не сходится с оконкой что-ли? И он, идиёт, не сортирует а пыжится?
Как я понял, вроде как всё нормально, но внутренняя организация процессов разная.

window spool
для фреймов в ROWS нотации ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW у Window Spool и Stream Aggregate есть оптимизации
Может не оптимизация, а хинт, который заставляет его создавать конкретный план?
А вот оптимизации как раз и не хватает - чтобы само догадывалось.

Делаете на последней версии скуля?

Осталось проверить 2 варианта: курсор и следующую версию скуля с нативной компиляцией. Хотя мне кажется агрегатку (хоть и такую корявую) не переплюнут.

Гость333
Написание запроса с LAG не осилил, возможно, его тут вообще не применить.
Это я ступил - нет конечно, применить невозможно.
Гость333
Попробую и LAG, почему-то мне кажется, что он будет быстрее, чем SUM.
Даже если был "грязный" Lag (видит "новое" значение), всё равно монописуально.
SET @SumPrev = @SumCurr
UPDATE T SET @SumCurr += Change, Settl = @SettlFirst / Exp(@SumPrev)

Гость333
А кто сказал, что эта сумма будет точно подсчитана? :-) В ней точно так же может накапливаться ошибка округления. Всё равно в типе float значения вроде 0.03305758852632944 хранятся лишь приблизительно.
Не, тип Float конечно говно, и ещё хуже когда им пользуются.
Но не стоит отбрасывать основную логику. Которая идёт от точных операций, т.е. от точного типа данных - Decimal.
Что лучше?! Точно полученный лимон операций (сумм) и одна, при котором тупо не хватит знаков, но все эти знаки будут правильными. Или лимон раз отбрасывать знаки после запятой которые могут накопится до чего угодной неточности.
Добавите неточность Float, логика не поменяется, тем более сумма будет довольно точной (при схожих показателях), в рамках погрешности.
Но я согласен, сильно расходящаяся сумма сведёт на нет любые вычисления.

Гость333
Да и сама десятичная запись 0.03305758852632944 — тоже явно не точное значение, а округление какого-то иррационального числа.
В рамках задачи дробные числа дают точное решение, с не меньшей точностью. А чистота данных тут не причём. Не надо чтобы вычисления добавляли погрешность. Особенно когда это стоит не больше.
Не поддавайтесь эффекту "выбитого окна". Исправится тип данных, а алгоритм останется костыльным и уже 100% неподобающим.
3 июн 13, 01:49    [14381733]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
window spool
Guest
Mnior,

тесты делал на 2012 без sp1.

оптимизация имелась ввиду по времени выполнения. с индексом там все ок. дело в реализации physical operator's window spool.

употребляя слово оптимизация имел ввиду, что у window spool есть в заначке эффективные пути выполнения для частных случаев.ему надо чутку помочь, уточнив тип фрейма.

при предложенной добавке, запрос по времени выполнения рвет и предложенный курсор, см. замеры вверху.
3 июн 13, 05:32    [14381756]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
window spool
Guest
minor, "... Может не оптимизация, а хинт,
который заставляет его создавать
конкретный план?..."

не, rows/range это не хинты, они в общем случае не только на физическом уровне отличаются, но и на логическом, когда поля сортировки не уникальны, например.
3 июн 13, 05:38    [14381758]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
window spool
не, rows/range это не хинты, они в общем случае не только на физическом уровне отличаются, но и на логическом, когда поля сортировки не уникальны, например.
Ну так вот я о том же.
Разная логика - подразумевает разную физику. Т.е. у ROWS своя физика у RANGE своя. И нет тут какой-то оптимизации у ROWS, разве нет? Не реализовывается же ROWS через RANGE, а потом типа её можно оптимизировать?

И вот когда логика монописуальна, RANGE оно или ROWS - у скуля нет оптимизационных механизмов чтобы выбрать правильный инструмент.

Я чисто об этом. Придрался к слову, не более. Не стоило оно того.

Так вот. Разница между RANGE и ROWS проявляется чисто не в уникальности сортировки. Но скулю сказали SQL по белому: N - уникальный ключ. Парится про RANGE нет смысла. А он тупит. По мне это баг.

Mnior
Т.е. у ROWS своя физика у RANGE своя. И нет тут какой-то оптимизации у ROWS, разве нет? Не реализовывается же ROWS через RANGE, а потом типа её можно оптимизировать?
Ну, можно сказать что есть "оптимизация", но правильнее сказать что механизм ROWS не так ресурсоёмок чем RANGE.

И ещё, по мне в скуле нет ещё одной оптимизации - это оптимизация при отсутствии PARTITION BY.
Не нужно ничего спулить при ROWS вообще.
Возможно вы говорите что мол не надо смотреть на циферики в плане. Мол - план так показывает что бы была видна логика запроса.
Но как раз проблема в том что план обязан показывать физику процессов, логика относится к самому запросу.
Если имеется общий для всех счётчик, не стоит это показывать как спул. Там этих внутренних переменных куча - внутренние ссылки на объекты, внутренние счётчики и т.п.
Его бы стоило показать как какой-то "скалярный каунтер". Лень что было вводить лишний showplan operator?
А так он показывает чётко - сохраняю его отдельно и достаю от туда. Бред же.
По нормальному должно быть ещё быстрее.
3 июн 13, 13:08    [14383482]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
И ещё, по мне в скуле нет ещё одной оптимизации - это оптимизация при отсутствии PARTITION BY.
Немного не точно. При PARTITION BY оно должно так-же работать, если сортировка их вместе идёт по уникальному ключу.
Просто разница в том что при отсутствии не надо проверять каждую строку, что произошла смена сегмента.
Т.е. условно тут две оптимизации:
1. Внутренний скаляр (спулинг не нужен)
2. Отключения механизма "сброса" скаляра при отсутствии PARTITION BY.

Т.е. они конечно молодцы - реализовали хоть. Но пилить надо.
Предположительно они думают - мол окошки для отчётов, сложных отчётов со сложными условиями.
И мол задачи типа "нарастающий итог" сами по себе бессмысленны как таковые. Ну есть логика, можно просто по другому процесс организовать. Но мене кажется что тут как раз лучше пусть будет разнообразие. Лучше переосмыслить задачи и способы их решения. XML то они впендёривают.
3 июн 13, 13:30    [14383611]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
window spool
Guest
Mnior,

согласен. добавлю window spool рассматривает три случая и для них реализует разные пути вычисления:

1) когда нижняя граница для rows - unbounded preceding, работает как описано тут, для каждой партиции ведется свой грубо говоря накопительный счетчик
2) функция кумулятивная (count, sum, avg, ...)и границы фрейма заданы как ROWS BETWEEN N XXXXXX AND M YYYYY
если длина фрейма > 4 то ведется два накопительных счетчика для нижней и верхней границы, потом в зависимости от функции вычисляется например для sum как разность этих величин.
если длина фрейма <= 4, то фрейм раскрывается полностью, для каждой строки в spool записывается ее фрейм и в колонку WindowCount записывается номер фрейма, потом группировка по WindowCount и вычисление требуемой функции
3) если функция не кумулятивная, то не зависимо от количество строк во фрейме, я так понял каждая строка раскроется в целый фрейм и это огромное число строк будет аггрегировать как в пункте два

---
когда выбирает spool на диск/ in-memory.

на диск пишет в случае,

1) когда длина фрейма не вычисляется до тех пор, пока не потрогаешь данные, например range between unbounded preceding and current row
2) между current row, top и bottom границами фрейма есть больше 10 000 строк. как я понял даже если current row в определении фрейма явно не участвует
3) если в lead/lag задано смещение в виде выражения
4) ...

а так старается делать in memory.

не умеет определять когда в RANGE колонка сортировки уникальна:

OVER(ORDER BY K RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

и потому не заменяет на ROWS в таких случаях.

и на том спасибо.
3 июн 13, 14:00    [14383813]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на предыдущее значение запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
window spool,
Жаль что вы не хотите регистрироваться. Не пробегаете же вы мимо мира sql?
4 июн 13, 00:56    [14386778]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить