哪一个 NHibernate / SQL“存在”？查询效率更高（以及如何知道）？
我一直在分析 NHibernate 为以下片段生成的 SQL 查询。我认为代码 #1 会更有效，但是生成的 SQL 让我怀疑。
1) bool any = Query.Where(user => user.Id == 1).Any(); 生成：
select
*
from
users
where exists
(select id
from users
where id = 1)
and rownum <= 1
2) bool any = Query.Where(user =>; user.Id == 1).Count() > 1; 生成：
select
count(*)
from
users
where
id = 1
哪个更有效率？未来我将如何确定这一点？我正在使用甲骨文。
I have been profiling the SQL query NHibernate generates for the following snippets below. I would think code #1 would be more efficient, however, the generated SQL gives me doubts.
1) bool any = Query.Where(user => user.Id == 1).Any(); generates:
select
*
from
users
where exists
(select id
from users
where id = 1)
and rownum <= 1
2) bool any = Query.Where(user => user.Id == 1).Count() > 1; generates:
select
count(*)
from
users
where
id = 1
Which is more efficient? In the future, how would I determine this? I am using Oracle.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
假设
ID是主键（或者至少是唯一的），第二个查询可能会稍微高效一些，因为它只需要读取ID列上的索引USERS表。第一个查询需要执行相同的工作来读取索引，但随后必须从表中获取相应的数据。如果查看两个查询计划，您应该会看到第一个查询对表进行了额外的读取。另一方面，如果您正在搜索没有唯一索引的列，则第二个查询的效率较低，因为它可能必须从索引中读取多个块或对表进行完整扫描才能获取第一个查询一旦找到第一个匹配行就可以停止处理的计数。
Assuming that
IDis the primary key (or at least unique), the second query may be slightly more efficient because it only needs to read the index on theIDcolumn of theUSERStable. The first query will need to the same work to read the index but then will have to fetch the corresponding data from the table. If you look at the two query plans, you should see that the first query does an extra read of the table.On the other hand, if you were searching on a column that did not have a unique index on it, the second query be less efficient because it potentially has to read multiple blocks from the index or to do a full scan of the table to get a count while the first query can stop processing once it finds the first matching row.