0 Replies Latest reply: Oct 19, 2012 4:31 AM by Aldo Liaks RSS

    How to faster up left join in for next loop

      Hi Guys,

       

      Please take a look a the code below.

      I am retrieving data from external qvd file, then creating some new field (based on data from same qvd) and joining to the first table - Table1.

      Since the fields I am adding will dynamically change, I woul like to do it (the join) with a loop.

      The issue is that doing the left join once as in the commented script below takes about 2 seconds, while doing the same with the for next loop takes several minutes...). Everything is the same, except the loop.

       

      Is there any good advice to work it around?

       

      Thanks in advance,

      Aldo.

       

      Table1:
      LOAD Distinct
            *
      FROM $(vTableDir)$(vTable).qvd (qvd);
      
      
      //Loop over each currency and create currency translated coulumn
      FOR EACH vCurr IN $(vCurrToRatesCross)
           LET vCurrCode =Null();
           CurrCode:
           LOAD Distinct
                      Upper(CODE) AS CODE
           FROM $(vAssociated)CURRENCIES.qvd (qvd)
                      WHERE CURRENCY =$(vCurr);
           LET vCurrCode =Peek('CODE',-1,'CurrCode');
      
      
           LET vField =Null();
      
      
      
      
      ////// It takes too much time... WHY ??  In the meanwhile, use as following:
      //               LEFT JOIN (Table1)
      //               LOAD Distinct
      //                     If( OrdI.CURRENCY =$(vCurr),  $(vField1),  $(vField1) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField1)_$(vCurrCode)
      //                     ,If( OrdI.CURRENCY =$(vCurr),  $(vField2),  $(vField2) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField2)_$(vCurrCode)
      //                     ,If( OrdI.CURRENCY =$(vCurr),  $(vField3),  $(vField3) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField3)_$(vCurrCode)
      //                     ,If( OrdI.CURRENCY =$(vCurr),  $(vField4),  $(vField4) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField4)_$(vCurrCode)
      //                     ,_ConvertToCurrencyKey1
      //               FROM $(vTableDir)$(vTable).qvd (qvd);
                     
                 FOR EACH vField IN $(vSpreadFieldList)
                     LEFT JOIN (Table1)
                     LOAD Distinct
                           If( OrdI.CURRENCY =$(vCurr),  $(vField),  $(vField) *OrdI.IEXCHANGE / If( OrdI.Rate_$(vCurrCode) <>0, OrdI.Rate_$(vCurrCode), 1) ) AS $(vField)_$(vCurrCode)
                           ,_ConvertToCurrencyKey1
                     FROM $(vTableDir)$(vTable).qvd (qvd);
                 NEXT
      
      
      DROP TABLE CurrCode;
      NEXT
      
      
      Exit Script;