我有一个非常简单的查询,但是速度很慢。Entity Framework Profiler 显示它需要大约100毫秒。
dbContext.Users.Single(u => u.Id == userId);
尝试了一番后,我找到了一个非常相似但速度更快的查询(约为3毫秒)。
dbContext.Users.Where(u => u.Id == userId).ToList().Single();
当我比较这两个查询的SQL时,第二个查询没有使用嵌套SELECT和TOP操作。但仅仅因为这两点,我不会期望它会快30倍。同时,在使用SQL Server Management Studio执行这两个查询时,无法测量出任何差异。
当我查看执行计划时,它们都进行了聚集索引查找,其查询成本为100%。而额外的SELECT和TOP操作的查询成本为0%。 EFProfiler的查询计划也是如此,表明这不应该有任何区别。
在这种情况下,我该怎么做才能更好地理解查询性能呢?
下面是第一个查询的SQL结果。
SELECT [Limit1].[Id] AS [Id],
[Limit1].[EmailAddress] AS [EmailAddress],
[Limit1].[FirstName] AS [FirstName],
[Limit1].[LastName] AS [LastName]
FROM (SELECT TOP (2) [Extent1].[Id] AS [Id],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[Id] = 'b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */)
AND ('b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */ IS NOT NULL)) AS [Limit1]
以下是第二个(更快)查询的SQL代码。
SELECT [Extent1].[Id] AS [Id],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[Id] = 'b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */)
AND ('b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */ IS NOT NULL)
ToList()
调用。Single()
函数同样会对查询进行枚举,只需保留Where()
子句即可。 - Robert Koritnik