1 Reply Latest reply: Feb 15, 2016 7:30 PM by Stefan Wühl RSS

    Can I make this code goes faster?

    Cecilia Flores

      I'm working with a table like the one below, and a lot of variables declared with a "LET"

       

      IDGStatusAgeDensityYear
      1MR2080%2010
      2FR2690%2012
      3FA2468%2013

       

      The variables are something like this:

      LET P11MR20=0.10; LET P11MR21=0.50; LET P11MR22=0.80; LET P11MR23=0.30; LET P11MR24=0.65; LET P11MR25=0.41;

      LET P21MR20=0.90; LET P21MR21=0.50; LET P11MR22=0.20; LET P11MR23=0.70; LET P11MR24=0.35; LET P11MR25=0.59;

       

      And the code does this:

       

      LET vCantFilasAx = noofrows('Table1'); // I need to work through all the registers of my table

      For i=0 to $(vCantFilasAx)-1

      //I save in a variable the data of each row in my Table1

        LET vEmpr= Peek('ID',$(i),'Table1');

        LET vStatus= Peek('Status',$(i),'Table1');

        LET vGender= Peek('G',$(i),'Table1');

        LET vAge=Peek('Age',$(i),'Table1');

        LET vDens=Peek('Density',$(i),'Table1');

        LET vAfil=Peek('Year',$(i),'Table1');

        LET vBP=((2015-$(vAfil))*6);

        LET vBC=((2015-$(vAfil))*6)*$(vDens);

        IF $(vAge)>24 or $(vAge)<14 then

        LET vDens=$(vDens);

        LET vBCot=$(vBC);

        ELSE

        Let SumD1=0;

        Let SumD2=0;

        Let TSumD1=0;

       

      //For each age starting with the age declared in the row for each employee

        For ag=$(vAge) to 25

       

      //I created new variables in order to use them as an algoritm

       

        LET vP11='P11'& '$(vStatus)' & '$(vGender)'& $(vAge);

        LET vP21='P21' & '$(vStatus)' & '$(vGender)' & '$(vAge)';

        LET DB1_A=($(vDens)*$(vP11))+((1-$(vDens))*$(vP21));

        LET DB1_B=1-$(DB1_A);

        LET DB2_A=($(DB1_A)*$(vP11))+($(DB1_B)*$(vP21));

        LET DB2_B=1-$(DB2_A);

        LET DB3_A=($(DB2_A)*$(vP11))+($(DB2_B)*$(vP21));

        LET DB3_B=1-$(DB3_A);

        LET DB4_A=($(DB3_A)*$(vP11))+($(DB3_B)*$(vP21));

        LET DB4_B=1-$(DB4_A);

        LET DB5_A=($(DB4_A)*$(vP11))+($(DB4_B)*$(vP21));

        LET DB5_B=1-$(DB5_A);

        LET DB6_A=($(DB5_A)*$(vP11))+($(DB5_B)*$(vP21)); //let DB6_B=1-$(DB6_A);

       

        LET SumD1=$(DB1_A)+$(DB2_A)+$(DB3_A)+$(DB4_A)+$(DB5_A)+$(DB6_A);

       

        LET SumD2=$(SumD2)+6;

        LET TSumD1=$(SumD1)+$(TSumD1);

        //Then calculate the fields I'm going to use in one final table

        LET vBCot=$(TSumD1)+$(vBC);

        LET vDensidad_final=($(vBC)+$(TSumD1))/($(vBP)+$(SumD2));

        LET vDensidad=$(vDensidad_final);

        LET vEdad=$(vEdad)+1;

        NEXT

       

        END If

        Final2:

        LOAD

        '$(vWorker)' as ID,

        '$(vDens)' as FinalDens,

        '$(vBCot)' as BimCot

        AutoGenerate 1;

        LET vBCot=0;

      NEXT

      LET vBP=0;

      LET vBC=0;

      Store Final2 into Final2.qvd (qvd);

       

       

      The thing is, actually I have to run this until age 65, I have 50 million rows and 312 variables for each age. Table1 is a qvd, and I try this with data of 3million employees and the whole execution took me 35 hrs...

       

      Is there something I'm doing wrong or that i can do better?

        • Re: Can I make this code goes faster?
          Stefan Wühl

          You should try to avoid iterating with a loop over each table row and peek the values.

           

          Try to load the table, if you need to create a loop based on a record's value like age use the WHILE clause in your load, something like

           

          LOAD Age, iterno() as IterNo

          FROM YourTableSource

          WHILE Age + Iterno()-1 <= 65;

           

          Iterno() can be used as a counter for each iteration. Then try to implement your transformations using the set of variables as part of the LOAD statements (maybe using the variables or tables, created from the variables).