"Indexing" Qlik data model tables, more quickly than before

My colleague, Tim, got us going down a path that led to a faster way to group like field values to speed up calculations. If this concept does not sound familiar, I wrote about it here and here. (See the second link for the full, original version of this subroutine.)

Tim's use case was the one in which I am most confident this technique will help noticeably: improving performance of calculations that involve the Aggr() function.

My old, benign join load autogenerated the resident values from the field you want to sort by, but he found that it works just as well with only one field value.

LOAD
FieldValue('$(v_GroupFieldName)', RecNo()) as [$(v_GroupFieldName)]
AUTOGENERATE
FieldValueCount('$(v_GroupFieldName)');

We further refined it and found that no values at all are required, i.e., you can join a 0-row table and it will still sort by the join field. The important thing is the join itself! The overhead associated with generating and joining actual field values is unnecessary. The resulting data model and table sort orders are identical to the original subroutine. Here is the updated subroutine with less overhead and shorter execution time:

SUB IndexResidentTable( v_TableName, v_GroupFieldName )

LET v_StartSubTimestamp = Now();

TRACE [MESSAGE]:  Grouping like '$(v_GroupFieldName)' field values in table '$(v_TableName)' to improve calculation performance;

OUTER JOIN ([$(v_TableName)])
LOAD
Null() as [$(v_GroupFieldName)]
AUTOGENERATE
0;

LET v_SubDuration = Interval(Now() - v_StartSubTimestamp, 'h:mm:ss');

TRACE [MESSAGE]:  Finished grouping like '$(v_GroupFieldName)' field values in table '$(v_TableName)' in $(v_SubDuration);

LET v_TableName = ;
LET v_GroupFieldName = ;
LET v_StartSubTimestamp = ;
LET v_SubDuration = ;

END SUB