Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

sql

azure-sql-database

entity-framework-core

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>
like image 906
Henry Ing-Simmons Avatar asked Apr 16 '18 14:04

Henry Ing-Simmons

People also ask

What is Cartesian explosion?

If a typical blog has multiple related posts, rows for these posts will duplicate the blog's information. This duplication leads to the so-called "cartesian explosion" problem. As more one-to-many relationships are loaded, the amount of duplicated data may grow and adversely affect the performance of your application.

What is offset fetch in SQL?

OFFSET and FETCH Clause are used in conjunction with SELECT and ORDER BY clause to provide a means to retrieve a range of records. OFFSET. The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records.

1 Answers

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.

like image 60
Alberto Morillo Avatar answered Oct 05 '22 11:10

Alberto Morillo
Sign in to Comment

Related questions

Get results by count and operators in one query
Workflow for adding new columns from Pandas to SQLite tables
Syntax for identity-insert FROM with no columns to insert?
Not getting ORA-25156 "old style outer join (+) cannot be used with ANSI join" when I should be
Update database tables
Is that possible to use full text index to find closest match strings? What does Statistical Semantics do in Full Text Indexing
sqlquery in R does not return all rows from query
Convert Horizontal CSV Template to a Tabular Format
INSERT into unique column same value from two sessions (Oracle)
Need help to improve MYSQL SubQuery Performance
How to count all the connected nodes (rows) in a graph on Postgres?
Postgres query running very slow
how to restore mysql backup that have generated always as column?
Is it possible to pass a DataTable to an ad-hoc sql query in Entity Framework?
MDX Doesn't show last version of data
Can aggregate filter expressions not use indices?
Desired port for google cloudSQL connection is not able to be used
Table-Valued Parameter without using a stored procedure
Why doesn't Oracle throw an "ambiguous column reference" here?
Using SSIS and SSMS - Attempting to create an ETL design document that details source and destination columns and their relations

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!