SYSQUERY::COUNTLOOPS() METHOD – COUNT OR LOOPS?

 

Recently, I was involved in investigating a performance issue. The initial thought for the issue was within the standard SysQuery::countLoops() method, but after some more deep dive, the culprit was due to some calculation of virtual fields. In this post, I will elaborate on the purpose and behavior of the SysQuery::countLoops() method and how the performance was impacted by virtual fields.

Purpose

Obviously, the purpose of the SysQuery::countLoops() method is to get a count of the number of records. Usually, a developer can use the next statement to get a record count.

MyTable  myTable;
integer  recordCount;

recordCount = select count(RecId) from myTable;

Eventually, the statement can be extended with a where clause. The SysQuery::countLoops() method has a huge advantage as this expects a QueryRun as a parameter. Using this method, you can pass the QueryRun from a form or report selection without knowing what filter ranges or additional table joins were set by the user. So, you can dynamically get a total number of records without any assumption on range values. This method is often used to e.g. determine the progress of periodic tasks or split batch jobs into multiple parallel tasks.

Under the hood

It is interesting to know how the count is executed within the SysQuery::countLoops() method. The method is calling another method inside the class SysQuery called countPrim() as you can see in the next screenshot.

The countPrim() method is assuming you need to have the count of the primary data source. The same method is also being called from the method SysQuery::countTotal(). When there is one data source in the query, you can use the countTotal() method; otherwise use the countLoops() method. Faisal Fareed wrote a blog about this before: Count records in Query Vs Cound Loops in Query

When there are multiple data sources, it will loop the results and count per record. This would be fast enough to get a count of the records unless the query has a huge number of records or something different which you can read below.

Performance degradation

During the performance investigation as referenced above, I learned something new myself. As I was already aware, you can create virtual fields on data entities and fill them using a postLoad() method, I had not noticed this option before on regular tables. To prevent having redundant data, you can do easy calculations in the postLoad() method which will be calculated row by row. E.g. you can calculate with decimals or subtract dates to get a number of days. Another option is to do database calls to get values from another related table.

Actually, the performance issue was caused during the loop in the countPrim() method. During the analysis of the trace, it was made clear that the next() method is also calling the postLoad() method. In this case, the postLoad() method was looking up values from another table, causing a lot of unnecessary trips to the SQL database. On Tier 2+ environments besides the query time, also the latency caused a performance issue.

As a solution, I suggested getting rid of the postLoad() method and adding a data source on the form or using display fields as the virtual fields were only used on a form.