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

Откуда: this
Сообщений: 184
Доброго времени суток!

Коллега стал жаловаться, что время выполнения одного из запросов "внезапно" увеличилось с 1 секунды до 12-14 минут.
И так запрос:
+

SELECT
	[dp].[FullName]
	, [dp].[BirthDay]
	, [dd].[AccountNo]
	, [ddh].[OpDay]
	, [ddh].[OpCash]

FROM
	[Deposit].[Person] [dp]
		inner join
	[Deposit].[Deposit] [dd]
		on	[dp].[ID_MINOR]			= [dd].[PERSON_MINOR]
		and	[dp].[ID_MAJOR]			= [dd].[PERSON_MAJOR]
		and	[dp].[FullName]			= N'Иванов Иван Иванович'
		and [dp].[BirthDay]			= cast('1960-01-01' as date)
		inner join
	[Deposit].[DepositHistory] [ddh]
		on	[ddh].[DEPOSIT_MINOR]	= [dd].[ID_MINOR]
		and	[ddh].[DEPOSIT_MAJOR]	= [dd].[ID_MAJOR]
		and	[ddh].[OpDay]			>= cast('2014-09-01' as date)




Записей в таблицах:
Deposit.Person - 37 685 126
Deposit.Deposit - 41 815 456
Deposit.DepositHistory - 1 209 298 016

Имеются следующие индексы:

+

ALTER TABLE [Deposit].[Person] ADD  CONSTRAINT [PK_DEPOSIT_PERSON] PRIMARY KEY CLUSTERED 
(
	[ID_MINOR] ASC,
	[ID_MAJOR] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_DEPOSIT_PERSON_FULLNAME_BIRTHDAY] ON [Deposit].[Person]
(
	[FullName] ASC,
	[BirthDay] ASC
)
GO

CREATE NONCLUSTERED INDEX [FK_DEPOSIT_DEPOSIT_PERSON] ON [Deposit].[Deposit]
(
	[PERSON_MINOR] ASC,
	[PERSON_MAJOR] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_DEPOSIT_DEPOSIT_HISTORY_OP_DAY] ON [Deposit].[DepositHistory]
(
	[OpDay] ASC
)
INCLUDE 
( 	[BranchNo],
	[Office],
	[InsertTime],
	[OpCash],
	[State],
	[HeirNo],
	[OpKind],
	[TurnCode],
	[DEPOSIT_MINOR],
	[DEPOSIT_MAJOR],
	[Clerk],
	[JrnNo],
	[OpCode],
	[Balance]
)
GO



Во всех трех таблицах ID_MINOR, ID_MAJOR - первичный ключ.

Для запрос строится следующий план:
+

<?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.3401.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtUseDb StatementCompId="1" StatementId="1" StatementText="USE [ATOL]&#xD;&#xA;" StatementType="USE DATABASE" RetrievedFromCache="false" Database="[ATOL]" />
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00985766" StatementText="&#xD;&#xA;&#xD;&#xA;SELECT&#xD;&#xA;	[dp].[FullName]&#xD;&#xA;	, [dp].[BirthDay]&#xD;&#xA;	, [dd].[AccountNo]&#xD;&#xA;	, [ddh].[OpDay]&#xD;&#xA;	, [ddh].[OpCash]&#xD;&#xA;&#xD;&#xA;FROM&#xD;&#xA;	[Deposit].[Person] [dp]&#xD;&#xA;		inner join&#xD;&#xA;	[Deposit].[Deposit] [dd]&#xD;&#xA;		on	[dp].[ID_MINOR]			= [dd].[PERSON_MINOR]&#xD;&#xA;		and	[dp].[ID_MAJOR]			= [dd].[PERSON_MAJOR]&#xD;&#xA;		and	[dp].[FullName]			= N'Иванов Иван Иванович'&#xD;&#xA;		and [dp].[BirthDay]			= cast('1960-01-01' as date)&#xD;&#xA;		inner join&#xD;&#xA;	[Deposit].[DepositHistory] [ddh]&#xD;&#xA;		on	[ddh].[DEPOSIT_MINOR]	= [dd].[ID_MINOR]&#xD;&#xA;		and	[ddh].[DEPOSIT_MAJOR]	= [dd].[ID_MAJOR]&#xD;&#xA;		and	[ddh].[OpDay]			&gt;= cast('2014-09-01' as date)&#xD;&#xA;&#xD;&#xA;" StatementType="SELECT" QueryHash="0x335D7EFC1DF5B7AF" QueryPlanHash="0xBD9BA90FD848E7C7" 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 CachedPlanSize="32" CompileTime="14" CompileCPU="14" CompileMemory="912">
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="393216" EstimatedPagesCached="196608" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="118" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
              <OutputList>
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
              </OutputList>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                </OuterReferences>
                <RelOp AvgRowSize="72" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                  <OutputList>
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                  </OutputList>
                  <NestedLoops Optimized="true">
                    <OuterReferences>
                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MINOR" />
                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MAJOR" />
                    </OuterReferences>
                    <RelOp AvgRowSize="28" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1209300000">
                      <OutputList>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MINOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MAJOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                      </OutputList>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MINOR" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MAJOR" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Index="[IX_DEPOSIT_DEPOSIT_HISTORY_OP_DAY]" Alias="[ddh]" IndexKind="NonClustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <StartRange ScanType="GE">
                                <RangeColumns>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="'2014-09-01'">
                                    <Const ConstValue="'2014-09-01'" />
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="61" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="41815500">
                      <OutputList>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                      </OutputList>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Index="[PK_DEPOSIT_DEPOSIT]" Alias="[dd]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[ATOL].[Deposit].[DepositHistory].[DEPOSIT_MAJOR] as [ddh].[DEPOSIT_MAJOR]">
                                    <Identifier>
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MAJOR" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[ATOL].[Deposit].[DepositHistory].[DEPOSIT_MINOR] as [ddh].[DEPOSIT_MINOR]">
                                    <Identifier>
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MINOR" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
                <RelOp AvgRowSize="65" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="37685100">
                  <OutputList>
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                  </OutputList>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Index="[IX_DEPOSIT_PERSON_FULLNAME_BIRTHDAY]" Alias="[dp]" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MINOR" />
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MAJOR" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="N'Иванов Иван Иванович'">
                                <Const ConstValue="N'Иванов Иван Иванович'" />
                              </ScalarOperator>
                              <ScalarOperator ScalarString="'1960-01-01'">
                                <Const ConstValue="'1960-01-01'" />
                              </ScalarOperator>
                              <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[PERSON_MINOR] as [dd].[PERSON_MINOR]">
                                <Identifier>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[PERSON_MAJOR] as [dd].[PERSON_MAJOR]">
                                <Identifier>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>



Судя по плану выборка идет сначала из DepositHistory, из самой большой таблицы. Сам же запрос при выполнении часто висит с ожиданием PAGEIOLATCH_.

Для немного измененного запроса:
+

SELECT
	[dp].[FullName]
	, [dp].[BirthDay]
	, [dd].[AccountNo]
	, [ddh].[OpDay]
	, [ddh].[OpCash]

FROM
	[Deposit].[Person] [dp]
		inner join
	[Deposit].[Deposit] [dd]
		on	[dp].[ID_MINOR]			= [dd].[PERSON_MINOR]
		and	[dp].[ID_MAJOR]			= [dd].[PERSON_MAJOR]
		and	[dp].[FullName]			= N'Иванов Иван Иванович'
		and [dp].[BirthDay]			= cast('1960-01-01' as date)
		inner join
	[Deposit].[DepositHistory] [ddh]
		on	[ddh].[DEPOSIT_MINOR]	= [dd].[ID_MINOR]
		and	[ddh].[DEPOSIT_MAJOR]	= [dd].[ID_MAJOR]
		--and	[ddh].[OpDay]			>= cast('2014-09-01' as date)



строится следующий план:
+

<?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.3401.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtUseDb StatementCompId="1" StatementId="1" StatementText="USE [ATOL]&#xD;&#xA;" StatementType="USE DATABASE" RetrievedFromCache="false" Database="[ATOL]" />
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="854958" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="2706.51" StatementText="&#xD;&#xA;&#xD;&#xA;SELECT&#xD;&#xA;	[dp].[FullName]&#xD;&#xA;	, [dp].[BirthDay]&#xD;&#xA;	, [dd].[AccountNo]&#xD;&#xA;	, [ddh].[OpDay]&#xD;&#xA;	, [ddh].[OpCash]&#xD;&#xA;&#xD;&#xA;FROM&#xD;&#xA;	[Deposit].[Person] [dp]&#xD;&#xA;		inner join&#xD;&#xA;	[Deposit].[Deposit] [dd]&#xD;&#xA;		on	[dp].[ID_MINOR]			= [dd].[PERSON_MINOR]&#xD;&#xA;		and	[dp].[ID_MAJOR]			= [dd].[PERSON_MAJOR]&#xD;&#xA;		and	[dp].[FullName]			= N'Иванов Иван Иванович'&#xD;&#xA;		and [dp].[BirthDay]			= cast('1960-01-01' as date)&#xD;&#xA;		inner join&#xD;&#xA;	[Deposit].[DepositHistory] [ddh]&#xD;&#xA;		on	[ddh].[DEPOSIT_MINOR]	= [dd].[ID_MINOR]&#xD;&#xA;		and	[ddh].[DEPOSIT_MAJOR]	= [dd].[ID_MAJOR]&#xD;&#xA;		--and	[ddh].[OpDay]			&gt;= cast('2014-09-01' as date)&#xD;&#xA;&#xD;&#xA;" StatementType="SELECT" QueryHash="0x98163D80130049DB" QueryPlanHash="0x8B7065A1B1AD8E31" 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 CachedPlanSize="80" CompileTime="35" CompileCPU="35" CompileMemory="1272">
            <ThreadStat Branches="1" />
            <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="170072" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="393216" EstimatedPagesCached="196608" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="118" EstimateCPU="2.41992" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="854958" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2706.51">
              <OutputList>
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
              </OutputList>
              <Parallelism>
                <RelOp AvgRowSize="118" EstimateCPU="0.893431" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="854958" LogicalOp="Inner Join" NodeId="1" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2704.09">
                  <OutputList>
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                  </OutputList>
                  <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                    <OuterReferences>
                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                      <ColumnReference Column="Expr1009" />
                    </OuterReferences>
                    <RelOp AvgRowSize="120" EstimateCPU="0.893431" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="854958" LogicalOp="Inner Join" NodeId="4" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="24.7072">
                      <OutputList>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                      </OutputList>
                      <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                          <ColumnReference Column="Expr1008" />
                        </OuterReferences>
                        <RelOp AvgRowSize="117" EstimateCPU="0.00268774" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2572" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="12.5332">
                          <OutputList>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                          </OutputList>
                          <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                            <OuterReferences>
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                              <ColumnReference Column="Expr1007" />
                            </OuterReferences>
                            <RelOp AvgRowSize="75" EstimateCPU="0.00268774" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2572" LogicalOp="Inner Join" NodeId="10" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.10944">
                              <OutputList>
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                              </OutputList>
                              <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                                <OuterReferences>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MINOR" />
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MAJOR" />
                                  <ColumnReference Column="Expr1006" />
                                </OuterReferences>
                                <RelOp AvgRowSize="75" EstimateCPU="0.000385212" EstimateIO="0.00509476" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1258.04" LogicalOp="Index Seek" NodeId="12" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00547998" TableCardinality="37685100">
                                  <OutputList>
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MINOR" />
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MAJOR" />
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MINOR" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MAJOR" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Index="[IX_DEPOSIT_PERSON_FULLNAME_BIRTHDAY]" Alias="[dp]" IndexKind="NonClustered" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="FullName" />
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="BirthDay" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="N'Иванов Иван Иванович'">
                                                <Const ConstValue="N'Иванов Иван Иванович'" />
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="'1960-01-01'">
                                                <Const ConstValue="'1960-01-01'" />
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="17" EstimateCPU="0.000159249" EstimateIO="0.003125" EstimateRebinds="1257.04" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2.04444" LogicalOp="Index Seek" NodeId="13" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.10127" TableCardinality="41815500">
                                  <OutputList>
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Index="[FK_DEPOSIT_DEPOSIT_PERSON]" Alias="[dd]" IndexKind="NonClustered" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MINOR" />
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="PERSON_MAJOR" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[ATOL].[Deposit].[Person].[ID_MINOR] as [dp].[ID_MINOR]">
                                                <Identifier>
                                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MINOR" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[ATOL].[Deposit].[Person].[ID_MAJOR] as [dp].[ID_MAJOR]">
                                                <Identifier>
                                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Person]" Alias="[dp]" Column="ID_MAJOR" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </NestedLoops>
                            </RelOp>
                            <RelOp AvgRowSize="51" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="2571" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="15" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="8.42103" TableCardinality="41815500">
                              <OutputList>
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                              </OutputList>
                              <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="AccountNo" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Index="[PK_DEPOSIT_DEPOSIT]" Alias="[dd]" TableReferenceId="-1" IndexKind="Clustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[ID_MAJOR] as [dd].[ID_MAJOR]">
                                            <Identifier>
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[ID_MINOR] as [dd].[ID_MINOR]">
                                            <Identifier>
                                              <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                        <RelOp AvgRowSize="20" EstimateCPU="0.000522651" EstimateIO="0.00386574" EstimateRebinds="2571" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="332.41" LogicalOp="Index Seek" NodeId="19" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="11.2806" TableCardinality="1209300000">
                          <OutputList>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpDay" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Index="[IX_DEPOSIT_DEPOSIT_HISTORY_COMB_N_01]" Alias="[ddh]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MINOR" />
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="DEPOSIT_MAJOR" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[ID_MINOR] as [dd].[ID_MINOR]">
                                        <Identifier>
                                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MINOR" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="[ATOL].[Deposit].[Deposit].[ID_MAJOR] as [dd].[ID_MAJOR]">
                                        <Identifier>
                                          <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[Deposit]" Alias="[dd]" Column="ID_MAJOR" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="854957" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="21" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="2678.49" TableCardinality="1209300000">
                      <OutputList>
                        <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                      </OutputList>
                      <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="OpCash" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Index="[PK_DEPOSIT_DEPOSIT_HISTORY_N]" Alias="[ddh]" TableReferenceId="-1" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                                  <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[ATOL].[Deposit].[DepositHistory].[ID_MINOR] as [ddh].[ID_MINOR]">
                                    <Identifier>
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MINOR" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[ATOL].[Deposit].[DepositHistory].[ID_MAJOR] as [ddh].[ID_MAJOR]">
                                    <Identifier>
                                      <ColumnReference Database="[ATOL]" Schema="[Deposit]" Table="[DepositHistory]" Alias="[ddh]" Column="ID_MAJOR" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>



Из плана видно, что сначала выполняется выборка из таблиц Person и Deposit, а после из DepositHistory.
Сам запрос выполняется за считанные секунды.

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

ЗюЫю Запросы приходят из приложения, которое шлет их в цикле (десятками тысяч), поэтому желательно изменить именно запрос, чтобы внесение изменений в приложение было минимальным.
31 окт 14, 11:58    [16782156]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
novexelf
Member

Откуда: this
Сообщений: 184
Графический план для запроса 1

К сообщению приложен файл. Размер - 37Kb
31 окт 14, 12:06    [16782224]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
aleks2
Guest
Я б нарисовал так

SELECT
	[dp].[FullName]
	, [dp].[BirthDay]
	, [dd].[AccountNo]
	, [ddh].[OpDay]
	, [ddh].[OpCash]

FROM
	[Deposit].[Person] [dp]
	left outer join [Deposit].[Deposit] [dd]
		on	[dp].[ID_MINOR]			= [dd].[PERSON_MINOR]
		and	[dp].[ID_MAJOR]			= [dd].[PERSON_MAJOR]
	left outer [Deposit].[DepositHistory] [ddh]
		on	[ddh].[DEPOSIT_MINOR]	= [dd].[ID_MINOR]
		and	[ddh].[DEPOSIT_MAJOR]	= [dd].[ID_MAJOR]
where
		[dp].[FullName]			= N'Иванов Иван Иванович'
		and [dp].[BirthDay]	        = cast('1960-01-01' as date)
		and	[ddh].[OpDay]		>= cast('2014-09-01' as date)

А если тупой оптимизатор не поймет намека, то написал бы в конце

option(force order)
31 окт 14, 12:07    [16782228]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
novexelf
Member

Откуда: this
Сообщений: 184
Графический план для запроса 2

К сообщению приложен файл. Размер - 77Kb
31 окт 14, 12:07    [16782232]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
novexelf
Member

Откуда: this
Сообщений: 184
Спасибо aleks2!
option(force order) проблему решил.

Ушел читать что это и с чем его едят.
31 окт 14, 12:12    [16782293]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
novexelf
Спасибо aleks2!
option(force order) проблему решил.

Ушел читать что это и с чем его едят.


За одним еще почитайте что такое статистика и зачем её нужно держать в актуальном состоянии.
31 окт 14, 12:16    [16782324]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
novexelf
Member

Откуда: this
Сообщений: 184
WarAnt,

Ок.
31 окт 14, 12:37    [16782525]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
novexelf,

А у вас что приложение прям константы шлет в запросе? ('2014-09-01') Не параметры?

Я думаю что вам помогут Trace flags 2389 and 2390. Statistics on Ascending Keys
1 ноя 14, 00:48    [16786809]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
novexelf,
cast('1960-01-01' as date)
cast('19600101' as date)
1 ноя 14, 15:01    [16787829]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Владислав Колосов
novexelf,
cast('1960-01-01' as date)
cast('19600101' as date)
А в чем разница то?
3 ноя 14, 21:18    [16793812]     Ответить | Цитировать Сообщить модератору
 Re: Как повлиять на план запроса?  [new]
o-o
Guest
Mind
Владислав Колосов
novexelf,
cast('1960-01-01' as date)
cast('19600101' as date)
А в чем разница то?

может, человек хочет в явном виде услышать that the formats 'YYYY-MM-DD' and 'YYYY-MM-DD hh:mm…' are language dependent when converted to DATETIME or SMALLDATETIME, and language neutral when converted to DATE, DATETIME2 and DATETIMEOFFSET
3 ноя 14, 23:03    [16794406]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить