1 Reply Latest reply: May 4, 2015 4:32 AM by Marcus Sommer RSS

    Optimization of a LOAD

    Friedrich Hofmann



      I have an app that parses a directory for  files with a specific ending (xls in this instance) and matching a specific name.

      - The issue is, in this one directory, those files (they are generated and saved there automatically) are saved with a timestamp,
         so there are hundreds of files there.

      - The files have a few different names

           - FA_TAR or FA_TAS

           - FA_TAR_FC or FA_TAS_FC

           - FA_TAR_RW or FA_TAS_RW

      => There are six different filenames possible.

      I have no problem with the latter four for they are unique of course.

      <=> The first two, FA_TAR and FA_TAS, are problematic insofar as when I make a searchmask to fit those, I use the * (because of the timestamp) and so two of the other four also match that (when the filemask is >> FA_TAR*.xls <<, then FA_TAR_FC*.xls and FA_TAR_RW*.xls also match)


      Currently I have a LOAD with a PRECEDING LOAD. It looks like this (the variables are all correct; p4 is the filename I look for and p5 is a specifier like >> (biff, no labels) <<)


      >> FOR EACH v_file in FileList('$(v_filemask)');
      FIRST 1 LOAD
      WHERE Aktuell_jn = 1
      AND Subfield(Subfield(Dateiname, '.', 1), chr(32), 1) = '$(p4)'
      //  AND   KEEPCHAR(Subfield(Dateiname, '.', 1), 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ456780_') = '$(p4)'
      FIRST 1 LOAD
      Filename() as Dateiname,
      FileTime() as Änderungszeit,
      IF(Date(Floor(num(FileTime())), 'DD.MM.YYYY') = DATE(Today(), 'DD.MM.YYYY'), 1, 0) as Aktuell_jn


      This construct works fine - but when it comes to the searchmask >> FA_TAS*.xls << or >> FA_TAR*.xls << - hundreds of files are parsed and then discarded.

      For some reason, that construct I have for the field >> Aktuell_jn << does not work if I use it in a WHERE clause in the main load - then I could do without the PRECEDING LOAD and just disregard all those other files. But, well, it doesn't work so I have to use the PRECEDING LOAD.


      Can anyone suggest something here?

      Thanks a lot!

      Best regards,




        • Re: Optimization of a LOAD
          Marcus Sommer

          Hi DataNibbler,


          maybe it's easier to use two (simpler) loops with:


          for each vFileTyp in 'FA_TAR',  'FA_TAS' // ....

               let v_filemask = .... // create new

               FOR EACH v_file in FileList('$(v_filemask)');


          And then you load the first record only to fetch filename and filetime (which goes quite fast even with hundreds of files) but you could query these properties directly from filesystem without loading them, maybe so:



          Load filename('$(v_file)') as FileName, filetime('$(v_file)') as FileTime Autogenerate 1;


          and it will be faster.


          - Marcus