Henry Ing-Simmons
Reputation: 1501
Parameterised OFFSET FETCH NEXT query (EF Core) has 10x slower performance
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:
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:
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]
 FROM [Associates] AS [dto]
 ORDER BY [dto].[Forename], [dto].[Id]
 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]
 FROM [Associates] AS [dto]
 ORDER BY [dto].[Forename], [dto].[Id]
 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>
Upvotes: 6
Views: 1828
Answers (1)
Alberto Morillo
Reputation: 15648
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.
Upvotes: 1
Related Questions
- Entity Framework query slow, but same SQL in SqlQuery is fast
- Entity Framework Take statement, cause EF generates unefficient SQL query
- EF6 Entity framework sometimes slow query on SQL Server
- Entity Framework Core first query vs subsequent query performance
- EF Core Query Performance
- Azure SQL query performance significantly degrades when WHERE clause returns a high proportion of rows
- Azure SQL slow performance on parameterized queries NVARCHAR(4000)
- EF: Query quite slow on first execution
- Entity framework takes about 10 minutes to get data from Azure sql
- Entity Framework and inconsistent performance with processing the results of a query