6 Replies Latest reply: Sep 21, 2011 9:31 AM by david.morris RSS

    Writing 'If Statement' within the Load clause

      Hello,

       

      Does writing 'If Statements' within the load clause in the script cause a significant slow down in the load speeds in you opinion.

      I know it will depend in the number of 'ifs' and the number of records you are loading but has anyone ever had any issues with performance with this?

      Interested to hear your experiences. Many thanks.

       

      Dave

        • Re: Writing 'If Statement' within the Load clause
          Deepak Vadithala

          Hi Dave,

           

          Good question. Yes, I have used IF statement in 30 million rows document. I have noticed that it takes more time if you have nested if's. However, I have eventually changed IF statement to ApplyMap with a Mapping Load.

           

          I hope this helps.

           

          Cheers - DV

          • Re: Writing 'If Statement' within the Load clause
            Perumal Ayyappan

            Hi,

            Please check this method

             

            Map_1:

            MAPPING LOAD

               test,

               'Yes'

            RESIDENT table1;

             

            New_Table:

            LOAD

               test,

               ApplyMap('Map_1',test,'b') AS test1

            RESIDENT table2;

             

            That should do it.

            Regards ,

            Perumal.A

            • Writing 'If Statement' within the Load clause
              Stefan Wühl

              Hi David,

               

              is your question about how to replace if()'s? Well, if you are using if's just to do kind of mapping, the already proposed mapping tables might be a solution if you are checking for equality on a single field.

               

              If your question is more of an abstract kind, you will need to clarify what you understand under the term performance "issue". If an issue is that your load time may break a business requirement, than I would guess that many developers have experienced that kind of issue during development, but probably handled successfully by redesigning the load (maybe using some other techniques like mapping tables) and / or the reporting architecture (i.e. maybe using somekind of stages to prepare qvd's that can be reused and loaded optimized from within multiple qvw's) or using incremental load or...

               

              So a solution to an "issue" is IMHO depending on your environment (technical and business).

               

              If your question is more about some comparative measures about using if's or don't use them in load, well I don't have data at hand. I would also guess that it is not only depending on number of if's and number of records, but also on type of comparison (i.e. string or numerical).

               

              But if you can do the data transformation using an if or similar technique in the script, I would always prefer this over doing same in the front end, even if your load time increases.

               

              Not sure if this helps, but have a nice weekend

              Stefan