Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difference Between count() and size

 
Jarrod Felino
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One Student entity can have many Presentation entity, which is held as a Collection. What I need to know if there is any difference between:

select count(s.presentations) from Student s

select size(s.presentations) from Student s
 
Amandeep Singh
Ranch Hand
Posts: 850
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SIZE- Arithmetic Functions
The SIZE function returns an integer value, the number of elements of the collection. If the collection is
empty, the SIZE function evaluates to zero.

Syntax for Size-
SIZE(collection_valued_path_expression)

COUNT- Aggregate function
The following aggregate functions can be used in the SELECT clause of a query: AVG, COUNT, MAX,
MIN, SUM. the path expression that is the argument to the aggregate
function must terminate in a state-field. The path expression argument to COUNT may terminate in
either a state-field or a association-field, or the argument to COUNT may be an identification variable.

Syntax for Count-

COUNT ([DISTINCT] identification_variable | state_field_path_expression |
single_valued_association_path_expression)
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Personally, i would suggest the use of count() instead of size.
Because count() will get the count while querying the database,
on the other hand, size() will load the entities and then return their size, because there is no such thing as size in the db.
since you only need the number of rows, better get them from the db instead of getting from java.

 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rahul Babbar wrote:better get them from the db instead of getting from java.

The SIZE function is not used to get anything from java. The persistence provider will turn the JPQL in a native query, whether you use COUNT or SIZE. SIZE is a function used in conditions, so it's different from COUNT.
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chris,

What i meant was if we use size(), then the persistence provider 'may'(not sure) load all the records and then return their size. which will not be good, since it'll affect performance.
If the persistence provider converts it into appropriate count query and executes on the db(as you said), it should be fine.

Thanks for the clarification..
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the misunderstanding came from the first post. There's no such JPQL like "select size(s.presentations) from Student s". The size function is used in conditions. It's a handy function which avoids to type in subqueries. For example the following :

could be written like :
 
Amandeep Singh
Ranch Hand
Posts: 850
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the same thing in my explanation but without example. :idea:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic