计算查询行的最有效方法 [英] 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 a String) 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 rows
• count(col) counts rows with non-null values of col

因此，如果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屋！