ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Изящно выбрать последних
25 сообщений из 28, страница 1 из 2
  1  все
Изящно выбрать последних
    #39902109
SHKoder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SHKoder
Участник
Ребят, выскочило из памяти, толи утомился.
Научите, пожалуйста выбрать изящно последних агентов по договорам.

Есть договора, у которых менялись агенты. Есть даты фиксации этих фактов.


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
agg_num    agent_id    datetime
9                11              2011-01-01
9                 12              2011-04-07
9                 16              2011-06-21
4                 12              2011-02-09
4                 14              2011-09-02
4                 19              2011-12-21
6                 14              2011-03-01
6                 29              2011-08-31

на выходе нужно получить
Код: plaintext
1.
2.
3.
4.
agg_num    agent_id    datetime
9                 16              2011-06-21
4                 19              2011-12-21
6                 29              2011-08-31

Прсотите за элементарный вопрос, но чот я утомился. спасибо за изящный запрос.

ver SQL 2012 sp4


в диктанте три-четыре варнинга, но в общем компилируется
...
Рейтинг: 0 / 0
12.12.2019, 19:32
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902149
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
SHKoder
Прсотите за элементарный вопрос, но чот я утомился. спасибо за изящный запрос.
Например, row_number() over()
...
Рейтинг: 0 / 0
12.12.2019, 21:00
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902314
SHKoder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SHKoder
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table #t (
id integer,
ag integer,
dt date
)

insert into #t

select 9,                11,             '2011-01-01' union all
select 9,                 12,              '2011-04-07' union all
select 9,                 16,              '2011-06-21' union all
select 4,                 12,              '2011-02-09' union all
select 4,                 14,              '2011-09-02' union all
select 4,                 19,              '2011-12-21' union all
select 6,                 14,              '2011-03-01' union all
select 6,                 29,              '2011-08-31'



Получилось вот так

Код: sql
1.
2.
3.
4.
select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1



Но выборка из выборки такое себе , изящество.

Еще есть варианты?
...
Рейтинг: 0 / 0
13.12.2019, 09:32
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902315
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
SHKoder
Но выборка из выборки такое себе , изящество.
Эээ, самый наиизящнейший вид!
А уж если отступов наставить, вообще красота:
Код: sql
1.
2.
3.
4.
5.
6.
select * 
from (
	select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * 
	from #t
) t
where t.ddd = 1


Ещё можно с CTE написать.
...
Рейтинг: 0 / 0
13.12.2019, 09:40
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902372
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
SHKoder
Код: sql
1.
2.
3.
4.
select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1



Но выборка из выборки такое себе , изящество.


Код: sql
1.
2.
3.
select top(1) with ties *
from #t
order by ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)



пс
но как-то тут "делали замеры" по всем вариантам выборки "первого из группы", так CTE с RN=1 (т.е. аналог того запроса, который тебе не нравится) - тогда победил :)
...
Рейтинг: 0 / 0
13.12.2019, 11:02
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902379
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
court
SHKoder
Код: sql
1.
2.
3.
4.
select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1



Но выборка из выборки такое себе , изящество.


Код: sql
1.
2.
3.
select top(1) with ties *
from #t
order by ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)



пс
но как-то тут "делали замеры" по всем вариантам выборки "первого из группы", так CTE с RN=1 (т.е. аналог того запроса, который тебе не нравится) - тогда победил :)


У top 1 with ties отставание от RN=1 было на пару процентов. Учитывая компактность записи , он рулез
...
Рейтинг: 0 / 0
13.12.2019, 11:08
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902459
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
a_voronin
У top 1 with ties отставание от RN=1 было на пару процентов. Учитывая компактность записи , он рулез
a_voronin в своем репертуаре :)

Рулез - это когда сначала думают, а потом пишут.
Для начала вам следует понять из-за чего with ties медленнее, почему это не исправить и в каких случаях это будет критично. А потом уже решать, будет ли with ties допустимо в конкретном случае.

Ну и про пару процентов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
use tempdb;
go

create table dbo.t (g int, v float, s char(200) default replicate('a', 200));

insert into dbo.t
 (g, v)
 select top (3000000)
  row_number() over (order by 1/0) / 3, rand(checksum(newid()))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

declare @v float, @s char(200);

set statistics time on;
select @v = v, @s = s from (select v, s, row_number() over (partition by g order by v) as rn from dbo.t) t where rn = 1 option (maxdop 1);
select top (1) with ties @v = v, @s = s from dbo.t order by row_number() over (partition by g order by v) option (maxdop 1);
set statistics time off;
go

drop table dbo.t;
go

Код: plaintext
1.
2.
3.
4.
5.
 SQL Server Execution Times:
   CPU time = 2046 ms,  elapsed time = 4915 ms.

 SQL Server Execution Times:
   CPU time = 6360 ms,  elapsed time = 9547 ms.
...
Рейтинг: 0 / 0
13.12.2019, 12:26
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902480
архивариус
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
архивариус
Участник
invm
Код: plaintext
1.
2.
3.
4.
5.
 SQL Server Execution Times:
   CPU time = 2046 ms,  elapsed time = 4915 ms.

 SQL Server Execution Times:
   CPU time = 6360 ms,  elapsed time = 9547 ms.

разве имеет смысл сравнивать запросы если они выводят разные результаты? (row_number и v различаются)
...
Рейтинг: 0 / 0
13.12.2019, 13:09
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902489
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
архивариус
разве имеет смысл сравнивать запросы если они выводят разные результаты? (row_number и v различаются)
И сможете показать различающиеся результаты?
...
Рейтинг: 0 / 0
13.12.2019, 13:29
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902538
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
invm,

А у меня он рулез, а вот rn = 1 сакс. Ваш скрипт без изменений

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 19 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'spt_values'. Scan count 9, logical reads 74, physical reads 1, page server reads 0, read-ahead reads 22, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 't'. Scan count 0, logical reads 3083333, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 9467 ms, elapsed time = 10266 ms.

(3000000 rows affected)
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 't'. Scan count 1, logical reads 83334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1735 ms, elapsed time = 1746 ms.
Table 't'. Scan count 1, logical reads 83334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2218 ms, elapsed time = 2217 ms.

Completion time: 2019-12-13T14:19:31.3341994+03:00
...
Рейтинг: 0 / 0
13.12.2019, 14:23
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902559
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
a_voronin

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

о боже, RTM в действии.
я его даже ради тестов не поставлю до первого CU
...
Рейтинг: 0 / 0
13.12.2019, 14:47
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902560
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
a_voronin,

Вы сравниваете время заполнения тестовой таблицы и время запроса с with ties, а не времена выполнения двух запросов.
Вот что там у вас на самом делеSQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 't'. Scan count 1, logical reads 83334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1735 ms, elapsed time = 1746 ms.
Table 't'. Scan count 1, logical reads 83334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2218 ms, elapsed time = 2217 ms.
...
Рейтинг: 0 / 0
13.12.2019, 14:49
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902566
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
a_voronin,

гляньте фактические планы, у вас при сортировке слив в tempdb присутствует?
у меня на 2016 sp2, при обоих запросах происходит слив в tempdb, при этом:
для сортировки rn = 1 входящий поток 300000
для order by row_number входящий потом 100002

соответственно производительность rn=1 сильно хуже:

 

SQL Server Execution Times:
CPU time = 4078 ms, elapsed time = 11507 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 6609 ms, elapsed time = 8278 ms.

Оператор использовал базу данных tempdb для временной записи данных во время выполнения. Уровень временной записи данных: 1, число записанных потоков: 1.; Операция сортировки записала 83334 стр. в базу данных tempdb и считала 83334 стр. из нее; предоставлено памяти: 949576 КБ, использовано памяти: 949576 КБ

при этом оценка кардинальности правильная, я не понимаю почему он начинает сливать.


если оценку поправить:
Код: sql
1.
update statistics dbo.t with rowcount = 6000000



то ситуация меняется в другую сторону:

 

спиллов нет:

SQL Server Execution Times:
CPU time = 2796 ms, elapsed time = 2800 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 5813 ms, elapsed time = 5881 ms.


add:кто объяснит с хренали при изначальной кардинальности после вставки, в планах происходит spill в сортировке?

 
=sqlplan
<ShowPlanXML xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=" http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5292.0" xmlns=" http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="3000000" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="1210.23" StatementText="select @v = v, @s = s from (select v, s, row_number() over (partition by g order by v) as rn from dbo.t) t where rn = 1 option (maxdop 1)" StatementType="SELECT" QueryHash="0x912E5212438FE888" QueryPlanHash="0x5BB21E5055364405" RetrievedFromCache="true" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="949576" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="168">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="949576" RequiredMemory="512" DesiredMemory="949576" RequestedMemory="949576" GrantWaitTime="0" GrantedMemory="949576" MaxUsedMemory="949576" MaxQueryMemory="6376904" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="290133" EstimatedPagesCached="217600" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="19310936" />
<TraceFlags IsCompileTime="true">
<TraceFlag Value="1222" Scope="Global" />
<TraceFlag Value="1224" Scope="Global" />
</TraceFlags>
<TraceFlags IsCompileTime="false">
<TraceFlag Value="1222" Scope="Global" />
<TraceFlag Value="1224" Scope="Global" />
</TraceFlags>
<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="15" WaitCount="640" />
<Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="63" WaitCount="118697" />
<Wait WaitType="IO_COMPLETION" WaitTimeMs="8263" WaitCount="10367" />
</WaitStats>
<QueryTimeStats CpuTime="4167" ElapsedTime="12303" />
<RelOp AvgRowSize="215" EstimateCPU="1.44" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1210.23">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000001" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="12203" ActualCPUms="4066" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="223" EstimateCPU="0.24" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="1208.79">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
<ColumnReference Column="Expr1003" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="12109" ActualCPUms="3972" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="223" EstimateCPU="0.06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Segment" NodeId="2" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="1208.55">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
<ColumnReference Column="Segment1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="11869" ActualCPUms="3732" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1004" />
</SegmentColumn>
<RelOp AvgRowSize="219" EstimateCPU="87.7174" EstimateIO="1055.74" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1208.49">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<Warnings>
<SpillToTempDb SpillLevel="1" SpilledThreadCount="1" />
<SortSpillDetails GrantedMemoryKb="949576" UsedMemoryKb="949576" WritesToTempDb="83334" ReadsFromTempDb="83334" />
</Warnings>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="11688" ActualCPUms="3551" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="83334" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="949576" OutputMemoryGrant="949192" UsedMemoryGrant="949576" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="219" EstimateCPU="3.30016" EstimateIO="61.7313" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" EstimatedRowsRead="3000000" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="65.0314" TableCardinality="3000000">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" ActualRowsRead="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="586" ActualCPUms="586" ActualScans="1" ActualLogicalReads="83334" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[Expr1003]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1003" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
<ParameterList>
<ColumnReference Column="@s" ParameterDataType="char(200)" ParameterRuntimeValue="'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'" />
<ColumnReference Column="@v" ParameterDataType="float" ParameterRuntimeValue="(2.2925616261502110e-001)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="3000000" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="2352.55" StatementText="select top (1) with ties @z = v, @k = s from dbo.t order by row_number() over (partition by g order by v) option (maxdop 1)" StatementType="SELECT" QueryHash="0x19EDA62F5D26178B" QueryPlanHash="0x81897C0EF5B378F4" RetrievedFromCache="true" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1913424" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="160">
<MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="1913424" RequiredMemory="640" DesiredMemory="1913424" RequestedMemory="1913424" GrantWaitTime="0" GrantedMemory="1913424" MaxUsedMemory="1730536" MaxQueryMemory="6376904" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="290133" EstimatedPagesCached="217600" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="19310216" />
<TraceFlags IsCompileTime="true">
<TraceFlag Value="1222" Scope="Global" />
<TraceFlag Value="1224" Scope="Global" />
</TraceFlags>
<TraceFlags IsCompileTime="false">
<TraceFlag Value="1222" Scope="Global" />
<TraceFlag Value="1224" Scope="Global" />
</TraceFlags>
<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="22" WaitCount="1494" />
<Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="116" WaitCount="216317" />
<Wait WaitType="IO_COMPLETION" WaitTimeMs="1463" WaitCount="3942" />
</WaitStats>
<QueryTimeStats CpuTime="6669" ElapsedTime="8096" />
<RelOp AvgRowSize="215" EstimateCPU="0.3" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="2352.55">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000001" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8009" ActualCPUms="6582" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="true">
<TieColumns>
<ColumnReference Column="Expr1003" />
</TieColumns>
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="223" EstimateCPU="87.7174" EstimateIO="1055.74" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2352.25">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
<ColumnReference Column="Expr1003" />
</OutputList>
<Warnings>
<SpillToTempDb SpillLevel="1" SpilledThreadCount="1" />
<SortSpillDetails GrantedMemoryKb="964232" UsedMemoryKb="964232" WritesToTempDb="83334" ReadsFromTempDb="83334" />
</Warnings>
<MemoryFractions Input="0.503831" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1000002" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7959" ActualCPUms="6532" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="33312" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="964232" OutputMemoryGrant="1912912" UsedMemoryGrant="964232" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1003" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="223" EstimateCPU="0.24" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="1208.79">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
<ColumnReference Column="Expr1003" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2706" ActualCPUms="2706" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="223" EstimateCPU="0.06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Segment" NodeId="3" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="1208.55">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
<ColumnReference Column="Segment1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2433" ActualCPUms="2433" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1004" />
</SegmentColumn>
<RelOp AvgRowSize="219" EstimateCPU="87.7174" EstimateIO="1055.74" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1208.49">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<MemoryFractions Input="1" Output="0.496169" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2218" ActualCPUms="2218" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1913296" OutputMemoryGrant="949192" UsedMemoryGrant="766304" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="219" EstimateCPU="3.30016" EstimateIO="61.7313" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3000000" EstimatedRowsRead="3000000" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="65.0314" TableCardinality="3000000">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3000000" ActualRowsRead="3000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="600" ActualCPUms="600" ActualScans="1" ActualLogicalReads="83334" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="v" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="s" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
</Sort>
</RelOp>
</Top>
</RelOp>
<ParameterList>
<ColumnReference Column="@k" ParameterDataType="char(200)" ParameterRuntimeValue="'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'" />
<ColumnReference Column="@z" ParameterDataType="float" ParameterRuntimeValue="(2.2925616261502110e-001)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
...
Рейтинг: 0 / 0
13.12.2019, 15:02
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902569
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
invm,

Если убрать (maxdop 1) результаты почти не отличаются . И не видно разницы с кол-ве чтений. This isn't something to amount to anything.



SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 't'. Scan count 5, logical reads 166667, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 4891 ms, elapsed time = 1570 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 't'. Scan count 5, logical reads 166667, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5203 ms, elapsed time = 1597 ms.
...
Рейтинг: 0 / 0
13.12.2019, 15:05
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902613
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
a_voronin
Если убрать (maxdop 1) результаты почти не отличаются
А CPU time сравнить не забыли? Или плюем, ибо ресурсов у всех неограниченно?
a_voronin
И не видно разницы с кол-ве чтений.
А с чего ей быть?

ЗЫ: Вы можете сколь угодно долго придумывать частные случаи, но убрать сортировку по row_number из запроса с ties не выйдет, из-за которой, собственно, этот вариант и медленнее.
...
Рейтинг: 0 / 0
13.12.2019, 15:31
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902643
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
felix_ff
для order by row_number входящий потом 100002
Это как? Видимо все-таки исходящий?
...
Рейтинг: 0 / 0
13.12.2019, 15:56
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902665
SHKoder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SHKoder
Участник
Еба....нитовая палочка!!!
Вы это тут о чем вапще?

Я вообще такого не просил... Тут уже на атомы углерода разобрали запрос...
...
Рейтинг: 0 / 0
13.12.2019, 16:05
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902690
архивариус
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
архивариус
Участник
invm
архивариус
разве имеет смысл сравнивать запросы если они выводят разные результаты? (row_number и v различаются)
И сможете показать различающиеся результаты?

ну я убрал присвоение @v и @s чтобы понять что выводится, и результаты не совпали от слова совсем (только S одинаковый естественно, количество строк одинаковое, а например в строках V не совпадают от слова совсем, я ожидал чтобы они частично отличались из за ties ) может я не понимаю просто в чем цимус, забейте
...
Рейтинг: 0 / 0
13.12.2019, 16:33
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902695
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
invm,

да, исходящий конечно
...
Рейтинг: 0 / 0
13.12.2019, 16:36
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902719
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
архивариус
может я не понимаю просто в чем цимус
Именно.
Вы почему-то решили, что если порядок строк разный, то результаты не совпадают.
...
Рейтинг: 0 / 0
13.12.2019, 16:59
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902784
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
SHKoder, очень даже просили )

SHKoder
Еще есть варианты?


Вот вам второй вариант и предложили, предупредив, что он медленнее, и на больших объемах может замедлить запрос.
Ну а дальше кто-то не согласился, и понеслась дискуссия )
...
Рейтинг: 0 / 0
13.12.2019, 17:43
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902894
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
invm,

не подскажете, почему в запросе

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select t.*
from #t as t
cross apply (
       select mx = max(t2.dt)
       from #t as t2 
       where t2.id = t.id
) as m
where t.dt = m.mx



получается один скан вместо двух? а если добавить индекс на (id, dt), то и сортировка исчезает, в отличие от вариантов с row_number()
...
Рейтинг: 0 / 0
13.12.2019, 20:11
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902906
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Shakill
а если добавить индекс на (id, dt), то и сортировка исчезает, в отличие от вариантов с row_number()
А если сделать индекс на (id, dt desc), то и с row_number() пропадёт :-)
...
Рейтинг: 0 / 0
13.12.2019, 20:28
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902908
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
alexeyvg
Shakill
а если добавить индекс на (id, dt), то и сортировка исчезает, в отличие от вариантов с row_number()
А если сделать индекс на (id, dt desc), то и с row_number() пропадёт :-)

а точно, не заметил
но все-таки главный вопрос насчёт скана. есть ведь подзапрос с агрегатной функцией к той же таблице
...
Рейтинг: 0 / 0
13.12.2019, 20:30
| Ответить | Цитировать | Написать  
Изящно выбрать последних
    #39902914
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Shakill
но все-таки главный вопрос насчёт скана. есть ведь подзапрос с агрегатной функцией к той же таблице
Ну и что, сервер же не строит план, напрямую переводя синтаксис запроса в выполняемые операторы.
И вот, как то догадался, что не нужно делать джойн, что можно обойтись одним сканом.
...
Рейтинг: 0 / 0
13.12.2019, 20:47
| Ответить | Цитировать | Написать  
25 сообщений из 28, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Изящно выбрать последних
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=76&tid=1686777]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 31ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 76ms
get tp. blocked users:
 2ms
others: 321ms
total:  483ms
созд. / загр.: 483ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]