4 Replies Latest reply: Nov 1, 2013 10:05 AM by Bill Markham RSS

    Join Efficiency and Performance

    Bill Markham

      Hi

       

      If in a load script have 2 tables one with a few million rows and one with only a few dozen rows. which is more efficient:

       

      a)     Load the big one first and then join load the small one second

       

      b)     Load the small one first and then join load the big one second

       

      [I know the ApplyMap() function could be an option, but in this scenario I do not wish to use it]

       

      Best Regards,     Bill

        • Re: Join Efficiency and Performance
          Clever Anjos
          T1:
          LOAD
          SubField('$(MonthNames)',';',mod(RecNo(),12)) as F1,
          rand() as F2
          AutoGenerate 10000000;
          join
          LOAD
            SubField('$(MonthNames)',';',mod(RecNo(),12)) as F1,
            rand() as F3
          autogenerate 100;
          

           

          took 28 secs into my notebook and the code below took the same 28 secs

           

          T2:
          LOAD
            SubField('$(MonthNames)',';',mod(RecNo(),12)) as F1,
            rand() as F3
          autogenerate 100;
          join
          LOAD
          SubField('$(MonthNames)',';',mod(RecNo(),12)) as F1,
          rand() as F2
          AutoGenerate 10000000;