sql-server - SQL Server 查询计划，运行时间较低，但子树成本较高-6ren

sql-server - SQL Server 查询计划，运行时间较低，但子树成本较高

转载作者：行者123 更新时间：2023-12-04 02:51:25

为什么下面两个查询中第一个的执行时间较低，而估计的子树成本较高？

估计子树成本 只是结合其他查询性能指标(如响应时间)来衡量查询计划性能的指南，但令我惊讶的是逆向并排比较两个相似查询时成本和时间之间的关系。

我创建了一个示例数据库来说明这个问题，并包含了两个查询的结果 XML 查询计划。我意识到 StateID 作为外键存在于 County 和 Town 表中是一种错误的形式，但对我来说这是说明这种现象的最简单方法。

我不只是问“哪个更重要，子树成本或响应时间”。我想了解两个相似查询的查询计划子树成本和响应时间之间如何存在反比关系，以便我可以做出更明智的决策。

请注意，下面的两个查询产生完全相同的结果集。

谢谢!

CREATE TABLE dbo.County (StateID INT, CountyID INT)
CREATE TABLE dbo.Town (StateID INT, CountyID INT, TownID INT)

DECLARE @State TABLE (StateID INT, PRIMARY KEY CLUSTERED (StateID))
DECLARE @County TABLE (CountyID INT, PRIMARY KEY CLUSTERED (CountyID))

INSERT INTO @State
VALUES (27)

INSERT INTO @County
SELECT DISTINCT
    c.CountyID
FROM dbo.County c
JOIN @State s ON c.StateID = s.StateID

-- low time, high cost query
SELECT DISTINCT
    t.CountyID,
    t.TownID
FROM dbo.Town t
JOIN @State s
    ON t.StateID = s.StateID

-- low cost, high time query
SELECT DISTINCT
    t.CountyID,
    t.TownID
FROM dbo.Town t
JOIN @State s
    ON t.StateID = s.StateID
JOIN @County c
    ON t.CountyID = c.CountyID

以下查询计划适用于估计子树成本为 6.06 且耗时为 1114 毫秒的查询。

<StmtSimple StatementCompId="6" StatementEstRows="273495" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="6.06304" StatementText="SELECT DISTINCT&#xD;&#xA;&#x9;t.CountyID,&#xD;&#xA;&#x9;t.TownID&#xD;&#xA;FROM dbo.Town t&#xD;&#xA;JOIN @State s&#xD;&#xA;&#x9;ON t.StateID = s.StateID&#xD;&#xA;&#xD;&#xA;-- low cost, high time query&#xD;" StatementType="SELECT" QueryHash="0x9347C19165C31DBF" QueryPlanHash="0x98EE57C66D8B347A">
          <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="2" MemoryGrant="28144" CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="312">
            <RelOp AvgRowSize="15" EstimateCPU="0.388488" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="6.06304">
              <OutputList>
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="13027" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Parallelism>
                <RelOp AvgRowSize="15" EstimateCPU="3.79725" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Aggregate" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5.67455">
                  <OutputList>
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OutputList>
                  <MemoryFractions Input="0.998723" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="2" ActualRows="6515" ActualEndOfScans="1" ActualExecutions="1" />
                    <RunTimeCountersPerThread Thread="1" ActualRows="6512" ActualEndOfScans="1" ActualExecutions="1" />
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                  </RunTimeInformation>
                  <Hash>
                    <DefinedTowns />
                    <HashKeysBuild>
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                    </HashKeysBuild>
                    <BuildResidual>
                      <ScalarOperator ScalarString="[Locality].[dbo].[Town].[CountyID] as [t].[CountyID] = [Locality].[dbo].[Town].[CountyID] as [t].[CountyID] AND [Locality].[dbo].[Town].[TownID] as [t].[TownID] = [Locality].[dbo].[Town].[TownID] as [t].[TownID]">
                        <Logical Operation="AND">
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Logical>
                      </ScalarOperator>
                    </BuildResidual>
                    <RelOp AvgRowSize="15" EstimateCPU="0.494228" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.8773">
                      <OutputList>
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="2" ActualRows="1017328" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="1" ActualRows="1093191" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="15" EstimateCPU="0.571604" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.38307">
                          <OutputList>
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="true">
                            <OuterReferences>
                              <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                            </OuterReferences>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0285019" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.031785">
                              <OutputList>
                                <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <Parallelism PartitioningType="RoundRobin">
                                <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                  <OutputList>
                                    <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedTowns>
                                      <DefinedTown>
                                        <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                      </DefinedTown>
                                    </DefinedTowns>
                                    <Object Table="[@State]" Index="[PK__#3AB788A__C3BA3B5A3C9FD11A]" Alias="[s]" />
                                  </IndexScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                            <RelOp AvgRowSize="15" EstimateCPU="0.301001" EstimateIO="0.478681" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Index Seek" NodeId="7" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.779682" TableCardinality="6291320">
                              <OutputList>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                <DefinedTowns>
                                  <DefinedTown>
                                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                  </DefinedTown>
                                  <DefinedTown>
                                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                  </DefinedTown>
                                </DefinedTowns>
                                <Object Database="[Locality]" Schema="[dbo]" Table="[Town]" Index="[NCX_Town_StateID_CountyID_TownID]" Alias="[t]" IndexKind="NonClustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="@State.[StateID] as [s].[StateID]">
                                            <Identifier>
                                              <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>

以下查询计划适用于估计子树成本为 0.14 且耗时为 4614 毫秒的查询。

<StmtSimple StatementCompId="7" StatementEstRows="339.324" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.142839" StatementText="SELECT DISTINCT&#xD;&#xA;&#x9;t.CountyID,&#xD;&#xA;&#x9;t.TownID&#xD;&#xA;FROM dbo.Town t&#xD;&#xA;JOIN @State s&#xD;&#xA;&#x9;ON t.StateID = s.StateID&#xD;&#xA;JOIN @County c&#xD;&#xA;&#x9;ON t.CountyID = c.CountyID&#xD;&#xA;&#xD;" StatementType="SELECT" QueryHash="0xF7B13FA8059B141C" QueryPlanHash="0x31CEE9DAF12E77A5">
          <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="1584" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="376">
            <RelOp AvgRowSize="15" EstimateCPU="0.00454996" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="339.324" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.142839">
              <OutputList>
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="13027" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Sort Distinct="true">
                <OrderBy>
                  <OrderByColumn Ascending="true">
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                  </OrderByColumn>
                  <OrderByColumn Ascending="true">
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OrderByColumn>
                </OrderBy>
                <RelOp AvgRowSize="15" EstimateCPU="0.0571741" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="339.324" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.127028">
                  <OutputList>
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="0" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Hash>
                    <DefinedTowns />
                    <HashKeysBuild>
                      <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                    </HashKeysBuild>
                    <HashKeysProbe>
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                    </HashKeysProbe>
                    <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                      <OutputList>
                        <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedTowns>
                          <DefinedTown>
                            <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                          </DefinedTown>
                        </DefinedTowns>
                        <Object Table="[@State]" Index="[PK__#3AB788A__C3BA3B5A3C9FD11A]" Alias="[s]" />
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="19" EstimateCPU="0.0357739" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8558.34" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.066568">
                      <OutputList>
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false">
                        <OuterReferences>
                          <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                        </OuterReferences>
                        <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                          <OutputList>
                            <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="137" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                            <DefinedTowns>
                              <DefinedTown>
                                <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                              </DefinedTown>
                            </DefinedTowns>
                            <Object Table="[@County]" Index="[PK__#3E88198__B68F9DF7407061FE]" Alias="[c]" />
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="19" EstimateCPU="0.00957118" EstimateIO="0.0179398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8558.34" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.027511" TableCardinality="6291320">
                          <OutputList>
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="137" ActualExecutions="137" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedTowns>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                              </DefinedTown>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                              </DefinedTown>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                              </DefinedTown>
                            </DefinedTowns>
                            <Object Database="[Locality]" Schema="[dbo]" Table="[Town]" Index="[NCX_Town_CountyID_inc_StateID_TownID]" Alias="[t]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="@County.[CountyID] as [c].[CountyID]">
                                        <Identifier>
                                          <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Sort>
            </RelOp>
          </QueryPlan>
        </StmtSimple>

最佳答案

虽然我没有深入研究您的查询计划，但我有一些建议和建议的解释。建议很简单:不要对行数超过两个数量级(100 行)的表使用表变量。可能的解释是它们的性能可能非常差，生成的查询计划可能不准确或效率低下，per Microsoft's article on table data types :

Best practices

Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data. Consider rewriting such queries to use temporary tables or use the USE PLAN query hint to ensure the optimizer uses an existing query plan that works well for your scenario.

和:

Limitations and Restrictions

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

...

在实践中，我发现使用表变量会妨碍并行查询计划的生成，并且会损害除最简单、最不复杂的表变量以外的所有表变量的性能。

相反，我发现几乎在所有情况下临时表的性能都更高，只要您小心及时清理自己(或使用短期 session 以允许服务器清除临时表) 然后你会得到更好的性能。

至于为什么第二个查询计划成本较低但执行时间较长？很可能有很多很多县(1000s？10000s？)并且正在生成的查询计划假设有少量(100s)。查询优化器认为@County 可能少于 100 行，因此它可能认为结果集将非常具体，因为城镇列表与 <100 个条目的一个列表连接，然后与第二个 <100 条目的列表连接，很可能少于 100 行。

实际上，每个城镇都有一个县，因此连接不是很有选择性，并且使用的连接类型选择不佳。在每种情况下，嵌套循环都用于最后一个连接，但嵌套循环连接对于大型排序数据来说效率极低。这可能是问题的症结所在。

使用临时表而不是表变量重新运行您的查询，我猜您会得到具有非常不同的性能特征的非常不同的查询计划。

关于sql-server - SQL Server 查询计划，运行时间较低，但子树成本较高，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17580830/

文章推荐： parentheses - 检查括号是否平衡 - 没有堆栈

文章推荐： c# - 使 UWP 颜色选择器更小或更大

sql - SQL、PL-SQL 和 T-SQL 之间有什么区别？
SQL、PL-SQL 和 T-SQL 之间有什么区别？谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？最佳答案 SQL 是一种对集合进行操作的查询语言。它或多或少是标准化的，几乎所有关
sql - T-SQL、SQL Server 和 SQL 有什么区别
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。我对 SQL 的了解足以完成我的
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任
sql - 在字符串中查找第二组数字(SQL/PL-SQL)
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
sql - 服务器端 sql 与客户端 sql
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
sql - sql 如何计算 sql 存储过程中的附加表？
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。有什么方
sql - 嵌入式 SQL 与动态 SQL
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。我自己做了一些数据库工作
sql - SQL Server SQL 语句可以有多少个字符？
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
sql-server - SQL 行到列 sql
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
sql - SQL Server SQL 语句中的动态日期
预先感谢您对此提供的任何帮助。假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
sql - (SQL Server) SQL 不允许在检查过程是否存在后创建过程
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
sql - exec sp_executesql @sql 和 exec (@sql) SQL Server
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
sql - 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？最佳答案 sp_executes
c# - SQL > Linq to Sql，SQL 查询有效，Linq to SQL 返回空数据集
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
sql-server - "> sql.txt && sql -h-1 -i sql.txt && del sql.txt"命令是什么意思？
我正在调试一些构建成功运行的 SQL 命令的代码。然而，在查询结束时，查询结果似乎被写入了一个文本文件。完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
sql - 如何从 MS SQL 数据库(Microsoft SQL Server)中的其他 .sql 文件运行 .sql 文件？
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
sql - 如何在查询中使用 SQL 变量(SQL Server)？
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql-server - SQL Server 查询计划，运行时间较低，但子树成本较高

Best practices

Limitations and Restrictions