Wednesday, June 18, 2014

How to count query loops number

Amongst our other mundane chores, counting of a query loops is not the trickiest one but, unfortunately, still clumsy developed even in AX 2012.

 Not only is it strange to have two different methods for one only and multiple data sources, but it takes long time and returns incorrect results when it comes to groupings.

 SysQuery::countTotal and SysQuery::countLoops use the private method SysQuery::countPrim, which can be redone in a way suggested here (in Russian).

private server static container сountPrim(container _queryPack)
{
    Query                   countQuery;
    QueryRun                countQueryRun;
    QueryBuildDataSource    qbds;
    QueryBuildFieldList     qbfl;
    Common                  common;
    Integer                 counter;
    Integer                 loops;
    
    Integer                 tmxGroupNumber;
    Integer                 tmxDataSourceNumber;
    ;
    countQueryRun   = new QueryRun(_queryPack);
    countQuery      = countQueryRun.query();
    tmxGroupNumber  = countQuery.groupByFieldCount(); //<-- this guarantees number of groupings

    for (tmxDataSourceNumber = 1; tmxDataSourceNumber <= countQuery.dataSourceCount(); tmxDataSourceNumber++)
    {
        qbds = countQuery.dataSourceNo(tmxDataSourceNumber);
         qbds.update(false);
        //qbds.sortClear();

        //tmxGroupNumber +=(qbds.orderMode()==orderMode::GroupBy); 
        qbfl = qbds.fields();
        qbfl.dynamic(false);
        qbfl.clearFieldList();
         qbds.addSelectionField(fieldNum(Common,RecId),SelectionField::Count);
    }
  
    countQueryRun   = new QueryRun(countQuery);

    while (countQueryRun.next())
    {
         common  = countQueryRun.getNo(1);
        counter += common.RecId;
        loops++;
    }
    //return [counter,loops];
    return [counter,(tmxGroupNumber ? loops : counter)];
 }