Performance Issue : Another way to write this expression

[#1]

HI All,

I am having a ridiculous amount of trouble running this expression and can't think of another way of writing this and still getting the same result

aggr(count({$<CalendarMonthAndYear={'vMonthSpecific'},CallType={'o'},CallClassMapping={'Mobile'}>}Key),Digits, Extn, FirstName, Surname, CalendarMonthAndYear)

What the expresion does is the following

• Counts the number of Keys with the set analysis above
• Aggregates this over Digits, Extension, FirstName, Surname and CalendarMonthAndYear

Additional information

• Dataset has been limited to 1 Month
• 800000 rows

Can someone explain or help me to get this expression to calculate without taking an hour

Thanks,

Byron

[Gysbert_Wassenaar · Accepted solution]

If vMonthSpecific is a variable then you probably don't need to aggregate over CalendarMonthAndYear since you're already limiting it to one value. If CalendarMonthAndYear is a date field you could make sure vMonthSpecific is a number and loose the single quotes. Comparing numbers is faster than comparing strings.

If you really want to improve performance, you might want to precalculate this in the script instead of in an expression. Something like:

PreCalculatedKeyCounts:

Load

Digits, Extn, FirstName, Surname, CalendarMonthAndYear, count(Key) as KeyCount

resident ...mydataset...

group by Digits, Extn, FirstName, Surname, CalendarMonthAndYear

where CallType = 'o' and CallClassMapping = 'Mobile';

[#3]

thanks for your reply mate. Removing the calendarmonthandyear did speed it up a little.

I think I will also take your suggestion about aggregating this in the script along with some other expressions which are causing a bit of trouble for me.

cheers,

byron