3 Replies Latest reply: Jun 25, 2015 12:56 PM 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?