比较实体框架5中Any()与Count()生成的查询的性能
本文关键字:实体 查询 性能 Count Any 框架 比较 | 更新日期: 2023-09-27 18:25:17
在我的项目中,我使用实体框架4.4.0.0,我面临以下困境。我必须检查用户是否已激活。我的查询看起来像:
Any()
_context.Users.Any(u => u.Id == userId && u.IsActivated);
生成的sql是:
SELECT CASE
WHEN ( EXISTS (SELECT 1 AS [C1]
FROM [dbo].[Users] AS [Extent1]
WHERE ( [Extent1].[Id] = @p__linq__0 )
AND ( [Extent1].[IsActivated] = 1 )) ) THEN cast(1 AS BIT)
WHEN ( NOT EXISTS (SELECT 1 AS [C1]
FROM [dbo].[Users] AS [Extent2]
WHERE ( [Extent2].[Id] = @p__linq__0 )
AND ( [Extent2].[IsActivated] = 1 )) ) THEN cast(0 AS BIT)
END AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable1]
对于Count()
,我得到以下查询:
SELECT [GroupBy1].[A1] AS [C1]
FROM (SELECT COUNT(1) AS [A1]
FROM [dbo].[Users] AS [Extent1]
WHERE ( [Extent1].[Id] = @p__linq__0 )
AND ( [Extent1].[IsActivated] = 1 )) AS [GroupBy1]
这看起来对吗?我不是很擅长sql。。。但在我看来效率不高。我是不是遗漏了什么?
"select count(*) from dbo.Users where id=@id and IsActivated=1
"是否效率较低?
这取决于情况。
EXISTS
的实现也不是很好。如果有0
行,它将执行两次检查。在这种情况下,COUNT
会更好,因为它只需要搜索不存在的行并计数一次。
你可能会发现检查
_context.Users
.Where(u => u.Id == userId && u.IsActivated)
.Select(u=> true)
.FirstOrDefault();
给出了一个比两者都好的计划(根据卢克的建议进行了修改)。在EF4上进行测试,生成的查询与一致
SELECT TOP (1) cast(1 AS BIT) AS [C1]
FROM Users
WHERE userId = @userId
AND IsActivated = 1
这意味着,如果存在多个行,它不会处理不必要的额外行,并且只执行一次与WHERE
匹配的行的搜索。