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

Откуда:
Сообщений: 203
Всем привет!
есть запрос, который
1) находит группы товаров-складов в разрезе дат
2) для каждой найденной группы находит нулевой остаток товара в разрезе склада на дату из п.1
3) из получившегося в п.2 варианта выбирает данные по максимальной дате

declare @cd date	
set @cd = '20120101'	
	
select 	
a.[Item No_]	
,a.[Location Code]	
,max(a.[Posting Date]) as 'Posting Date'	
from 	
(	
select	
ile.[Item No_],	
ile.[Location Code],	
ile.[Posting Date]	
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd and ile.[Positive]=0	
and (select round(sum(s.[Quantity]),2) from [dbo].[Company$Item Ledger Entry] s	
  where s.[Item No_]=ile.[Item No_]	
  and s.[Location Code]=ile.[Location Code]	
  and s.[Posting Date] <= ile.[Posting Date])=0	
group by ile.[Item No_], ile.[Location Code], ile.[Posting Date]	
) a	
group by a.[Item No_], a.[Location Code]	


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

Я пробовал оптимизировать запрос - что-то типа такого:

declare @cd date	
set @cd = '20120101'

select 
ile.[Item No_],	
ile.[Location Code],	
max(ile.[Posting Date])
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd and ile.[Positive]=0	
and (select round(sum(s.[Quantity]),2) from [dbo].[Company$Item Ledger Entry] s	
  where s.[Item No_]=ile.[Item No_]	
  and s.[Location Code]=ile.[Location Code]	
  and s.[Posting Date] <= max(ile.[Posting Date]))=0	
group by ile.[Item No_], ile.[Location Code]

Но такой запрос не выполняется...
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause 
or a select list, and the column being aggregated is an outer reference.


Можно как-то оптимизировать запрос?
Спасибо!
26 дек 17, 14:50    [21063383]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
ptr128
Member

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

А как Вы представляете себе оптимизацию запроса, когда не известны ни планы запроса, ни даже индексы таблиц?
26 дек 17, 15:10    [21063491]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks222
Guest
ptr128
Yury609,

А как Вы представляете себе оптимизацию запроса, когда не известны ни планы запроса, ни даже индексы таблиц?


Элементарно, ватсон!

Главная и единственно верная оптимизация запроса - написать его "покороче".
Т.е. без лишних выборок, группировок и т.д. и т.п.
Разглядывание планов - отдыхает.

PS. В запросе страдальца аж три группировки и один подзапрос.
Изничтожение любого и... все станет летать.
26 дек 17, 15:29    [21063591]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Подозреваю, что автору надо открыть для себя магический мир ОКОН.

SUM() OVER( PartitionBy ... GroupBy...) 
26 дек 17, 15:30    [21063592]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Cammomile
Подозреваю, что автору надо открыть для себя магический мир ОКОН.

SUM() OVER( PartitionBy ... GroupBy...) 

в смысле OrderBy - так он только для 2012+ падонкафщаслифчикофф.
26 дек 17, 15:31    [21063600]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
ptr128,
вот план

+

<?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.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4.49642" StatementText="select top (1) a.[Item No_] ,a.[Location Code] ,max(a.[Posting Date]) as 'Posting Date' from ( select top(1) ile.[Item No_], ile.[Location Code], ile.[Posting Date] from [dbo].[Company$Item Ledger Entry] ile where ile.[Posting Date] < @cd and ile.[Positive]=0 and (select round(sum(s.[Quantity]),2) from [dbo].[Company$Item Ledger Entry] s where s.[Item No_]=ile.[Item No_] and s.[Location Code]=ile.[Location Code] and s.[Posting Date] <= ile.[Posting Date])=0 group by ile.[Item No_], ile.[Location Code], ile.[Posting Date] ) a group by a.[Item No_], a.[Location Code] " StatementType="SELECT" QueryHash="0x63195E63362D3D85" QueryPlanHash="0xBE5F4B366436B6C0" RetrievedFromCache="true">
<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" MemoryGrant="1040" CachedPlanSize="64" CompileTime="76" CompileCPU="76" CompileMemory="1520">
<MissingIndexes>
<MissingIndexGroup Impact="75.3686">
<MissingIndex Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[Item No_]" ColumnId="1" />
<Column Name="[Location Code]" ColumnId="5" />
</ColumnGroup>
<ColumnGroup Usage="INEQUALITY">
<Column Name="[Posting Date]" ColumnId="6" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[$Cnt]" ColumnId="7" />
<Column Name="[SUM$Quantity]" ColumnId="8" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1040" RequiredMemory="1024" DesiredMemory="1040" RequestedMemory="1040" GrantWaitTime="0" GrantedMemory="1040" MaxUsedMemory="104" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209703" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="37" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="4.49642">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="37" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="4.49642">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="37" EstimateCPU="0.00607979" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4.49642">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="[БД].[dbo].[Company$Item Ledger Entry].[Posting Date] as [ile].[Posting Date]">
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="37" EstimateCPU="2.42457" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Flow Distinct" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="4.49642">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</OutputList>
<MemoryFractions Input="0" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[БД].[dbo].[Company$Item Ledger Entry].[Item No_] as [ile].[Item No_] = [БД].[dbo].[Company$Item Ledger Entry].[Item No_] as [ile].[Item No_] AND [БД].[dbo].[Company$Item Ledger Entry].[Location Code] as [ile].[Location Code] = [БД].[dbo].[Company$Item Ledger Entry].[Location Code] as [ile].[Location Code] AND [БД].[dbo].[Company$Item Ledger Entry].[Posting Date] as [ile].[Posting Date] = [БД].[dbo].[Company$Item Ledger Entry].[Posting Date] as [ile].[Posting Date]">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="37" EstimateCPU="1.86472" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="4" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="4.47864">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="54" EstimateCPU="9.99297" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="39.3215" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.47861">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</OuterReferences>
<RelOp AvgRowSize="38" EstimateCPU="12.0081" EstimateIO="53.7091" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="39.3215" LogicalOp="Index Scan" NodeId="6" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00435794" TableCardinality="10916300">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</DefinedValue>
</DefinedValues>
<Object Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Index="[$4]" Alias="[ile]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[БД].[dbo].[Company$Item Ledger Entry].[Posting Date] as [ile].[Posting Date]<[@cd] AND [БД].[dbo].[Company$Item Ledger Entry].[Positive] as [ile].[Positive]=(0)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@cd" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Positive" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="24" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="39.3214" EstimateRewinds="1.64479E-05" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4.47393">
<OutputList>
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="globalagg1008" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="globalagg1010" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="32" EstimateCPU="1.7E-06" EstimateIO="0" EstimateRebinds="39.3214" EstimateRewinds="1.64479E-05" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="9" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="4.47393">
<OutputList>
<ColumnReference Column="globalagg1008" />
<ColumnReference Column="globalagg1010" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="3" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="globalagg1008" />
<ScalarOperator ScalarString="SUM([partialagg1007])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="partialagg1007" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="globalagg1010" />
<ScalarOperator ScalarString="SUM([partialagg1009])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="partialagg1009" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="32" EstimateCPU="0.0045237" EstimateIO="0" EstimateRebinds="39.3214" EstimateRewinds="1.64479E-05" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Aggregate" NodeId="10" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="4.47386">
<OutputList>
<ColumnReference Column="partialagg1007" />
<ColumnReference Column="partialagg1009" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="partialagg1007" />
<ScalarOperator ScalarString="SUM([БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[$Cnt])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="$Cnt" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="partialagg1009" />
<ScalarOperator ScalarString="SUM([БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[SUM$Quantity])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="SUM$Quantity" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="62" EstimateCPU="0.00844862" EstimateIO="0.0846065" EstimateRebinds="39.3214" EstimateRewinds="1.64479E-05" EstimatedExecutionMode="Row" EstimateRows="7537.84" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="3.75045" TableCardinality="2799550">
<OutputList>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="$Cnt" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="SUM$Quantity" />
</OutputList>
<Warnings>
<ColumnsWithNoStatistics>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="$Cnt" />
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="SUM$Quantity" />
</ColumnsWithNoStatistics>
</Warnings>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="13" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="true" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="$Cnt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="SUM$Quantity" />
</DefinedValue>
</DefinedValues>
<Object Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Index="[VSIFTIDX]" IndexKind="ViewClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Item No_" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[БД].[dbo].[Company$Item Ledger Entry].[Item No_] as [ile].[Item No_]">
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Item No_" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[Posting Date] IS NOT NULL AND [БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[Posting Date]<=[БД].[dbo].[Company$Item Ledger Entry].[Posting Date] as [ile].[Posting Date] AND [БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[Item No_] IS NOT NULL AND [БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[Location Code] IS NOT NULL AND [БД].[dbo].[Company$Item Ledger Entry$VSIFT$2].[Location Code]=[БД].[dbo].[Company$Item Ledger Entry].[Location Code] as [ile].[Location Code]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Posting Date" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Item No_" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Location Code" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry$VSIFT$2]" Column="Location Code" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="[ile]" Column="Location Code" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
<IndexedViewInfo>
<Object Database="[БД]" Schema="[dbo]" Table="[Company$Item Ledger Entry]" Alias="s" />
</IndexedViewInfo>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="round([Expr1004],(2))=(0.00000000000000000000)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="round">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1004" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0.00000000000000000000)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</Top>
</RelOp>
<ParameterList>
<ColumnReference Column="@cd" ParameterRuntimeValue="'2012-01-01'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>


Вот индексы (на мой взгляд самые подходящие)

+

USE [БД]
GO

/****** Object: View [dbo].[Компания$Item Ledger Entry$VSIFT$2] Script Date: 26.12.2017 15:35:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Company$Item Ledger Entry$VSIFT$2] WITH SCHEMABINDING AS SELECT "Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date",COUNT_BIG(*) "$Cnt",SUM("Quantity") "SUM$Quantity",SUM("Invoiced Quantity") "SUM$Invoiced Quantity" FROM dbo."Company$Item Ledger Entry" GROUP BY "Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date"
GO



+

USE [БД]
GO

/****** Object: View [dbo].[Company$Item Ledger Entry$VSIFT$4] Script Date: 26.12.2017 15:33:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Company$Item Ledger Entry$VSIFT$4] WITH SCHEMABINDING AS SELECT "Item No_","Open","Variant Code","Positive","Location Code","Posting Date",COUNT_BIG(*) "$Cnt",SUM("Quantity") "SUM$Quantity",SUM("Remaining Quantity") "SUM$Remaining Quantity" FROM dbo."Company$Item Ledger Entry" GROUP BY "Item No_","Open","Variant Code","Positive","Location Code","Posting Date"
GO



26 дек 17, 15:36    [21063624]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
Руслан Дамирович
Cammomile
Подозреваю, что автору надо открыть для себя магический мир ОКОН.

SUM() OVER( PartitionBy ... GroupBy...) 

в смысле OrderBy - так он только для 2012+ падонкафщаслифчикофф.


Я как раз один из таких щаслифчикофф, просто пока не силен в ОКНАх...
26 дек 17, 15:39    [21063645]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks222
Guest
Нафига этот "план"?

Нате вам "оптимизацию"
declare @cd date	
set @cd = '20120101'	
	
select 	
a.[Item No_]	
,a.[Location Code]	
,max(a.[Posting Date]) as 'Posting Date'	
from 	
(	
select	
ile.[Item No_],	
ile.[Location Code],	
ile.[Posting Date]	
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd and ile.[Positive]=0	
and (select round(sum(s.[Quantity]),2) from [dbo].[Company$Item Ledger Entry] s	
  where s.[Item No_]=ile.[Item No_]	
  and s.[Location Code]=ile.[Location Code]	
  and s.[Posting Date] <= ile.[Posting Date])=0	
--group by ile.[Item No_], ile.[Location Code], ile.[Posting Date]	
) a	
group by a.[Item No_], a.[Location Code]	
26 дек 17, 15:42    [21063658]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Yury609

Я как раз один из таких щаслифчикофф, просто пока не силен в ОКНАх...


Ну вот попробуй

SUM() OVER (Partition BY  ile.[Item No_], ile.[Location Code], ile.[Posting Date] ) AS GroupSum 
26 дек 17, 15:50    [21063707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
aleks222
Нафига этот "план"?

Нате вам "оптимизацию"
declare @cd date	
set @cd = '20120101'	
	
select 	
a.[Item No_]	
,a.[Location Code]	
,max(a.[Posting Date]) as 'Posting Date'	
from 	
(	
select	
ile.[Item No_],	
ile.[Location Code],	
ile.[Posting Date]	
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd and ile.[Positive]=0	
and (select round(sum(s.[Quantity]),2) from [dbo].[Company$Item Ledger Entry] s	
  where s.[Item No_]=ile.[Item No_]	
  and s.[Location Code]=ile.[Location Code]	
  and s.[Posting Date] <= ile.[Posting Date])=0	
--group by ile.[Item No_], ile.[Location Code], ile.[Posting Date]	
) a	
group by a.[Item No_], a.[Location Code]	


эта оптимизация - "слишком" ничтожная...
запрос все равно продолжает рассчитывать остаток для всех найденных групп, а мне нужно при первом же нахождении "нулевого" остатка с учетом "убывания" дат, переходить к следующей подгруппе...
Неужели тут ОКНА сделают "чудеса"? (это уже вопрос к остальным участникам топика)
26 дек 17, 15:57    [21063737]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Окна не делают чудес, просто мне показалось, что окно замена тому, что ты написал во втором своем запросе, где у тебя ошибка синтаксиса.

Ты можешь, как мне кажется, вынести этот свой расчет сумм по группам в окно, а потом фильтровать по результатам этого вычисления.


"не считать как только ноль", это, увы не про сервер. Ну или надо отдельно шибко думать.
26 дек 17, 16:06    [21063785]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
И вообще, попробуй текстом сформулировать ДАНО и НАДО.
Вдруг твой вопрос вообще решается аплаем с топ 1.
26 дек 17, 16:08    [21063791]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
Yury609,

то что вы хотите - получить дату когда случился нулевой остаток для пары товар-склад (если я правильно понял)
при условии, что ваши остатки не рассчитаны заранее можно сделать только пройдя по вашей таблице "сверху вниз".
при этом сам проход по таблице вы можете отдать на волю сервера и тогда сервер посчитает остатки на для всех пар из которых вы потом отфильтруете нулевые
либо итерироваться по таблице "руками" в цикле и тогда вы сами можете контролировать момент "перехода к следующей паре если остаток нулевой".

главный вопрос... эта оптимизация вызвана проблемами с производительностью или с представлением как было бы лучше?
26 дек 17, 16:31    [21063926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
Cammomile
И вообще, попробуй текстом сформулировать ДАНО и НАДО.
Вдруг твой вопрос вообще решается аплаем с топ 1.


Дано:

Дата Товар Склад Количество Тип Положительно
01.12.2012 МОЛОКО ПЕРВЫЙ 10 Приход Да
05.12.2012 МОЛОКО ПЕРВЫЙ 15 Приход Да
07.12.2012 МОЛОКО ПЕРВЫЙ -12 Расход Нет
10.12.2012 МОЛОКО ПЕРВЫЙ -7 Расход Нет
15.12.2012 МОЛОКО ПЕРВЫЙ -6 Расход Нет
01.01.2013 МОЛОКО ПЕРВЫЙ 50 Приход Да
01.02.2013 МОЛОКО ПЕРВЫЙ -40 Расход Нет
03.02.2013 МОЛОКО ПЕРВЫЙ -10 Расход Нет

+

т.е. остатки на дату получаются следующие:
Дата остатки
01.12.2012 10
05.12.2012 25
07.12.2012 13
10.12.2012 6
15.12.2012 0
01.01.2013 50
01.02.2013 10
03.02.2013 0


Надо:

Дата Товар Склад
03.02.2013 МОЛОКО ПЕРВЫЙ

т.е. надо получить максимальную дату (меньше заданной, например 01.04.2015) по товару-складу, на которую остатки обнуляются
В приведенном выше примере - остатки обнуляются на даты 15.12.2012 и 03.02.2013
Вот 03.02.2013 дата и нужна.
26 дек 17, 16:35    [21063955]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
Дедушка
Yury609,

то что вы хотите - получить дату когда случился нулевой остаток для пары товар-склад (если я правильно понял)
при условии, что ваши остатки не рассчитаны заранее можно сделать только пройдя по вашей таблице "сверху вниз".
при этом сам проход по таблице вы можете отдать на волю сервера и тогда сервер посчитает остатки на для всех пар из которых вы потом отфильтруете нулевые
либо итерироваться по таблице "руками" в цикле и тогда вы сами можете контролировать момент "перехода к следующей паре если остаток нулевой".

главный вопрос... эта оптимизация вызвана проблемами с производительностью или с представлением как было бы лучше?


и то и другое.
На таблице 10+млн записей с 2001 по 2017 год - на 2012 год запрос отработал за 3.5 часа (на не самом быстром сервере)
Т.к. задача разовая - то время особо не критично.
Хочется и быстро, и оптимально. )))
26 дек 17, 16:38    [21063980]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
а если сумма на сойдётся (0 не будет), то что делать будете? а если один и тот же товар назвали по разному?
про кассовый разрыв слышали?
26 дек 17, 16:42    [21064001]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4536
Yury609, оптимально сразу при изменениях - перерерасщитывать и хранить...
+
select 
ile.[Item No_],	
ile.[Location Code],
max(ile.[Posting Date]) as 'Posting Date'
from
(	
select	
ile.[Item No_],	
ile.[Location Code],	
ile.[Posting Date],
sum(Quantity) over(partition by ile.[Item No_],	ile.[Location Code] order by ile.[Posting Date]) as Rt
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd 
) running
where round(running.Rt, 2) = 0
group by 
ile.[Item No_],	
ile.[Location Code]

как и предлагали с окном не быстрее?
26 дек 17, 16:44    [21064019]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4536
buser, безграмотность одолела :)
26 дек 17, 16:45    [21064025]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
Konst_One
а если сумма на сойдётся (0 не будет), то что делать будете? а если один и тот же товар назвали по разному?
про кассовый разрыв слышали?


Если сумма не сойдется - то ничего страшного, не нашлось, так не нашлось.
Товары я фильтрую по коду, как он там назывался в разных транзакциях - не принципиально.
Ситуаций, когда товар приходуется как МОЛОКО, а списывается как СГУЩЕНКА - исключен.
Так же исключен "отрицательный" остаток на складе, т.е. нельзя списать больше, чем есть на самом деле.
В любом случае - я принимаю "данные" - как есть, т.е. на всякие "ошибки" не анализирую.
26 дек 17, 16:46    [21064037]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
я так понял, это вы сейчас занимаетесь нормализацией старой базы. если это задача одноразовая, то скорость запросов не столь важна. переносите сразу в верную структуру и делайте сразу ,чтобы остатки подсчитывались сразу при проведении операции и хранились отдельно
26 дек 17, 16:51    [21064062]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
buser
Yury609, оптимально сразу при изменениях - перерерасщитывать и хранить...
+
select 
ile.[Item No_],	
ile.[Location Code],
max(ile.[Posting Date]) as 'Posting Date'
from
(	
select	
ile.[Item No_],	
ile.[Location Code],	
ile.[Posting Date],
sum(Quantity) over(partition by ile.[Item No_],	ile.[Location Code] order by ile.[Posting Date]) as Rt
from [dbo].[Компания$Item Ledger Entry] ile	
where 	
ile.[Posting Date] < @cd 
) running
where round(running.Rt, 2) = 0
group by 
ile.[Item No_],	
ile.[Location Code]

как и предлагали с окном не быстрее?


как-то не особо быстро...
с top(1) уже минуты 3 выполняется

раньше с top(1) 2 секунды было
26 дек 17, 16:53    [21064078]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4536
Yury609
как-то не особо быстро...
с top(1) уже минуты 3 выполняется

раньше с top(1) 2 секунды было

Стесняюсь спросить... а вы куда топ ставили?
26 дек 17, 16:58    [21064100]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Yury609
Member

Откуда:
Сообщений: 203
Konst_One
я так понял, это вы сейчас занимаетесь нормализацией старой базы. если это задача одноразовая, то скорость запросов не столь важна. переносите сразу в верную структуру и делайте сразу ,чтобы остатки подсчитывались сразу при проведении операции и хранились отдельно


Konst_One, с точки зрения нормализации - с базой полный порядок.
это в 1С остатки хранятся на даты, во многих других базах - остатки рассчитываются на даты посредством нужных индектов\view
(не хочу поднимать холивар, поэтому кто хочет порассуждать на тему "как хранить остатки на даты" - то это в отдельную тему.)
26 дек 17, 16:58    [21064101]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
то что у вас выше в качестве примера - явно не очень структура.
26 дек 17, 16:59    [21064106]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Так что-ли?
WITH
m AS (
SELECT
  [Item]
  [Location],
  [Date] = MAX( [Date] )
FROM
  [Ledger]
WHERE
  [Date] < @cd
GROUP BY
  [Item]
  [Location]
HAVING
  ROUND( SUM( [Quantity] ), 2 ) = 0
)
SELECT
  t.*
FROM
  m
  INNER JOIN [Legder] t ON (
            t.[Item] = m.[Item]
     AND t.[Location] = m.[Location]
     AND t.[Date] = m.[Date] )
;
26 дек 17, 17:08    [21064142]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить