How to optimize a loop/peek operation?

[JeffQV]

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:

_CarTypeBitMask:

LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
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]:
LOAD * INLINE [
%CarID,CarTypeMask
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

    LET vMask=peek('CarTypeMask',$(i),'Cars');          // read the mask

    FOR j = 1 toNoOfRows('_CarTypeBitMask')              // for each possible bit mask
        LET vMask_Num = peek('_CarTypeMaskVal',$(j),'_CarTypeBitMask');
        IF( $(vMask) bitand $(vMask_Num) = $(vMask_Num) ) THEN          // IF this bit is ON
            LET vCMF_Text = peek('_CarTypeMaskText',$(j),'_CarTypeBitMask');
            [CarTypes]:
            LOAD * INLINE[
                %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?

[swuehl · Accepted solution]

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
LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
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]:
LOAD * INLINE [
%CarID,CarTypeMask
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:
LOAD * 
  ,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType
WHERE Split;
LOAD %CarID, 
  CarTypeMask bitand pow(2,iterno()-1) as Split
Resident Cars
WHILE iterno() < 7;

If this runs better, could you post the improvement?

[JeffQV]

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 !!

[swuehl]

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!

[Anonymous]

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:

Load

    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:

LOAD  $(vTicketKey) as Ticket,

      $(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

Thanks in advance.

[swuehl]

"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

LOAD

     Ticket,

     Max(Timestamp) as LatestTimestamp,

     FirstSortedValue(AnotherAttribute, -Timestamp) as LatestAnotherAttribute

RESIDENT YourTable

GROUP BY Ticket;

[Anonymous]

Hi,

Thanks for your quick reply on this.

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

Please see below output.

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.

[swuehl]

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.