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

Откуда:
Сообщений: 1117
Исходная таблица
CREATE TABLE [dbo].[Operations]
(
	[opId] [bigint] NOT NULL,
	[contractId] [bigint] NOT NULL,
	[opTime] [datetime] NOT NULL,
	[amount] [money] NOT NULL,
        CONSTRAINT [PK_operations_opId] PRIMARY KEY ([opId]),
        CONSTRAINT [UQ_operations_contractId_opTime] UNIQUE ([contractId],[opTime])
)


Запрос:
выводит контрактные операции отсортированные по контракту и времени
с выводом текущей суммы по контрактным операциям

1) запрос с использованием over partition by

SELECT opId, contractId, opTime, amount, SUM(amount) OVER 
    (PARTITION BY contractId ORDER BY opTime)
FROM [Operations].[dbo].[Operations] ;


2) Оптимизируем =)
SELECT [contractId], [opId], [opTime], [amount]
             ,(SELECT SUM(amount) FROM [Operations].[dbo].[Operations] t00 
               WHERE t00.contractId = t01.contractId AND t00.opTime<=t01.opTime) 
               as   [cumulativeAmount]
FROM [Operations].[dbo].[Operations] t01
ORDER BY contractId, opTime
17 дек 15, 20:30    [18574906]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
mini.weblab,

Это гениально! Супер! Я такого никогда не видел и сам бы до такого не догадался. А какой наверное красивый план получился!
+
Такой реакции вы ожидали?
18 дек 15, 02:44    [18575998]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Исходная таблица
Guest
mini.weblab,

результат только малёха разненький будет
AND t00.opTime<=t01.opTime
18 дек 15, 08:01    [18576152]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Добрый Э - Эх
Guest
Исходная таблица,

почему так думаешь?
18 дек 15, 08:14    [18576164]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Исходная таблица
Guest
Добрый Э - Эх,

с утра дальше партишн бай не прочиталось... устал, занервничал, написал изобличающий реплай. пойду еды поем.
18 дек 15, 08:23    [18576181]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1683
mini.weblab
2) Оптимизируем =)
SELECT [contractId], [opId], [opTime], [amount]
             ,(SELECT SUM(amount) FROM [Operations].[dbo].[Operations] t00 
               WHERE t00.contractId = t01.contractId AND t00.opTime<=t01.opTime) 
               as   [cumulativeAmount]
FROM [Operations].[dbo].[Operations] t01
ORDER BY contractId, opTime


пятница - что тут сказать
18 дек 15, 10:28    [18576556]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
План
Guest
mini.weblab

2) Оптимизируем =)
SELECT [contractId], [opId], [opTime], [amount]
             ,(SELECT SUM(amount) FROM [Operations].[dbo].[Operations] t00 
               WHERE t00.contractId = t01.contractId AND t00.opTime<=t01.opTime) 
               as   [cumulativeAmount]
FROM [Operations].[dbo].[Operations] t01
ORDER BY contractId, opTime


План то покажешь? И с каких это пор 2 FULLSCAN'a стали лучше одного.
18 дек 15, 10:37    [18576617]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
Женская логика!
18 дек 15, 10:43    [18576656]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

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

да! :D
18 дек 15, 10:49    [18576687]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

Откуда:
Сообщений: 1117
у меня запрос с partition выполняется около 2 минут в тестовой таблице на 100 записей
18 дек 15, 10:53    [18576710]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
mini.weblab
у меня запрос с partition выполняется около 2 минут в тестовой таблице на 100 записей

А должен быстрее ? Или медленнее ?
18 дек 15, 10:53    [18576716]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

Откуда:
Сообщений: 1117
План,

тут дело не в фулскане, а в том, что же исполняется внутри sum over partition
18 дек 15, 10:56    [18576744]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
mini.weblab,
милая девушка
или вы покажите оба плана со статистикой, или дадатите гнератор тестовых значений для вашей таблицы ,что б все поигрались и начали отвечать по теме..... или будет продолжаться разговор не о чем :)
Ну и полную версию сервера привести не мешалоб
18 дек 15, 10:59    [18576763]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
mini.weblab
тут дело не в фулскане, а в том, что же исполняется внутри sum over partition

Вы думали, что sum over partition безобидная оконная функция ?
Вы страшно ошибались !
Только у нас самые самые шокирующие подробности о том, что же там исполняется внутри !
18 дек 15, 11:00    [18576778]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

Откуда:
Сообщений: 1117
Glory, Maxx

1) а что не так ?
2) как бы вы написали подобный запрос ?

могу выложить тестовую таблицу
генератор значений тоже есть :)
18 дек 15, 11:11    [18576870]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
mini.weblab,

Так выкладывайте) и сразу все узнаете
18 дек 15, 11:16    [18576930]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
mini.weblab
1) а что не так ?
2) как бы вы написали подобный запрос ?

Для хвастания своим "достиженими" в оптимизации используйте другие ресурсы.

mini.weblab
могу выложить тестовую таблицу
генератор значений тоже есть :)

Прелестно, что вы об этом теперь упомянули.
Пожалуй не надо. Вы же сюда пришли не для разбора ситуации и доказательств преимущества своих методов, прада ?
18 дек 15, 11:16    [18576935]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
invm
Member

Откуда: Москва
Сообщений: 9632
mini.weblab,

Вам для анализа и размышлений
+
use tempdb;
go

create table dbo.t (id int identity primary key, g int, v float);

insert into dbo.t
select top (300000)
 rand(checksum(newid())) * 1000, rand(checksum(newid()))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create index IX_t__g__id on dbo.t (g, id) include (v);
go

declare @v float;

set statistics xml, time, io on;

select
 @v = sum(v) over (partition by g order by id)
from
 dbo.t
option
 (maxdop 1);

select
 @v = (select sum(v) from dbo.t where g = t1.g and id <= t1.id)
from
 dbo.t t1
option
 (maxdop 1);

select
 @v = sum(t2.v)
from
 dbo.t t1 join
 dbo.t t2 on t2.g = t1.g and t2.id <= t1.id
group by
 t1.g
option
 (maxdop 1);

set statistics xml, time, io off;
go

drop table dbo.t;
go

Table 'Worktable'. Scan count 301000, logical reads 1801405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't'. Scan count 1, logical reads 827, physical reads 0, read-ahead reads 28, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3136 ms, elapsed time = 3620 ms.


Table 't'. Scan count 300001, logical reads 1082463, physical reads 0, read-ahead reads 27, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 11466 ms, elapsed time = 11530 ms.


Table 'Worktable'. Scan count 1000, logical reads 1505999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't'. Scan count 2, logical reads 1654, 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 = 29921 ms, elapsed time = 30154 ms.
18 дек 15, 11:35    [18577094]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

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

1)
объясните на какие показатели смотреть и что они означают

Scan count
logical reads
physical reads
read-ahead reads
lob logical reads
lob physical reads
lob read-ahead reads

2)
на что расходуется разница между CPU time и elapsed time
CPU time = 3136 ms
elapsed time = 3620 ms.
18 дек 15, 12:55    [18577676]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
mini.weblab
объясните на какие показатели смотреть и что они означают

mini.weblab
на что расходуется разница между CPU time и elapsed time

Разве не вы должны нам это объяснить ?
18 дек 15, 13:01    [18577720]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

Откуда:
Сообщений: 1117
планы вот

partition
Картинка с другого сайта.


alternative
Картинка с другого сайта.
18 дек 15, 13:19    [18577864]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
invm
Member

Откуда: Москва
Сообщений: 9632
mini.weblab,

Если не знаете что это, то не понятно как вы оцениваете оптимальность запроса.
А ответы на ваши вопросы есть в документации. Достаточно изучить статьи по инструкциям set statistics *
18 дек 15, 13:19    [18577866]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
mini.weblab
Member

Откуда:
Сообщений: 1117
меня смущает, что запрос с partition by исполнялся около 2х минут на таблице в 100 записей
18 дек 15, 13:21    [18577881]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
mini.weblab,

а второй вариант сколько выполнялся? кэш чистили? несколько раз прогоняли?
18 дек 15, 13:31    [18577975]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировала запрос, использующий OVER PARTITION BY  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
iap
Женская логика!

+1
пятницо удался
18 дек 15, 14:44    [18578546]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить