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

    How to optimize a loop/peek operation?

    Jeff Harrison

      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?

        • Re: How to optimize a loop/peek operation?
          Stefan Wühl

          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?

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

              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?
                Ashok N

                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.

                  • Re: How to optimize a loop/peek operation?
                    Stefan Wühl

                    "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;

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

                        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_NUMSTART_DATE_TIMEEND_DATE_TIMETicket_His_OpenStatus_DescTicket_His_TeamName
                        14987915-06-17 10:3818-09-17 14:13OpenCS Team 8 Birm
                        14987918-09-17 14:1318-09-17 14:16In ProcessData Management 2
                        14987918-09-17 15:0418-09-17 15:09In ProcessCS Team 2 Birm
                        14987922-09-17 8:2427-09-17 8:44Customer ActionCS Team 2 Birm
                        14987927-09-17 8:4427-09-17 8:473rd Level SupportCS Team 2 Birm
                        14987927-09-17 8:4728-09-17 12:343rd Level SupportCS Team 8 Birm
                        14987928-09-17 12:3428-09-17 12:36In ProcessCS Team 8 Birm
                        14987905-10-17 12:0231-12-99 0:00CompleteCS Team 8 Birm
                        1731-05-17 12:4501-06-17 11:44OpenPost Live - Plus / Elite / Video
                        1701-06-17 11:4430-06-17 10:23Closed (in use)Post Live - Plus / Elite / Video
                        1730-06-17 10:2330-06-17 10:24In ProcessPost Live - Plus / Elite / Video
                        1730-06-17 10:2430-06-17 10:53In ProcessElite Support
                        1730-06-17 10:5331-12-99 0:00CompleteElite Support



                            

                        Output
                        TCKT_HIS_KEY_AS_NUMSTART_DATE_TIMEEND_DATE_TIMETicket_His_OpenStatus_DescTicket_His_TeamName
                        14987927-09-17 8:4728-09-17 12:343rd Level SupportCS Team 8 Birm
                        14987928-09-17 12:3428-09-17 12:36In ProcessCS Team 8 Birm
                        14987905-10-17 12:0231-12-99 0:00CompleteCS Team 8 Birm
                        1730-06-17 10:2430-06-17 10:53In ProcessElite Support
                        1730-06-17 10:5331-12-99 0:00CompleteElite Support

                        Please let me know required more clarification on this.

                         

                        Thanks,

                        Ashok.