Java – the most efficient way to calculate query rows
I'm using hibernate to retrieve the number of rows for a particular query Suppose I have a table named 'person', which contains various columns One column is' name '
If I want to get the number of people named "Andrew", which method is the most effective? Assume that some / all of them have performance differences Is there a better way to use hibernate / SQL?
(1) Select all columns
Query query = session.createQuery("from Person where name= :name");
query.setParameter("name",name);
List result = query.list();
int count = result.size();
(2) Select only name columns
Query query = session.createQuery("select name from Person where name= :name");
query.setParameter("name",name);
List result = query.list();
int count = result.size();
(3) Use count in query
Query query = session.createQuery("select count(*) from Person where name= :name");
query.setParameter("name",name);
long count = (Long) query.uniqueResult();
(4) Use the count and name columns in the query
Query query = session.createQuery("select count(name) from Person where name= :name");
query.setParameter("name",name);
long count = (Long) query.uniqueResult();
Editor: sorry, there are two numbers 3 in my list
Solution
If you only want to calculate the number of rows, do not retrieve the result set, which only means useless overhead:
>You'll get more than you really want (whether you select all columns or just one) > you need to send them over a wire > you need to create an instance (whether it's a complete person entity or just a string)
In other words, if you only want to calculate, not execute on the Java side, the DBMS optimizes this task and can do better
This excludes (1) and (2)
For (3) and (4), please note the difference between the general count (*) and the count (Col):
>Count (*) calculate all rows > count (Col) calculate rows whose col is a non null value
Therefore, if col can be null (count (*) is faster), they will give different results in performance and query results, otherwise the performance is the same
I use (3)
Similar problems
> COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? > count(*) vs count(column-name) – which is more correct? > Count(*) vs Count(1) > @L_ 419_ 3@