Which approach to use to check if an aggregate returns X number of records

In the Coding Analysis article, it stated that

  1. Use list empty instead of count for aggregate/simple query because using Count property executes an additional access to the database
  2. Use an optimized query instead of an <Advanced Query> count. Writing a new and optimized query to fetch the count is more performant. For example, pay attention to any LEFT JOIN that is not filtering data.

More can be read here - http://jmateus.outsystemscloud.com/CodeReview/Rules.aspx?(Not.Licensed.For.Production)=


So, i should always use List.Empty instead of .Count = 0.


If i want to get the total number of records filtered by certain Id, do I use .Count or do i do an aggregate query Group by Count ?

Hi,

Yes, use Empty instead of Count = 0.

Regarding the max records, it depends if you are only interested in the count use an aggregate with a calculated count field or an advanced SQL  just returning the count. If you need the actual records with columns then use an aggregate and check the count property.

See also this great article of OutSystems MVP Justin James:

“Which property to see if a Query has results?” by Justin James https://link.medium.com/02IUSncHC7

Regards,

Daniel

i was checking through my colleague's code and discovered that he used the calculated count field in the preparation to get the total number of rows for the query (based on Id). He needs the total number of records to display to users.


So i was thinking about performance and from your reply does it mean that i do a simply aggregate and then just use the MyAggregate.Count property to get the total records?


Is this a better approach than to use a calculate count field? I was under the impression that use .Count would require the web to do another round trip to the server, but using calculated count field is a single trip.

Solution

Hi,

If you only need to show a total, then an aggregate with a calculated count is the way to go, as I already mentioned before. If you also need the actual records AND the count of those records then use <aggregate>.count.

 .Count will require a round trip to the db server

Regards,

Daniel

Solution