7 Replies Latest reply: Dec 1, 2017 2:58 AM by Stefan Wühl

# How to optimize a loop/peek operation?

PROBLEM: I have a very slow running loop that peeks() at a bit mask field in each row in order to extract the values into a separate fact table

DETAILS:

I have a data model in which each possible status is encoded as a different bit.

Here would be the 'hard coded' table representing the possible bit values:

1,Truck

2,Car

4,Mini Van

8,Cross-Over

16,2-wheel

32,4-wheel

64,AWD

];

And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)

[Cars]:
1,18          // Car + 2-wheel
2,33          // Truck + 4-wheel
3,24          // Cross-Over + 2-wheel
];

The idea is to generate a new table pulling out the car type combinations:

CarTypes:

%CarID,CarType

1,Car

1,2-Wheel

2,Truck

2,4-Wheel

3,Cross-Over

3,2-Wheel

Here's the script I run, but with thousands of rows it is way too slow:

// CREATE CarTypes 1-N table

//

vNumRows = NoOfRows('Cars')
FOR i=0 to \$(vNumRows)-1;                                        // for each Car
LET vCarID=peek('%CarID',\$(i),'Cars');                    // read unique car ID

[CarTypes]:
%CarID,CarType
\$(vCarID),\$(vCMF_Text)]
;
ENDIF
NEXT

NEXT

(please note I modified the code here to be based on car and car types from my original code. My original code works. It's just slow)

So any ideas on how to improve the performance?

• ###### Re: How to optimize a loop/peek operation?

Yes, Loops are really slow for what you are trying to do.

I would chose another approch, creating a load of your cars table with a integrated WHILE loop in the LOAD statement to generate a record per bit of the mask. Then MAP the bits:

```CarTypeBitMask:
MAPPING
1,Truck
2,Car
4,Mini Van
8,Cross-Over
16,2-wheel
32,4-wheel
64,AWD
];

//And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)

[Cars]:
1,18
2,33
3,24
];
//
//The idea is to generate a new table pulling out the car type combinations:
//
//CarTypes:
//%CarID,CarType
//1,Car
//1,2-Wheel
//2,Truck
//2,4-Wheel
//3,Cross-Over
//3,2-Wheel
//
//
//Here's the script I run, but with thousands of rows it is way too slow:
//
// CREATE CarTypes 1-N table
//

RESULT:
,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType
WHERE Split;
Resident Cars
WHILE iterno() < 7;

```

If this runs better, could you post the improvement?

• ###### Re: How to optimize a loop/peek operation?

Thank you very much!

This is a very slick piece of code - I continue to forget that you can iterate during the load operation.

This is SIGNIFICANTLY FASTER!

I had a table of 30,000 entries and 16 possible bit masks.

The original script took 2 hours to run. With your solution it was down to 30 seconds !!

• ###### Re: How to optimize a loop/peek operation?

I had a table of 30,000 entries and 16 possible bit masks.

The original script took 2 hours to run. With your solution it was down to 30 seconds !!

Yes, that's what I call significant. There is a BIG overhead in For / Next loop and table creation.

Like it!

• ###### Re: How to optimize a loop/peek operation?

Hi Stefan,

I am also facing same type of problem. I am using Loops and Peek functions to get the Last team data in Ticket history table.Logic is working fine but it runs too slow and takes 10 hrs to complete the load.

Requirement is needs to pick most recent team details only per each ticket.

For more details please open attached excel where you can find sample Input and Output format.

Code i written in my application is below.

Ticket_History:

TCKT_HIS_KEY_AS_NUM,

Ticket_His_TeamName,

START_DATE_TIME,

END_DATE_TIME,

Ticket_His_OpenStatus_Desc,

1 as Jink

//    TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName as TTnKey,

//    TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName&START_DATE_TIME&END_DATE_TIME as TempKey

Resident    Ticket_History1

Order by TCKT_HIS_KEY_AS_NUM,START_DATE_TIME desc , END_DATE_TIME desc;

DROP Table Ticket_History1;

Let vNoOfRows = NoOfRows('Ticket_History');

set vFlag=1;

FOR i=0 to \$(vNoOfRows)-1

Let i1= \$(i)+1;

Let vTicketKey2=Peek('TCKT_HIS_KEY_AS_NUM',\$(i),'Ticket_History');

Let vTicketKey3=Peek('TCKT_HIS_KEY_AS_NUM',\$(i1),'Ticket_History');

SET vTicketKey=Peek('TCKT_HIS_KEY_AS_NUM',\$(i),'Ticket_History');

SET vTicketKey1=Peek('TCKT_HIS_KEY_AS_NUM',\$(i1),'Ticket_History');

SET vTeam=Peek('Ticket_His_TeamName',\$(i),'Ticket_History');

SET vST=Peek('START_DATE_TIME',\$(i),'Ticket_History');

SET vET=Peek('END_DATE_TIME',\$(i),'Ticket_History');

SET vStatus=Peek('Ticket_His_OpenStatus_Desc',\$(i),'Ticket_History');

SET vKey=Peek('TCKT_HIS_KEY_AS_NUM',\$(i),'Ticket_History')& Peek('Ticket_His_TeamName',\$(i),'Ticket_History');

SET vKey1=Peek('TCKT_HIS_KEY_AS_NUM',\$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',\$(i1),'Ticket_History');

Let vKey2=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',\$(i),'Ticket_History')& Peek('Ticket_His_TeamName',\$(i),'Ticket_History'));

lET vKey3=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',\$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',\$(i1),'Ticket_History'));

TestReplicate:

\$(vTeam) as Team,

\$(vST) as ST,

\$(vET) as ET,

\$(vStatus) as Status,

\$(vKey) AS Key,

\$(vKey1) AS Key1,

\$(vFlag) as Flag

AutoGenerate(1);

//  TRACE \$(vKey2) = \$(vKey3);

If '\$(vKey2)'<>'\$(vKey3)' then

set vFlag=0;

ENDIF

//TRACE \$(vTicketKey2) = \$(vTicketKey3);

If '\$(vTicketKey2)'<>'\$(vTicketKey3)' then

set vFlag=1;

ENDIF

//Where  \$(vKey)=\$(vKey1) ;

NEXT i;

@Stefan wuhl

• ###### Re: How to optimize a loop/peek operation?

"Requirement is needs to pick most recent team details only per each ticket."

You should be able to fulfill your requirement by simply aggregating your table, grouped by ticket.

Use FirstSortedValue() to retrieve the latest information by a sort order value (latest timestamp).

Something like

Ticket,

Max(Timestamp) as LatestTimestamp,

FirstSortedValue(AnotherAttribute, -Timestamp) as LatestAnotherAttribute

RESIDENT YourTable

GROUP BY Ticket;

• ###### Re: How to optimize a loop/peek operation?

Hi,

Sorry for confusion, I don't want to pick only last record..I need to pick last team related all records.

If we use Aggregation we will get one record per Ticket, But i want to load last team all records to find Turntime of that last team

 Input TCKT_HIS_KEY_AS_NUM START_DATE_TIME END_DATE_TIME Ticket_His_OpenStatus_Desc Ticket_His_TeamName 149879 15-06-17 10:38 18-09-17 14:13 Open CS Team 8 Birm 149879 18-09-17 14:13 18-09-17 14:16 In Process Data Management 2 149879 18-09-17 15:04 18-09-17 15:09 In Process CS Team 2 Birm 149879 22-09-17 8:24 27-09-17 8:44 Customer Action CS Team 2 Birm 149879 27-09-17 8:44 27-09-17 8:47 3rd Level Support CS Team 2 Birm 149879 27-09-17 8:47 28-09-17 12:34 3rd Level Support CS Team 8 Birm 149879 28-09-17 12:34 28-09-17 12:36 In Process CS Team 8 Birm 149879 05-10-17 12:02 31-12-99 0:00 Complete CS Team 8 Birm 17 31-05-17 12:45 01-06-17 11:44 Open Post Live - Plus / Elite / Video 17 01-06-17 11:44 30-06-17 10:23 Closed (in use) Post Live - Plus / Elite / Video 17 30-06-17 10:23 30-06-17 10:24 In Process Post Live - Plus / Elite / Video 17 30-06-17 10:24 30-06-17 10:53 In Process Elite Support 17 30-06-17 10:53 31-12-99 0:00 Complete Elite Support

 Output TCKT_HIS_KEY_AS_NUM START_DATE_TIME END_DATE_TIME Ticket_His_OpenStatus_Desc Ticket_His_TeamName 149879 27-09-17 8:47 28-09-17 12:34 3rd Level Support CS Team 8 Birm 149879 28-09-17 12:34 28-09-17 12:36 In Process CS Team 8 Birm 149879 05-10-17 12:02 31-12-99 0:00 Complete CS Team 8 Birm 17 30-06-17 10:24 30-06-17 10:53 In Process Elite Support 17 30-06-17 10:53 31-12-99 0:00 Complete Elite Support

Please let me know required more clarification on this.

Thanks,

Ashok.

• ###### Re: How to optimize a loop/peek operation?

Ok, but similar approach:

Find the last team per ticket, using my previous script. Then use these two fields, ticket and team, as key to join all records related to these ticket team combinations from your input table.