To get the number of rows returned by an Aggregate or a SQL query, you can resort to the .Count property. The original query is normally designed for retrieving data, not for counting. When the .Count property is evoked, the compiler generates a second query to perform the counting, without any Maxrecords limitation.
While in a aggregate OutSystems platform knows exactly how to generate an optimized query for counting records, this is not the case for SQL queries - A SQL query is an open door for writing any complex SQL of the developer responsibility and it is too complex to get the intended semantic to generate an optimized version for counting records.
In SQL queries, all the platform does is to run the same query without the Maxrecords limitation. Since the query was originally designed to retrieve data, it will fetch unnecessary fields and perform unnecessary joins to get to that data and eventually sort the result, simply to obtain the row count in the end in brute force way that is far from being performant.
1. To count the number of rows of a SQL Query, copy the query designed to retrieve data and adapt it with the sole goal of counting number of rows
2. Don’t use the .Count property, both in aggregates or SQL queries, just to test if the query returned something or not, i.e.,
Testing <Query>.List.Empty instead of testing <Query>.Count = 0 saves a costly run of an extra query just to count elements when all you want is to check if the result is empty or not