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

Откуда: Moscow city
Сообщений: 616
Ребят, выскочило из памяти, толи утомился.
Научите, пожалуйста выбрать изящно последних агентов по договорам.

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


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

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

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

ver SQL 2012 sp4


в диктанте три-четыре варнинга, но в общем компилируется
12 дек 19, 19:32    [22039101]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
SHKoder
Прсотите за элементарный вопрос, но чот я утомился. спасибо за изящный запрос.
Например, row_number() over()
12 дек 19, 21:00    [22039149]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
SHKoder
Member

Откуда: Moscow city
Сообщений: 616
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'


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

select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1


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

Еще есть варианты?
13 дек 19, 09:32    [22039371]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
SHKoder
Но выборка из выборки такое себе, изящество.
Эээ, самый наиизящнейший вид!
А уж если отступов наставить, вообще красота:
select * 
from (
	select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * 
	from #t
) t
where t.ddd = 1

Ещё можно с CTE написать.
13 дек 19, 09:40    [22039378]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
court
Member

Откуда:
Сообщений: 2016
SHKoder
select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1


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


select top(1) with ties *
from #t
order by ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)


пс
но как-то тут "делали замеры" по всем вариантам выборки "первого из группы", так CTE с RN=1 (т.е. аналог того запроса, который тебе не нравится) - тогда победил :)
13 дек 19, 11:02    [22039460]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4730
court
SHKoder
select * from (
select  (ROW_NUMBER () OVER ( PARTITION BY id order by dt desc)) as ddd, * from #t
) t
where t.ddd = 1


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


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 было на пару процентов. Учитывая компактность записи , он рулез
13 дек 19, 11:08    [22039470]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
a_voronin
У top 1 with ties отставание от RN=1 было на пару процентов. Учитывая компактность записи , он рулез
a_voronin в своем репертуаре :)

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

Ну и про пару процентов
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
 SQL Server Execution Times:
CPU time = 2046 ms, elapsed time = 4915 ms.

SQL Server Execution Times:
CPU time = 6360 ms, elapsed time = 9547 ms.
13 дек 19, 12:26    [22039580]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
архивариус
Member

Откуда:
Сообщений: 150
invm
 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 различаются)
13 дек 19, 13:09    [22039623]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
архивариус
разве имеет смысл сравнивать запросы если они выводят разные результаты? (row_number и v различаются)
И сможете показать различающиеся результаты?
13 дек 19, 13:29    [22039641]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4730
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
13 дек 19, 14:23    [22039715]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
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
13 дек 19, 14:47    [22039758]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
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.
13 дек 19, 14:49    [22039762]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
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 КБ

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


если оценку поправить:
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>


Сообщение было отредактировано: 13 дек 19, 15:05
13 дек 19, 15:02    [22039774]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4730
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.
13 дек 19, 15:05    [22039779]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
a_voronin
Если убрать (maxdop 1) результаты почти не отличаются
А CPU time сравнить не забыли? Или плюем, ибо ресурсов у всех неограниченно?
a_voronin
И не видно разницы с кол-ве чтений.
А с чего ей быть?

ЗЫ: Вы можете сколь угодно долго придумывать частные случаи, но убрать сортировку по row_number из запроса с ties не выйдет, из-за которой, собственно, этот вариант и медленнее.
13 дек 19, 15:31    [22039845]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
felix_ff
для order by row_number входящий потом 100002
Это как? Видимо все-таки исходящий?
13 дек 19, 15:56    [22039886]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
SHKoder
Member

Откуда: Moscow city
Сообщений: 616
Еба....нитовая палочка!!!
Вы это тут о чем вапще?

Я вообще такого не просил... Тут уже на атомы углерода разобрали запрос...
13 дек 19, 16:05    [22039912]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
архивариус
Member

Откуда:
Сообщений: 150
invm
архивариус
разве имеет смысл сравнивать запросы если они выводят разные результаты? (row_number и v различаются)
И сможете показать различающиеся результаты?

ну я убрал присвоение @v и @s чтобы понять что выводится, и результаты не совпали от слова совсем (только S одинаковый естественно, количество строк одинаковое, а например в строках V не совпадают от слова совсем, я ожидал чтобы они частично отличались из за ties ) может я не понимаю просто в чем цимус, забейте
13 дек 19, 16:33    [22039947]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
felix_ff
Member

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

да, исходящий конечно
13 дек 19, 16:36    [22039953]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
архивариус
может я не понимаю просто в чем цимус
Именно.
Вы почему-то решили, что если порядок строк разный, то результаты не совпадают.
13 дек 19, 16:59    [22039984]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SHKoder, очень даже просили )

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


Вот вам второй вариант и предложили, предупредив, что он медленнее, и на больших объемах может замедлить запрос.
Ну а дальше кто-то не согласился, и понеслась дискуссия )

Сообщение было отредактировано: 13 дек 19, 17:43
13 дек 19, 17:43    [22040056]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
Shakill
Member

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

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

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()
13 дек 19, 20:11    [22040206]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Shakill
а если добавить индекс на (id, dt), то и сортировка исчезает, в отличие от вариантов с row_number()
А если сделать индекс на (id, dt desc), то и с row_number() пропадёт :-)
13 дек 19, 20:28    [22040223]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
alexeyvg
Shakill
а если добавить индекс на (id, dt), то и сортировка исчезает, в отличие от вариантов с row_number()
А если сделать индекс на (id, dt desc), то и с row_number() пропадёт :-)

а точно, не заметил
но все-таки главный вопрос насчёт скана. есть ведь подзапрос с агрегатной функцией к той же таблице
13 дек 19, 20:30    [22040226]     Ответить | Цитировать Сообщить модератору
 Re: Изящно выбрать последних  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Shakill
но все-таки главный вопрос насчёт скана. есть ведь подзапрос с агрегатной функцией к той же таблице
Ну и что, сервер же не строит план, напрямую переводя синтаксис запроса в выполняемые операторы.
И вот, как то догадался, что не нужно делать джойн, что можно обойтись одним сканом.
13 дек 19, 20:47    [22040234]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить