Parameterised OFFSET FETCH NEXT query (EF Core) has 10x slower performance

azure-sql-database entity-framework-core sql

Question

I'm running the following query through (generated by Entity Framework Core) Microsoft SQL Server Management Studio against a SQL Azure database table with ~46,000 rows which takes ~5seconds

DECLARE @__TypedProperty_0 as int = 20
DECLARE @__TypedProperty_1 as int = 20

SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email]
 FROM [Associates] AS [dto]
 ORDER BY [dto].[Forename], [dto].[Id]
 OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY

This results in the following execution plan:

Parameterised query execution plan

The same query, in-lining the parameters like below runs in 500ms - 10x faster!

SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email]
 FROM [Associates] AS [dto]
 ORDER BY [dto].[Forename], [dto].[Id]
 OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

But results in an almost identical execution plan:

Inline parameters execution plan

This code is generated by EntityFramework Core so I am not in control of it. My questions are:

  • why do these seemingly identical execution plans result in drastically different performance results
  • how can I improve the performance of the parameterised version of this query?

This table is created essentially like this (some columns omitted for brevity):

CREATE TABLE [dbo].[Associates](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AzureId] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [Forename] [nvarchar](max) NULL,
    [Surname] [nvarchar](max) NULL,
 CONSTRAINT [PK_Associates] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Edit:

Parameterised plan:

<?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.520" Build="15.0.300.379" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="3" StatementEstRows="100" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="5.37918" StatementText="SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email]&#xD; FROM [Associates] AS [dto]&#xD; ORDER BY [dto].[Forename], [dto].[Id]&#xD; OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY" StatementType="SELECT" QueryHash="0x0DE3D0FE5886DC7C" QueryPlanHash="0xF19DA08DF72AADE7" RetrievedFromCache="true" StatementSqlHandle="0x09005281339FAE104036AAAECEB2DCBF22BA0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="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 DegreeOfParallelism="0" NonParallelPlanReason="EstimatedDOPIsOne" MemoryGrant="242880" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="192">
            <Warnings>
              <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="242880" GrantedMemory="242880" MaxUsedMemory="8824" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="909960" RequiredMemory="512" DesiredMemory="909960" RequestedMemory="242880" GrantWaitTime="0" GrantedMemory="242880" MaxUsedMemory="8824" MaxQueryMemory="242888" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="17616030" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="849240" />
            <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="4860" WaitCount="63" />
              <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="2622" WaitCount="190" />
            </WaitStats>
            <QueryTimeStats CpuTime="293" ElapsedTime="5152" />
            <RelOp AvgRowSize="16117" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5.37918">
              <OutputList>
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5151" ActualCPUms="292" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <OffsetExpression>
                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@__TypedProperty_0],0)">
                    <Identifier>
                      <ColumnReference Column="ConstExpr1002">
                        <ScalarOperator>
                          <Convert DataType="bigint" Style="0" Implicit="true">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="@__TypedProperty_0" />
                              </Identifier>
                            </ScalarOperator>
                          </Convert>
                        </ScalarOperator>
                      </ColumnReference>
                    </Identifier>
                  </ScalarOperator>
                </OffsetExpression>
                <TopExpression>
                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@__TypedProperty_1],0)">
                    <Identifier>
                      <ColumnReference Column="ConstExpr1001">
                        <ScalarOperator>
                          <Convert DataType="bigint" Style="0" Implicit="true">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="@__TypedProperty_1" />
                              </Identifier>
                            </ScalarOperator>
                          </Convert>
                        </ScalarOperator>
                      </ColumnReference>
                    </Identifier>
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="16117" EstimateCPU="3.27909" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5.37917">
                  <OutputList>
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="40" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5151" ActualCPUms="292" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="242880" OutputMemoryGrant="242496" UsedMemoryGrant="8824" />
                  </RunTimeInformation>
                  <Sort Distinct="false">
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                      </OrderByColumn>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="16117" EstimateCPU="0.050878" EstimateIO="2.03794" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="46110" EstimatedRowsRead="46110" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.08882" TableCardinality="46110">
                      <OutputList>
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="46110" ActualRowsRead="46110" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="859" ActualCPUms="33" ActualScans="1" ActualLogicalReads="2759" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Index="[PK_Associates]" Alias="[dto]" IndexKind="Clustered" Storage="RowStore" />
                      </IndexScan>
                    </RelOp>
                  </Sort>
                </RelOp>
              </Top>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@__TypedProperty_0" ParameterDataType="int" ParameterRuntimeValue="(20)" />
              <ColumnReference Column="@__TypedProperty_1" ParameterDataType="int" ParameterRuntimeValue="(20)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Non parameterised plan:

<?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.520" Build="15.0.300.379" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="5.37917" StatementText="SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email]&#xD; FROM [Associates] AS [dto]&#xD; ORDER BY [dto].[Forename], [dto].[Id]&#xD; OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY" StatementType="SELECT" QueryHash="0x0DE3D0FE5886DC7C" QueryPlanHash="0x320ECFD7D3D25A6E" RetrievedFromCache="true" StatementSqlHandle="0x0900910E3823662F71FB79B11C319338FB890000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="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 DegreeOfParallelism="0" NonParallelPlanReason="EstimatedDOPIsOne" MemoryGrant="1024" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">
            <MemoryGrantInfo SerialRequiredMemory="336" SerialDesiredMemory="352" RequiredMemory="336" DesiredMemory="352" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="336" MaxQueryMemory="243624" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="17616030" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="848432" />
            <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="1713" WaitCount="23" />
              <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="1130" WaitCount="83" />
            </WaitStats>
            <QueryTimeStats CpuTime="96" ElapsedTime="1808" />
            <RelOp AvgRowSize="16117" EstimateCPU="4E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5.37917">
              <OutputList>
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1808" ActualCPUms="95" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <OffsetExpression>
                  <ScalarOperator ScalarString="(20)">
                    <Const ConstValue="(20)" />
                  </ScalarOperator>
                </OffsetExpression>
                <TopExpression>
                  <ScalarOperator ScalarString="(20)">
                    <Const ConstValue="(20)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="16117" EstimateCPU="3.27909" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="TopN Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5.37917">
                  <OutputList>
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                    <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="40" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1808" ActualCPUms="95" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="336" />
                  </RunTimeInformation>
                  <TopSort Distinct="false" Rows="40">
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                      </OrderByColumn>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="16117" EstimateCPU="0.050878" EstimateIO="2.03794" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="46110" EstimatedRowsRead="46110" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.08882" TableCardinality="46110">
                      <OutputList>
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                        <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="46110" ActualRowsRead="46110" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1009" ActualCPUms="31" ActualScans="1" ActualLogicalReads="2759" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="AzureId" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Email" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Forename" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Alias="[dto]" Column="Surname" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[MomentaUAT]" Schema="[dbo]" Table="[Associates]" Index="[PK_Associates]" Alias="[dto]" IndexKind="Clustered" Storage="RowStore" />
                      </IndexScan>
                    </RelOp>
                  </TopSort>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
1
6
4/16/2018 2:58:06 PM

Accepted Answer

Please add an index that supports the ORDER BY ([dto].[Forename], [dto].[Id])and that covers all of the columns in the SELECT clause.

CREATE NONCLUSTERED INDEX IX_Associates_Forename_ID  
ON Associates (Forename, Id)  
INCLUDE (Surname, AzureId, Email);

As you can see in the plan, doing pagination without proper index originates scans or key lookups and that is the reason behind poor performance.

1
4/17/2018 7:50:15 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow