gpt4 book ai didi

sql - 参数化的 OFFSET FETCH NEXT 查询 (EF Core) 的性能降低了 10 倍

转载 作者:行者123 更新时间:2023-12-04 14:39:41 29 4
gpt4 key购买 nike

我正在通过(由 Entity Framework Core 生成)Microsoft SQL Server Management Studio 针对具有约 46,000 行的 SQL Azure 数据库表运行以下查询,这需要约 5 秒

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

这导致以下执行计划:

Parameterised query execution plan

相同的查询,内联如下参数,运行时间为 500 毫秒 - 快 10 倍!
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

但导致几乎相同的执行计划:

Inline parameters execution plan

此代码由 EntityFramework Core 生成,因此我无法控制它。我的问题是:
  • 为什么这些看似相同的执行计划会导致截然不同的性能结果
  • 如何提高此查询的参数化版本的性能?


  • 该表的创建基本上是这样的(为简洁起见,省略了一些列):
    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)
    )

    编辑:

    参数化计划:
    <?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>

    非参数化计划:
    <?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>

    最佳答案

    请添加一个支持 ORDER BY ([dto].[Forename], [dto].[Id]) 并且涵盖 SELECT 子句中所有列的索引。

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

    正如您在计划中所看到的,在没有适当索引的情况下进行分页会导致扫描或键查找,这就是性能不佳的原因。

    关于sql - 参数化的 OFFSET FETCH NEXT 查询 (EF Core) 的性能降低了 10 倍,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49859193/

    29 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com