sql - शीर्ष क्यों चुनें... अनुक्रमित स्तंभ द्वारा क्रम अभी भी क्रमबद्ध है?



sql-server tsql (1)

आपकी तालिका बी द्वारा विभाजित है

सूचकांक इस विभाजन योजना को तब तक विरासत में लेता है जब तक आप अन्यथा निर्दिष्ट नहीं करते। साथ उदाहरण के लिए

create unique index ix_ on T (A, B)  with (data_compression = page) on [primary] 

(जो मामले में यह गैर-गठबंधन हो जाता है और मेटाडेटा जैसे कुछ कार्रवाइयां रोकती हैं)

सबसे कम "ए" मान किसी भी विभाजन में हो सकता है।

यह बहुत अच्छी तरह से अनुकूलित नहीं है आप संरेखित इंडेक्स को रख सकते हैं और कोड के आधार पर इस रीराइट का उपयोग कर सकते हैं

SELECT TOP 20 A, B 
FROM sys.partitions AS P
  CROSS APPLY ( SELECT TOP 20 A, B 
                FROM dbo.T
                WHERE $PARTITION.YourPartitionFunction(T.B) = P.partition_number 
                ORDER BY A,B
                ) AS A
WHERE P.object_id = OBJECT_ID('dbo.T')
AND P.index_id = INDEXPROPERTY( OBJECT_ID('dbo.T'), 'ix_', 'IndexID' )
ORDER BY  A,B

इसे 41 विभाजनों में से प्रत्येक के शीर्ष 20 पंक्तियां मिलेंगी (बिना किसी क्रम के), तो सिर्फ 820 पंक्तियों को ठीक करें, जो कि अंतिम शीर्ष 20 (पूरे 42 मिलियन के बजाय) पाने के लिए होता है।

मैंने चयनित शीर्ष कथन को कवर करने के लिए निम्न सूचकांक बनाया है।

-- Column A, B have type of int
create unique index ix_ on T (A, B) with (data_compression = page) 
-- tried to create non-unique index too and the execution plan is the same

select top 20 A, B from T order by A, B -- 19 seconds
select top 20 A, B from T -- return result instantly

हालांकि, यह अभी भी कुछ समय लेता है (मेरे टेबल पर 1 9 सेकंड जो 50 मिलियन पंक्तियाँ हैं) और निष्पादन योजना से पता चलता है कि अभी भी एक "सॉर्ट" है?

निष्पादन योजना से पता चलता है

← मूल्य (मूल्य: 0%) ← शीर्ष (लागत: 0%) ← समानता (इकट्ठा होने वाली धारा) (लागत: 0%) ← सॉर्ट करें (टॉप एन सॉर्ट) लागत: 93% ← इंडेक्स स्कैन (नॉनक्लास्टर्ड) [टी.िक्स_] लागत : 7%

निष्पादन योजना

<?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="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="552.009" StatementText="select  top 20 A A, B B --, checksum(*) cs&#xA;from T with (index(ix_))&#xA;order by A, B" StatementType="SELECT" QueryHash="0x1531573504856080" QueryPlanHash="0x5D4FED760C34AF43" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="8" MemoryGrant="1024" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="256">
            <ThreadStat Branches="1" UsedThreads="8">
              <ThreadReservation NodeId="0" ReservedThreads="8" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="896" DesiredMemory="960" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="896" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="768000" EstimatedPagesCached="768000" EstimatedAvailableDegreeOfParallelism="8" />
            <RelOp AvgRowSize="15" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="552.009">
              <OutputList>
                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="(20)">
                    <Const ConstValue="(20)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="15" EstimateCPU="0.0286101" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="552.009">
                  <OutputList>
                    <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                    <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                      </OrderByColumn>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="15" EstimateCPU="212.739" EstimateIO="303.269" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="551.98">
                      <OutputList>
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="20" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <TopSort Distinct="false" Rows="20">
                        <OrderBy>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                          </OrderByColumn>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                          </OrderByColumn>
                        </OrderBy>
                        <RelOp AvgRowSize="15" EstimateCPU="5.81245" EstimateIO="30.16" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="42226500" LogicalOp="Index Scan" NodeId="3" Parallel="true" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="35.9724" TableCardinality="42226500">
                          <OutputList>
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="8" ActualRows="3993270" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="7" ActualRows="2713924" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="6" ActualRows="8866373" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="5" ActualRows="10625143" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="4" ActualRows="4254726" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="3" ActualRows="3195887" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="3626671" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="4950538" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <RunTimePartitionSummary>
                            <PartitionsAccessed PartitionCount="41">
                              <PartitionRange Start="1" End="41" />
                            </PartitionsAccessed>
                          </RunTimePartitionSummary>
                          <IndexScan Ordered="false" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[DB]" Schema="[dbo]" Table="[T]" Index="[ix_]" Alias="[T]" IndexKind="NonClustered" Storage="RowStore" />
                          </IndexScan>
                        </RelOp>
                      </TopSort>
                    </RelOp>
                  </Parallelism>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>




sql-server-2014