Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Select top и функция. Помогите оптимизировать  [new]
select top
Guest
Добрый день.

Подскажите пожалуйста что происходит с оптимизатором в следующих запросах:

Итак, имеем таблицу вида:
create table dbo.t (
	id int identity(1, 1)
	,val int
   ,created_on datetime constraint [pk_t] primary key clustered ([id] asc)
   )

CREATE NONCLUSTERED INDEX [idx_t_created_on] ON dbo.t
(
	[created_on] ASC
)

ALTER TABLE dbo.t ADD  CONSTRAINT [df_t_created_on]  DEFAULT (getdate()) FOR [created_on]

go


В эту таблицу вставляем тестовые данные:
insert into dbo.t(val)
select o.object_id from sys.objects o
join sys.columns c on c.object_id = o.object_id

go 100



Теперь два запроса:

Запрос 1:
select top 51 * from(
select created_on from dbo.t
)a
order by a.created_on asc


План:
+ Запрос 1
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="51" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.00348957" StatementText="select top 51 * from(
select created_on from dbo.t
)a
order by a.created_on asc
" StatementType="SELECT" QueryHash="0x535A8D18F52F21E5" QueryPlanHash="0xEA240FFEF5B45CAD" RetrievedFromCache="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="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="157286" EstimatedPagesCached="39321" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="15" EstimateCPU="5.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00348957">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(51)">
<Const ConstValue="(51)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.477227" EstimateIO="1.27275" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51" LogicalOp="Index Scan" NodeId="1" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00348447" TableCardinality="433700">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB]" Schema="[dbo]" Table="[t]" Index="[idx_t_created_on]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



Запрос 2:
select top 51 * from(
select dateadd(hour, 2, created_on) as created_on from dbo.t
)a
order by a.created_on asc


План 2:
+ План
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="51" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="20.2067" StatementText="select top 51 * from(
select dateadd(hour, 2, created_on) as created_on from dbo.t
)a
order by a.created_on asc
" StatementType="SELECT" QueryHash="0xB79566E0CDE9E597" QueryPlanHash="0x9E6FDDF0208B87A8" RetrievedFromCache="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="4" MemoryGrant="1024" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="152">
<ThreadStat Branches="1" UsedThreads="4">
<ThreadReservation NodeId="0" ReservedThreads="4" />
</ThreadStat>
<MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="448" DesiredMemory="480" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="448" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="157286" EstimatedPagesCached="39321" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="15" EstimateCPU="5.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="20.2067">
<OutputList>
<ColumnReference Column="Expr1003" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(51)">
<Const ConstValue="(51)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.0288159" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="20.2067">
<OutputList>
<ColumnReference Column="Expr1003" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1003" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="18.6392" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="20.1778">
<OutputList>
<ColumnReference Column="Expr1003" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="51" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="51">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1003" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="0.021685" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="433700" LogicalOp="Compute Scalar" NodeId="3" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.53305">
<OutputList>
<ColumnReference Column="Expr1003" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="dateadd(hour,(2),[DB].[dbo].[t].[created_on])">
<Intrinsic FunctionName="dateadd">
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="0.238614" EstimateIO="1.27275" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="433700" LogicalOp="Index Scan" NodeId="4" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.51137" TableCardinality="433700">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="104040" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="2" ActualRows="104995" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="3" ActualRows="112332" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="1" ActualRows="112333" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[t]" Column="created_on" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB]" Schema="[dbo]" Table="[t]" Index="[idx_t_created_on]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</TopSort>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



Вопрос:
почему в первом случае оптимизатор выбирает 51 запись, а во втором случае сначала происходит преобразование всех записей, затем уже только сортировка и далее фильтр?
10 апр 14, 12:46    [15861083]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
select top
Guest
Вот план в Jpeg
10 апр 14, 12:48    [15861098]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
select top
Guest


К сообщению приложен файл. Размер - 77Kb
10 апр 14, 12:48    [15861102]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
select top,

+
select top 51 * 
from
(
	select [expression: created_on] = dateadd(hour, 2, created_on), [column: created_on] = created_on from dbo.t
) a
order by [expression: created_on]  asc

select top 51 * 
from
(
	select [expression: created_on] = dateadd(hour, 2, created_on), [column: created_on] = created_on from dbo.t
) a
order by  [column: created_on]  asc

Потому, что вы в одном случае сортируете по колонке, во втором, по выражению. Если вы сортируете по выражению, то его нужно сначала вычислить, иначе непонятно по каким значениям сортировать.
10 апр 14, 13:02    [15861184]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
select top
Guest
SomewhereSomehow,

Обойти такую ситуацию никак нельзя?
Ведь очевидно же, что без вычисления выражения можно отсортировать 51 записи, а потом уже к ним применить эту функцию.

Только к сожалению это не процедура, а представление и я не смогу производить каких-то действий с разбиением запроса на несколько.
10 апр 14, 13:09    [15861225]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Добавьте в представление оригинальное поле created_on и сортируйте по нему.
10 апр 14, 13:11    [15861246]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
select top,

а как он может осортировать и тем более отфильтровать НЕИЗВЕСТНЫЕ (непрообразованные) данные?
10 апр 14, 13:12    [15861252]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
select top
Ведь очевидно же, что без вычисления выражения можно отсортировать 51 записи, а потом уже к ним применить эту функцию.
Это как это?!
Поставить себя на место сервера слабО?

Но можно сделать вычисляемое поле и проиндексировать его.
Если так уж хочется.
10 апр 14, 13:12    [15861259]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
select top
SomewhereSomehow,

Обойти такую ситуацию никак нельзя?
Ведь очевидно же, что без вычисления выражения можно отсортировать 51 записи, а потом уже к ним применить эту функцию.

Только к сожалению это не процедура, а представление и я не смогу производить каких-то действий с разбиением запроса на несколько.

можно, ордер бай убери , а топ оставь.
10 апр 14, 13:14    [15861285]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
а вообще суть проблемы - в общем случае последовательность отсортированных функций от значения не совпадает с сортировкой самих значений!
10 апр 14, 13:16    [15861305]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
1. А зачем тут вообще подзапрос?
2. Если ко всем записям прибавляется 2 часа, то что мешает это сделать уже после выборки 50 записей, а сортировать по оригинальному полю?
11 апр 14, 00:30    [15865500]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Имхо, Внутри представлений сортировки лучше не писать!!!! А делать это уже снаружи. Иначе представления поверх представлений построить будет проблемно
11 апр 14, 10:26    [15866389]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Mike_za
Имхо, Внутри представлений сортировки лучше не писать!!!! А делать это уже снаружи. Иначе представления поверх представлений построить будет проблемно
А где тут представление?
11 апр 14, 10:29    [15866409]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Mike_za
Имхо, Внутри представлений сортировки лучше не писать!!!! А делать это уже снаружи. Иначе представления поверх представлений построить будет проблемно
Причем здесь "лучше" или "хуже"?
Здесь сортировка совместно с предложением TOP играет роль фильтрации, а не "чистой" сортировки.
11 апр 14, 10:34    [15866446]     Ответить | Цитировать Сообщить модератору
 Re: Select top и функция. Помогите оптимизировать  [new]
select top
Guest
Всем спасибо. Решил свою проблему дополнительной таблицей с конвертированным временем в нем. Её и привязываю к основному запросу и сортирую по этому полю.

Mike_za
1. А зачем тут вообще подзапрос?
2. Если ко всем записям прибавляется 2 часа, то что мешает это сделать уже после выборки 50 записей, а сортировать по оригинальному полю?


1. Этот подзапрос строю не я и менять его не могу. В качестве подзапроса выступает вьюха.
2. Мешает опять-таки то, что я не могу менять саму структуру подзапроса, когда верхний запрос берет top 51 и сортирует по этому полю.
14 апр 14, 11:45    [15878086]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить