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

Откуда: Kiev
Сообщений: 6802
invm
TaPaK
а миллион ридов это даже лучше
Иногда да.

когда "нормально" в 500 раз меньше? оправдывать затраты на электроэнергию что ли:)
2 мар 17, 15:35    [20257842]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Massa52
Member

Откуда:
Сообщений: 387
Выводы от Ицика:
Returning Previous or Next Values
Suppose that you need to query the Orders table in the TSQLFundamentals2008 database and return, for each order, information about the current order, and also the previous order ID. The concept previous implies logical ordering, but because you know that the rows in a table have no order, you need to come up with a logical equivalent to the concept of "previous" that can be phrased with a T-SQL expression. One example of such a logical equivalent is "the maximum value that is smaller than the current." This phrase can be expressed in T-SQL with a correlated subquery like so:

SELECT orderid, orderdate, empid, custid,
  (SELECT MIN(O2.orderid)
   FROM Sales.Orders AS O2
   WHERE O2.orderid > O1.orderid) AS nextorderid
FROM Sales.Orders AS O1;
------
set dateformat dmy;

Declare @t table (a datetime, b int)

Insert into @t values
('01.01.2017 01:20:00',	1),
('01.01.2017 02:15:00',	2),
('01.01.2017 03:10:00',	1),
('01.01.2017 03:50:00',	2),
('01.01.2017 04:15:00',	1),
('01.01.2017 04:40:00',	2)

select b, a, (SELECT MIN(O2.a) FROM @t AS O2 WHERE O2.a > O1.a) AS nexta
 from @t AS O1
3 мар 17, 03:16    [20259164]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

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

Ваш запрос выдает не те данные, которые нужны.
3 мар 17, 11:35    [20259825]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
komrad
Massa52,

Ваш запрос выдает не те данные, которые нужны.

зануда :) + WHERE b = 1
3 мар 17, 11:37    [20259829]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5735
TaPaK
komrad
Massa52,

Ваш запрос выдает не те данные, которые нужны.

зануда :) + WHERE b = 1

ну если уж занудствовать, тогда добавим предложенное решение от Massa52 в скрипт под №4 и смотрим результаты ;)

+ продолжим тест

declare @t table (a datetime, b int)

insert into @t values
('20170101 01:20:00',	1),
('20170101 02:15:00',	2),
('20170101 03:10:00',	1),
('20170101 03:50:00',	2),
('20170101 04:15:00',	1),
('20170101 04:40:00',	2);

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t (a datetime, b int);

insert into #t
select
 dateadd(day, n.n, t.a), t.b
from
 @t t cross join
 (
  select top (100000)
   row_number() over (order by (select 1)) as n
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
 ) n;

create index IX_#t__1 on #t (a, b);
create index IX_#t__2 on #t (b, a);
go

declare @a1 datetime, @a2 datetime;

set statistics time, io on;

/*1*/
print 'Query #1'
;with t as (
    Select a, b, lead(a) over (order by a asc, b asc) aa
    from #t)
Select @a1 = a, @a2 = aa from t
Where b=1
option
 (maxdop 1);

 /*2*/
print 'Query #2'
;with new as 
	( select 
		ROW_NUMBER() over (order by a asc,b asc) [n]
		,a
		,b
	from #t 
	)
select 
	@a1 = t1.a
	,@a2 = t2.a
from new t1
join new t2 on t2.n=t1.n+1
and t1.b=1
option
 (maxdop 1);

 /*3*/
print 'Query #3'
select
 @a1 = t1.a, @a2 = t2.a
from
 #t t1 cross apply
 (select top (1) a from #t where b = 2 and a > t1.a order by a) t2
where
 t1.b = 1
option
 (maxdop 1);

 /*4*/
print 'Query #4'
 select /*b,*/ 
 @a1=a, @a2=(SELECT MIN(O2.a) FROM #t AS O2 WHERE O2.a > O1.a) --AS nexta
 from #t AS O1
where b=1
option
 (maxdop 1);

set statistics  time, io off;




(6 row(s) affected)

(600000 row(s) affected)

/*Query #1*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000014CE'. Scan count 1, logical reads 2022, physical reads 0, read-ahead reads 413, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 844 ms,  elapsed time = 836 ms.

/*Query #2*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000014CE'. Scan count 2, logical reads 4044, 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 = 422 ms,  elapsed time = 427 ms.

/*Query #3*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#t__________________________________________________________________________________________________________________0000000014CE'. Scan count 300001, logical reads 958215, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 656 ms,  elapsed time = 646 ms.

/*Query #4*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#t__________________________________________________________________________________________________________________0000000014CE'. Scan count 300001, logical reads 901942, 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 = 938 ms,  elapsed time = 937 ms.
3 мар 17, 12:03    [20259905]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
uaggster
Member

Откуда:
Сообщений: 1056
Народ, хоть убейте меня, я не понимаю, почему так!!!


Query #1

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000000B5'. Scan count 1, logical reads 1940, 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 = 1047 ms, elapsed time = 1040 ms.
Query #2

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000000B5'. Scan count 2, logical reads 3880, 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 = 516 ms, elapsed time = 524 ms.

Вроде бы второй запрос должен быть дороже и медленнее, а всё наоборот!
Почему? Window spool?

К сообщению приложен файл. Размер - 109Kb
3 мар 17, 14:12    [20260452]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
uaggster,
автор
Вроде бы второй запрос должен быть дороже и медленнее, а всё наоборот!
Почему? Window spool?

а соседнее значение находит просто так?
3 мар 17, 14:19    [20260479]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
если добавить, то это вполне обычный порядок для оконных функций, само окно надо ж где-то получить
3 мар 17, 14:32    [20260527]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
uaggster
Member

Откуда:
Сообщений: 1056
TaPaK
uaggster,
автор
Вроде бы второй запрос должен быть дороже и медленнее, а всё наоборот!
Почему? Window spool?

а соседнее значение находит просто так?

Не понял, что вы хотели сказать.
Я про то, что сканов меньше, логических чтений меньше (и того, и другого - в 2 раза), estimated number of rows в первом случае считает верно, во втором - промахивается на 5 порядков (sic!), стоимость первого оценивает на три порядка меньше...
А выполняет медленнее.

Как так то? Не понимаю!
Объясните на пальцах.
3 мар 17, 14:34    [20260534]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
uaggster
Я про то, что сканов меньше, логических чтений меньше
А потребление CPU больше.
3 мар 17, 14:55    [20260625]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

Откуда:
Сообщений: 599
Я рад, что мои сомнения привели к большему пониманию работы оконных функций.

"само окно надо ж где-то получить". Да надо, но во втором случае нужно получить два окна.

У меня план немного другой. Отличие в том, что после сканирования таблицы идет сортировка.

Я думаю, что если в отсортированной таблице сложно найти соседние строки, то скорее всего дело в реализации lead.
3 мар 17, 14:59    [20260636]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Wlr-l,


автор
Я думаю, что если в отсортированной таблице сложно найти соседние строки, то скорее всего дело в реализации lead.

ну вы же понимаете что "соседние" могут быть любые записи

автор
но во втором случае нужно получить два окна.
да, но ROW_NUMBER реализован через sequence
3 мар 17, 15:07    [20260660]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Massa52
Member

Откуда:
Сообщений: 387
А такой вариант был? Кажись очень шустрый
;with t1 as 
(  
   select ROW_NUMBER() OVER (ORDER BY a) n, a from #t   where b = 1 
),
t2 as 
(  
   select ROW_NUMBER() OVER (ORDER BY a) n, a from #t   where b = 2 
) 
select @a1=t1.a, @a2=t2.a from t1 join t2 on t1.n = t2.n

option
 (maxdop 1);
3 мар 17, 15:14    [20260693]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

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

Точно так же как "соседними" являются n и n+1.

Проблема в реализации lead.
3 мар 17, 15:18    [20260709]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Wlr-l
TaPaK,

Точно так же как "соседними" являются n и n+1.

Проблема в реализации lead.

т.е. вы сравниваете join со смещением? однако. И да, все говорят одно и тоже: окна это тормоз
3 мар 17, 15:20    [20260726]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

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

про тормоз оконных функций говорит, в основном, Alex2, а, например, Итцик Бен-Ган другого мнения.
3 мар 17, 15:28    [20260756]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Wlr-l,

автор
Итцик Бен-Ган другого мнения.
ГДЕ там он нахваливает производительность ????
3 мар 17, 15:29    [20260761]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

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

+ скрипт, запрос №5
declare @t table (a datetime, b int)

insert into @t values
('20170101 01:20:00',	1),
('20170101 02:15:00',	2),
('20170101 03:10:00',	1),
('20170101 03:50:00',	2),
('20170101 04:15:00',	1),
('20170101 04:40:00',	2);

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t (a datetime, b int);

insert into #t
select
 dateadd(day, n.n, t.a), t.b
from
 @t t cross join
 (
  select top (100000)
   row_number() over (order by (select 1)) as n
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
 ) n;

create index IX_#t__1 on #t (a, b);
create index IX_#t__2 on #t (b, a);
go

declare @a1 datetime, @a2 datetime;

set statistics time, io on;

/*1*/
print '/*Query #1*/'
;with t as (
    Select a, b, lead(a) over (order by a asc, b asc) aa
    from #t)
Select @a1 = a, @a2 = aa from t
Where b=1
option
 (maxdop 1);

 /*2*/
print '/*Query #2*/'
;with new as 
	( select 
		ROW_NUMBER() over (order by a asc,b asc) [n]
		,a
		,b
	from #t 
	)
select 
	@a1 = t1.a
	,@a2 = t2.a
from new t1
join new t2 on t2.n=t1.n+1
and t1.b=1
option
 (maxdop 1);

 /*3*/
print '/*Query #3*/'
select
 @a1 = t1.a, @a2 = t2.a
from
 #t t1 cross apply
 (select top (1) a from #t where b = 2 and a > t1.a order by a) t2
where
 t1.b = 1
option
 (maxdop 1);

 /*4*/
print '/*Query #4*/'
 select /*b,*/ 
 @a1=a, @a2=(SELECT MIN(O2.a) FROM #t AS O2 WHERE O2.a > O1.a) --AS nexta
 from #t AS O1
where b=1
option
 (maxdop 1);

/*5*/
print '/*Query #5*/'
;with t1 as 
(  
   select ROW_NUMBER() OVER (ORDER BY a) n, a from #t   where b = 1 
),
t2 as 
(  
   select ROW_NUMBER() OVER (ORDER BY a) n, a from #t   where b = 2 
) 
select @a1=t1.a, @a2=t2.a from t1 join t2 on t1.n = t2.n

option
 (maxdop 1);

set statistics  time, io off;


+ результат
(6 row(s) affected)

(600000 row(s) affected)
/*Query #1*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000034EC'. Scan count 1, logical reads 1940, 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 = 843 ms,  elapsed time = 925 ms.
/*Query #2*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000034EC'. Scan count 2, logical reads 3880, 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 = 360 ms,  elapsed time = 418 ms.
/*Query #3*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#t__________________________________________________________________________________________________________________0000000034EC'. Scan count 300001, logical reads 958213, 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 = 656 ms,  elapsed time = 662 ms.
/*Query #4*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#t__________________________________________________________________________________________________________________0000000034EC'. Scan count 300001, logical reads 901936, 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 = 797 ms,  elapsed time = 831 ms.
/*Query #5*/

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________0000000034EC'. Scan count 2, logical reads 1944, 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 = 234 ms,  elapsed time = 229 ms.
3 мар 17, 15:56    [20260888]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

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

Microsoft SQL Server 2012: Высокопроизводительный код T-SQL. Оконные функции
Автор: Бен-Ган Ицик
Издательство: Русская Редакция, 2013

Эта книга — подробное руководство по применению оконных функций в SQL Server, а также в стандарте SQL, в том числе по использованию элементов, которые пока не реализованы в SQL Server. Здесь описаны принципы работы с окнами в SQL Server и работа различных оконных функций, также функции упорядоченных наборов. Подробно освещен вопрос оптимизации оконных функций в SQL Server 2012 для достижения максимальной производительности.
3 мар 17, 16:10    [20260941]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Wlr-l
TaPaK,

Microsoft SQL Server 2012: Высокопроизводительный код T-SQL. Оконные функции
Автор: Бен-Ган Ицик
Издательство: Русская Редакция, 2013

Эта книга — подробное руководство по применению оконных функций в SQL Server, а также в стандарте SQL, в том числе по использованию элементов, которые пока не реализованы в SQL Server. Здесь описаны принципы работы с окнами в SQL Server и работа различных оконных функций, также функции упорядоченных наборов. Подробно освещен вопрос оптимизации оконных функций в SQL Server 2012 для достижения максимальной производительности.

Учебник Біологія 7 клас Д.А. Шабанов, М.О. Кравченко (2015 рік) Содержит описание растений. Автор восхищается растениями? и те что они более производительны чем животные?
3 мар 17, 16:13    [20260957]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
o-o
Guest
Wlr-l
TaPaK,

про тормоз оконных функций говорит, в основном, Alex2, а, например, Итцик Бен-Ган другого мнения.

а про тормоз вашего решения говорит время выполнения на таблице,
содержащей более 5 строк.
но вы упорно читаете нарисованные студией цифры
и приводите общие цитаты.
непонятно, зачем отрицать очевидное?
по крайней мере в данном конкретном случае?
3 мар 17, 16:14    [20260960]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

Откуда:
Сообщений: 599
o-o,

Вы опоздали, чуть раннее я признался в этом.
3 мар 17, 16:28    [20261032]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

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

Я с удовольствием прочту Вашу книгу о производительности оконных функций.
3 мар 17, 16:34    [20261061]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Wlr-l
TaPaK,

Я с удовольствием прочту Вашу книгу о производительности оконных функций.

я читал указанную вам книгу и там нет ни слова о том что оконные функции являются величайшим достижением производительности, и единственное, что они действительно породили так это массовое использование в любой ситуации "недалёкими" разработчиками. И тов. Ицик для этого и написал целую книгу, что бы раз уж вы не способны использовать общий функционал стандарта sql, то хоть не заставляйте седеть dba
3 мар 17, 16:38    [20261074]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
o-o
Guest
Wlr-l
o-o,

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

в приведенной мной цитате нет вас,
я комментирую именно ее.
к тому я не вижу никакого "признания"
я вижу реальные цифры, приведенные другими.
от вашего имени кроме попугаев и перечисления кто и чего говорит, не вижу ничего
3 мар 17, 16:38    [20261075]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить