实体框架超时，但来自分析程序的查询在几毫秒内执行
本文关键字：几毫 执行 查询 超时 框架 程序 实体 | 更新日期: 2023-09-27 18:06:41
以下代码在60秒后超时。对于只读目的，它应该返回10K行:
using (var db = new TUdvEntities(_connectionString))
{
try
{
db.Set<TEjendom>().AsNoTracking();
db.Set<TEjd_ESR>().AsNoTracking();
db.Set<TMat>().AsNoTracking();
IQueryable<TEjendom> query = db.TEjendom;
foreach (var propertyId in propertiesInProgress)
{
query = query.Where(x => x.EjdId != propertyId);
}
var description = propertyState.GetDescription();
var resultListTmp = query.Where(x => x.EjdStatus == description)
.Include(nameof(TEjd_ESR))
.Include(nameof(TMat))
.Take(amount).ToList();
....
}
}
然而，EF生成的查询(我从SQL Server Profiler中拾取的)在一毫秒内执行。
我已经尝试使用以下命令禁用更改跟踪:
db.Configuration.AutoDetectChangesEnabled = false;
但这没有帮助。
两个include的组合会减慢查询速度，但是当我删除其中一个时，它会立即返回结果。
SQL查询:exec sp_executesql N'SELECT
[UnionAll1].[EjdId] AS [C1],
[UnionAll1].[EjdId1] AS [C2],
[UnionAll1].[EjdType] AS [C3],
[UnionAll1].[BNummer] AS [C4],
[UnionAll1].[TNummer] AS [C5],
[UnionAll1].[ANummer] AS [C6],
[UnionAll1].[ENummer] AS [C7],
[UnionAll1].[Beskrivelse] AS [C8],
[UnionAll1].[SBT] AS [C9],
[UnionAll1].[EjdStatus] AS [C10],
[UnionAll1].[StatusTimestamp] AS [C11],
[UnionAll1].[IAbo] AS [C12],
[UnionAll1].[AOAttempts] AS [C13],
[UnionAll1].[AboId] AS [C14],
[UnionAll1].[BFE] AS [C15],
[UnionAll1].[UpdateToken] AS [C16],
[UnionAll1].[FEJ] AS [C17],
[UnionAll1].[C1] AS [C18],
[UnionAll1].[ESRId] AS [C19],
[UnionAll1].[EjdId2] AS [C20],
[UnionAll1].[Passiv] AS [C21],
[UnionAll1].[EjdId3] AS [C22],
[UnionAll1].[C2] AS [C23],
[UnionAll1].[C3] AS [C24],
[UnionAll1].[C4] AS [C25],
[UnionAll1].[C5] AS [C26]
FROM (SELECT
CASE WHEN ([Extent2].[ESRId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Limit1].[EjdId] AS [EjdId],
[Limit1].[EjdId] AS [EjdId1],
[Limit1].[EjdType] AS [EjdType],
[Limit1].[BNummer] AS [BNummer],
[Limit1].[TNummer] AS [TNummer],
[Limit1].[ANummer] AS [ANummer],
[Limit1].[ENummer] AS [ENummer],
[Limit1].[Beskrivelse] AS [Beskrivelse],
[Limit1].[SBT] AS [SBT],
[Limit1].[EjdStatus] AS [EjdStatus],
[Limit1].[StatusTimestamp] AS [StatusTimestamp],
[Limit1].[IAbo] AS [IAbo],
[Limit1].[AOAttempts] AS [AOAttempts],
[Limit1].[AboId] AS [AboId],
[Limit1].[BFE] AS [BFE],
[Limit1].[UpdateToken] AS [UpdateToken],
[Limit1].[FEJ] AS [FEJ],
[Extent2].[ESRId] AS [ESRId],
[Extent2].[EjdId] AS [EjdId2],
[Extent2].[Passiv] AS [Passiv],
[Extent2].[EjdId] AS [EjdId3],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5]
FROM (SELECT TOP (10416)
[Extent1].[EjdId] AS [EjdId],
[Extent1].[EjdType] AS [EjdType],
[Extent1].[BNummer] AS [BNummer],
[Extent1].[TNummer] AS [TNummer],
[Extent1].[ANummer] AS [ANummer],
[Extent1].[ENummer] AS [ENummer],
[Extent1].[Beskrivelse] AS [Beskrivelse],
[Extent1].[SBT] AS [SBT],
[Extent1].[EjdStatus] AS [EjdStatus],
[Extent1].[StatusTimestamp] AS [StatusTimestamp],
[Extent1].[IAbo] AS [IAbo],
[Extent1].[AOAttempts] AS [AOAttempts],
[Extent1].[AboId] AS [AboId],
[Extent1].[BFE] AS [BFE],
[Extent1].[UpdateToken] AS [UpdateToken],
[Extent1].[FEJ] AS [FEJ]
FROM [dbo].[TEjendom] AS [Extent1]
WHERE ([Extent1].[EjdStatus] = @p__linq__0) OR (([Extent1].[EjdStatus] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [Limit1]
LEFT OUTER JOIN [dbo].[TEjd_ESR] AS [Extent2] ON [Limit1].[EjdId] = [Extent2].[EjdId]
UNION ALL
SELECT
2 AS [C1],
[Limit2].[EjdId] AS [EjdId],
[Limit2].[EjdId] AS [EjdId1],
[Limit2].[EjdType] AS [EjdType],
[Limit2].[BNummer] AS [BNummer],
[Limit2].[TNummer] AS [TNummer],
[Limit2].[ANummer] AS [ANummer],
[Limit2].[ENummer] AS [ENummer],
[Limit2].[Beskrivelse] AS [Beskrivelse],
[Limit2].[SBT] AS [SBT],
[Limit2].[EjdStatus] AS [EjdStatus],
[Limit2].[StatusTimestamp] AS [StatusTimestamp],
[Limit2].[IAbo] AS [IAbo],
[Limit2].[AOAttempts] AS [AOAttempts],
[Limit2].[AboId] AS [AboId],
[Limit2].[BFE] AS [BFE],
[Limit2].[UpdateToken] AS [UpdateToken],
[Limit2].[FEJ] AS [FEJ],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS bit) AS [C4],
CAST(NULL AS int) AS [C5],
[Extent4].[EjdId] AS [EjdId2],
[Extent4].[LeKode] AS [LeKode],
[Extent4].[MatNummer] AS [MatNummer],
[Extent4].[EjdId] AS [EjdId3]
FROM (SELECT TOP (10416)
[Extent3].[EjdId] AS [EjdId],
[Extent3].[EjdType] AS [EjdType],
[Extent3].[BNummer] AS [BNummer],
[Extent3].[TNummer] AS [TNummer],
[Extent3].[ANummer] AS [ANummer],
[Extent3].[ENummer] AS [ENummer],
[Extent3].[Beskrivelse] AS [Beskrivelse],
[Extent3].[SBT] AS [SBT],
[Extent3].[EjdStatus] AS [EjdStatus],
[Extent3].[StatusTimestamp] AS [StatusTimestamp],
[Extent3].[IAbo] AS [IAbo],
[Extent3].[AOAttempts] AS [AOAttempts],
[Extent3].[AboId] AS [AboId],
[Extent3].[BFE] AS [BFE],
[Extent3].[UpdateToken] AS [UpdateToken],
[Extent3].[FEJ] AS [FEJ]
FROM [dbo].[TEjendom] AS [Extent3]
WHERE ([Extent3].[EjdStatus] = @p__linq__0) OR (([Extent3].[EjdStatus] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [Limit2]
INNER JOIN [dbo].[TMat] AS [Extent4] ON [Limit2].[EjdId] = [Extent4].[EjdId]) AS [UnionAll1]
ORDER BY [UnionAll1].[EjdId1] ASC, [UnionAll1].[C1] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'HentData'
有什么建议吗?
我同意Allan S. Hansen关于参数嗅探的评论。
您的查询部分包含WHERE ([Extent1].[EjdStatus] = @p__linq__0)，并且@p__linq__0参数在最后的查询中作为nvarchar(4000)传入:
ORDER BY [UnionAll1]。[EjdId1] ASC， [UnionAll1]。(C1) ASC ' N ' @p__linq__0 nvarchar (4000) , @p__linq__0 = N 'HentData
我猜EjdStatus列在数据库中没有定义为nvarchar(4000)。我认为这会使数据库选择错误的执行计划或索引。我们遇到了这个问题，我们所做的就是确保EF查询使用正确的数据类型，这有很大帮助。
我们通过在我们的数据上下文中调用OnModelCreating覆盖中的HasDataType方法来做到这一点:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<MyClass>().Property(a => a.EjdStatus).HasColumnType("VARCHAR");
//other code here
}
这导致EF在它生成的查询中使用正确的数据类型，从而允许Sql选择适当的执行计划。