计算查询行的最有效方法 [英] Most efficient way to count rows of a query
问题描述
我正在使用Hibernate来检索特定查询的行数.假设我有一个名为"Person"的表,其中包含各种列.这些列之一就是名称".
I'm using Hibernate to retrieve the number of rows for a specific query. Let's say I have a table called 'Person' with various columns. One of those columns is 'name'.
如果我想获得名为"Andrew"的人数,以下哪种方法最有效?假设其中一些/全部之间存在性能差异.使用Hibernate/SQL有更好的方法吗?
If I wanted to get the number of people with the name of 'Andrew', which of these ways would be most efficient? Assuming there is a performance difference between some/all of them. Is there a better way to do this using Hibernate/SQL?
(1)选择所有列
Query query = session.createQuery("from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();
(2)仅选择名称列
Query query = session.createQuery("select name from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();
(3)在查询中使用Count
(3) Using Count in the query
Query query = session.createQuery("select count(*) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();
(4)在查询中的名称列中使用Count
(4) Using Count with the name column in the query
Query query = session.createQuery("select count(name) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();
对不起,我的列表中有两个数字3.
Sorry, I had two number 3's in my list
推荐答案
如果只想计算行数,则不要检索结果集,这仅意味着无用的开销:
Don't retrieve a result set if you just want to count the number of rows, this just means useless overhead:
- 您将获得比实际需要更多的东西(无论您是选择所有列还是仅选择一个列)
- 您需要通过有线方式发送它们
- 您将无需创建实例(无论是完整的
Person
实体还是仅仅是String
).
- you'll get more stuff than actually wanted (whether you're selecting all columns or just one)
- you'll need to send them over the wire
- you'll need to create instances (whether it's a full
Person
entity or just aString
) for nothing.
换句话说,如果您只想计数,而不是在Java方面这样做,那么DBMS已针对此任务进行了优化,并且会做得更好.
In other words, if you only want to count, don't do it on the Java side, DBMS are optimized for this task and will do a much better job.
这不包括(1)和(2).
This excludes (1) and (2).
关于(3)和(4),请注意count(*)
和count(col)
通常有所不同:
Regarding (3) and (4), note that there is a difference between count(*)
and count(col)
in general:
-
count(*)
计算 ALL 行 -
count(col)
计算具有非空值为col
的行
count(*)
counts ALL rowscount(col)
counts rows with non-null values ofcol
因此,如果col
可以为NULL(count(*)
更快),它们将在性能和查询结果上给出不同的结果,否则性能相同.
So they will give different results in performance and query result if col
can be NULL (the count(*)
being faster), otherwise identical performance.
我会用(3).
- > COUNT(*)vs. COUNT(1)vs. COUNT (pk):哪个更好?
- count(*)vs count(column-name)-其中更正确?
- Count(*)vs Count(1)
- SQL查询:我应该使用哪一个? count(列名"),count(1)
- COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
- count(*) vs count(column-name) - which is more correct?
- Count(*) vs Count(1)
- SQL Query: Which one should i use? count("columnname"), count(1)
这篇关于计算查询行的最有效方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!