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

DECLARE @ObjectIds xml = '<Ids><Id>249591</Id><Id>249608</Id></Ids>' 
--DECLARE @ObjectIds xml = '<Ids><Id>12</Id></Ids>'
DECLARE @StartDate datetime = GetUTCDATE() -1
DECLARE @EndDate datetime = getutcdate()


declare @IdsTable table(Id int primary key);
insert into @IdsTable select * from dbo.ParseIds(@ObjectIds)

 SELECT TOP 100   
    p.Id AS PostId,
    p.PostContentId AS PostContentId,
    p.Url AS Url,
    a.Name AS Author,
    p.BlogId as Blog,   
    b.BlogHostId as BlogHostId,
    p.Title AS Title,
    p.UpdateDate AS UpdateDate
   FROM Posts p WITH (NOLOCK)
  join PostObjects po WITH (NOLOCK) on po.PostId = p.Id 
  INNER JOIN @IdsTable oids ON oids.Id = po.ObjectId   
    JOIN dbo.Blogs AS b WITH (NOLOCK) ON p.BlogId = b.Id
    JOIN Authors AS a WITH (NOLOCK) ON p.PersonId = a.Id   
    WHERE b.BlogHostId IS NOT NULL 
   AND p.PostContentId IS NOT NULL 
   AND b.BlogHostId = 1 
   AND p.IsComment <> 1  
    ORDER BY p.UpdateDate DESC
   option(recompile)


Выполняенся фактически мгновенно с таким планом:
+

|--Top(TOP EXPRESSION:((100)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([p].[BlogId], [Expr1017]) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([p].[PersonId], [Expr1016]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, WHERE:([Blogans_PROD].[dbo].[PostObjects].[ObjectId] as [po].[ObjectId]=@IdsTable.[Id] as [oids].[Id]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([po].[Id], [Expr1015]) WITH ORDERED PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[Id], [Expr1014]) WITH ORDERED PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[Id], [Expr1013]) WITH ORDERED PREFETCH)
| | | | | |--Index Scan(OBJECT:([Blogans_PROD].[dbo].[Posts].[IX_Posts_UpdateDate] AS [p]), WHERE:([Blogans_PROD].[dbo].[Posts].[PostContentId] as [p].[PostContentId] IS NOT NULL) ORDERED BACKWARD)
| | | | | |--Clustered Index Seek(OBJECT:([Blogans_PROD].[dbo].[Posts].[PK_Posts] AS [p]), SEEK:([p].[Id]=[Blogans_PROD].[dbo].[Posts].[Id] as [p].[Id]), WHERE:([Blogans_PROD].[dbo].[Posts].[IsComment] as [p].[IsComment]<(1) OR [Blogans_PROD].[dbo].[Posts].[IsComment] as [p].[IsComment]>(1)) LOOKUP ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([Blogans_PROD].[dbo].[PostObjects].[IX_FK_PostPostObjects] AS [po]), SEEK:([po].[PostId]=[Blogans_PROD].[dbo].[Posts].[Id] as [p].[Id]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([Blogans_PROD].[dbo].[PostObjects].[PK_SearchObjects] AS [po]), SEEK:([po].[Id]=[Blogans_PROD].[dbo].[PostObjects].[Id] as [po].[Id]) LOOKUP ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:(@IdsTable AS [oids]))
| |--Clustered Index Seek(OBJECT:([Blogans_PROD].[dbo].[Authors].[PK_Authors] AS [a]), SEEK:([a].[Id]=[Blogans_PROD].[dbo].[Posts].[PersonId] as [p].[PersonId]) ORDERED FORWARD)
|--Index Seek(OBJECT:([Blogans_PROD].[dbo].[Blogs].[IX_FK_BlogHostBlog] AS [b]), SEEK:([b].[BlogHostId]=(1) AND [b].[Id]=[Blogans_PROD].[dbo].[Posts].[BlogId] as [p].[BlogId]) ORDERED FORWARD)



Проблемы начинаются, когда в таблице @IdsTable нет записей... он начинает сканировать многомиллионные таблички и результат выдается минут через 10 причем в результате целых ноль записей. План выполнения при этом такой:

+

<?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.1" Build="10.50.1746.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0132874" StatementText="insert into @IdsTable select * from dbo.ParseIds(@ObjectIds)

" StatementType="INSERT" QueryHash="0x3838C231A0F4AEFE" QueryPlanHash="0x3A960D914DEF985B">
<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" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="168">
<RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.0132874">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<TableValuedFunction>
<DefinedValues />
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Index="[PK__ParseIds__3214EC0741D98783]" />
<ParameterList>
<ScalarOperator ScalarString="[@ObjectIds]">
<Identifier>
<ColumnReference Column="@ObjectIds" />
</Identifier>
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Insert" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.0132842">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@IdsTable]" Index="[PK__#79B0D0C__3214EC077B991932]" />
<SetPredicate>
<ScalarOperator ScalarString="[Id] = [Blogans_PROD].[dbo].[ParseIds].[Id]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="Id" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Column="Id" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="4" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[ParseIds]" Index="[PK__ParseIds__3214EC0741D98783]" />
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@ObjectIds" ParameterRuntimeValue="N'<Ids><Id>12</Id></Ids>'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="6" StatementEstRows="100" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="45.518" StatementText="SELECT TOP 100
p.Id AS PostId,
p.PostContentId AS PostContentId,
p.Url AS Url,
a.Name AS Author,
p.BlogId as Blog,
b.BlogHostId as BlogHostId,
p.Title AS Title,
p.UpdateDate AS UpdateDate
FROM dbo.Posts p WITH (NOLOCK)
join dbo.PostObjects po WITH (NOLOCK) on po.PostId = p.Id
INNER JOIN @IdsTable oids ON oids.Id = po.ObjectId
JOIN dbo.Blogs AS b WITH (NOLOCK) ON p.BlogId = b.Id
JOIN dbo.Authors AS a WITH (NOLOCK) ON p.PersonId = a.Id
WHERE b.BlogHostId IS NOT NULL
AND p.PostContentId IS NOT NULL
AND b.BlogHostId = 1
AND p.IsComment <> 1
ORDER BY p.UpdateDate DESC
option(recompile)



" StatementType="SELECT" QueryHash="0xD40E18AC0474DFC0" QueryPlanHash="0x3E5A27FDC3669C8A">
<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" CachedPlanSize="80" CompileTime="59" CompileCPU="57" CompileMemory="2000">
<RelOp AvgRowSize="10039" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="45.518">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="BlogHostId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(100)">
<Const ConstValue="(100)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="10039" EstimateCPU="1.49323" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="45.518">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="BlogHostId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Column="Expr1017" />
</OuterReferences>
<RelOp AvgRowSize="10035" EstimateCPU="1.49323" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="45.2073">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Column="Expr1016" />
</OuterReferences>
<RelOp AvgRowSize="8037" EstimateCPU="83.2788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="44.8758">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[PostObjects].[ObjectId] as [po].[ObjectId]=@IdsTable.[Id] as [oids].[Id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="ObjectId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@IdsTable" Alias="[oids]" Column="Id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="8041" EstimateCPU="83.2788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5577.08" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="44.4037">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="ObjectId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="17616676" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
<ColumnReference Column="Expr1015" />
</OuterReferences>
<RelOp AvgRowSize="8041" EstimateCPU="84.2861" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5577.08" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="26.1484">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="17616676" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Column="Expr1014" />
</OuterReferences>
<RelOp AvgRowSize="8038" EstimateCPU="63.453" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4249.37" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="13.9478">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="13924032" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Column="Expr1013" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="16.7124" EstimateIO="38.1039" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4249.37" LogicalOp="Index Scan" NodeId="12" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0186233" TableCardinality="15193000">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15182622" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="UpdateDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Index="[IX_Posts_UpdateDate]" Alias="[p]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[PostContentId] as [p].[PostContentId] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PostContentId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="8022" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4249.37" EstimateRewinds="0.00195951" EstimateRows="3897.85" LogicalOp="Clustered Index Seek" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="13.9094" TableCardinality="15193000">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="13924032" ActualEndOfScans="1258590" ActualExecutions="15182622" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Url" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Title" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Index="[PK_Posts]" Alias="[p]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[Id] as [p].[Id]">
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[IsComment] as [p].[IsComment]<(1) OR [Blogans_PROD].[dbo].[Posts].[IsComment] as [p].[IsComment]>(1)">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="IsComment" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="IsComment" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.000158593" EstimateIO="0.003125" EstimateRebinds="3897.85" EstimateRewinds="0.00195951" EstimateRows="1.44812" LogicalOp="Index Seek" NodeId="24" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="12.1722" TableCardinality="20369800">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="17616676" ActualEndOfScans="13924032" ActualExecutions="13924032" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Index="[IX_FK_PostPostObjects]" Alias="[po]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="PostId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[Id] as [p].[Id]">
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="5577.08" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="18.2319" TableCardinality="20369800">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="ObjectId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="17616676" ActualEndOfScans="0" ActualExecutions="17616676" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="ObjectId" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Index="[PK_SearchObjects]" Alias="[po]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[PostObjects].[Id] as [po].[Id]">
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[PostObjects]" Alias="[po]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="5500" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="30" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.441083" TableCardinality="1">
<OutputList>
<ColumnReference Table="@IdsTable" Alias="[oids]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="17616676" ActualEndOfScans="17616676" ActualExecutions="17616676" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="@IdsTable" Alias="[oids]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Table="[@IdsTable]" Index="[PK__#7D8161A__3214EC077F69AA16]" Alias="[oids]" />
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="2011" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="99.9982" EstimateRewinds="0.00167958" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="32" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.331117" TableCardinality="2626320">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Name" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="3" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Name" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Index="[PK_Authors]" Alias="[a]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Authors]" Alias="[a]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[PersonId] as [p].[PersonId]">
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="PersonId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="99.9942" EstimateRewinds="0.00559859" EstimateRows="1" LogicalOp="Index Seek" NodeId="33" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.3102" TableCardinality="2624310">
<OutputList>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="BlogHostId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="BlogHostId" />
</DefinedValue>
</DefinedValues>
<Object Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Index="[IX_FK_BlogHostBlog]" Alias="[b]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="BlogHostId" />
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Blogs]" Alias="[b]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator ScalarString="[Blogans_PROD].[dbo].[Posts].[BlogId] as [p].[BlogId]">
<Identifier>
<ColumnReference Database="[Blogans_PROD]" Schema="[dbo]" Table="[Posts]" Alias="[p]" Column="BlogId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>


Частично ситуацию можно поправить, переписав запрос таким образом:

DECLARE @ObjectIds xml = '<Ids><Id>12</Id></Ids>'
DECLARE @StartDate datetime = GetUTCDATE() -1
DECLARE @EndDate datetime = getutcdate()


declare @IdsTable table(Id int primary key);
insert into @IdsTable select * from dbo.ParseIds(@ObjectIds)

 SELECT TOP 100   
    p.Id AS PostId,
    p.PostContentId AS PostContentId,
    p.Url AS Url,
    a.Name AS Author,
    p.BlogId as Blog,   
    b.BlogHostId as BlogHostId,
    p.Title AS Title,
    p.UpdateDate AS UpdateDate
   FROM (select po.PostId  from PostObjects po WITH (NOLOCK) 
		INNER JOIN @IdsTable oids ON oids.Id = po.ObjectId   
   ) as po
   inner join 
    dbo.Posts p WITH (NOLOCK) on p.Id=po.PostId 
    JOIN dbo.Blogs AS b WITH (NOLOCK) ON p.BlogId = b.Id
    JOIN dbo.Authors AS a WITH (NOLOCK) ON p.PersonId = a.Id   
    WHERE b.BlogHostId IS NOT NULL 
   AND p.PostContentId IS NOT NULL 
   AND b.BlogHostId = 1 
   AND p.IsComment <> 1  
    ORDER BY p.UpdateDate DESC
   option(FORCE ORDER)


Но это все равно на порядок больше, чем при обычном раскладе...

Каким-то образом можно поправить ситуацию???
12 мар 12, 13:44    [12231303]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИщуПравильныйПлан,

во-первых - SELECT @@VERSION.
Во-вторых - реальные планы
В-третьих - попробуйте добавить условие exists(select * from @IdsTable)
12 мар 12, 13:51    [12231376]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
ИщуПравильныйПлан
Guest
iljy,

select @@version

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) Dec 9 2011 11:27:20 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Добавление exists таким образом не помагает, может как-то по другому?

--DECLARE @ObjectIds xml = '<Ids><Id>249591</Id><Id>249608</Id></Ids>' 
DECLARE @ObjectIds xml = '<Ids><Id>12</Id></Ids>'
DECLARE @StartDate datetime2(7) = GetUTCDATE() -1
DECLARE @EndDate datetime2(7) = getutcdate()


declare @IdsTable table(Id int primary key);
insert into @IdsTable select * from dbo.ParseIds(@ObjectIds)

 SELECT TOP 100   
    p.Id AS PostId,
    p.PostContentId AS PostContentId,
    p.Url AS Url,
    a.Name AS Author,
    p.BlogId as Blog,   
    b.BlogHostId as BlogHostId,
    p.Title AS Title,
    p.UpdateDate AS UpdateDate
   FROM dbo.Posts p WITH (NOLOCK)
  join dbo.PostObjects po WITH (NOLOCK) on po.PostId = p.Id 
  INNER JOIN @IdsTable oids ON oids.Id = po.ObjectId   
    JOIN dbo.Blogs AS b WITH (NOLOCK) ON p.BlogId = b.Id
    JOIN dbo.Authors AS a WITH (NOLOCK) ON p.PersonId = a.Id   
    WHERE b.BlogHostId IS NOT NULL 
   AND p.PostContentId IS NOT NULL 
   AND b.BlogHostId = 1 
   AND p.IsComment <> 1  
   and exists (select * from @IdsTable) 
    ORDER BY p.UpdateDate DESC
   option(recompile)


План во вложении

К сообщению приложен файл. Размер - 92Kb
12 мар 12, 14:11    [12231550]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ну, хинтом придушите. Loop или forceseek.
12 мар 12, 14:15    [12231583]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИщуПравильныйПлан,

не надо план картинкой выкладывать, че с ней делать-то? Сохраните как план и прикрепите файл.
Но, честно говоря, все это очень странно. Можно еще попробовать 2 вещи:
1. Вместо табличной переменной использовать временную таблицу.
2. Использовать TOP(case when exists(select * from @IdsTable) then 100 else 0 end)
12 мар 12, 14:19    [12231635]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
ИщуПравильныйПлан,
автор
... and exists (select * from @IdsTable)
Это реальная жесть
мож махнуть не глядя на
and exists (select * from @IdsTable z where z.Id = po.ObjectId ) 
12 мар 12, 14:30    [12231771]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
ИщуПравильный план
Guest
iljy,

Мда через временную таблицу все просто отлично.
Блин специально же ставил option(recompile)

Извините. Спасибо большое!
12 мар 12, 14:32    [12231795]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
iljy
Member

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

это не жесть, а PRESTARTUP FILTER.
12 мар 12, 14:51    [12231993]     Ответить | Цитировать Сообщить модератору
 Re: Плохой план при пустой коррелирующей таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИщуПравильный план
iljy,

Мда через временную таблицу все просто отлично.
Блин специально же ставил option(recompile)

Извините. Спасибо большое!

Для табличных переменных не ведется статистика.
12 мар 12, 14:51    [12232002]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить