Writing 'If Statement' within the Load clause

[#1]

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

[IAMDV]

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

[perumal_41]

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

[swuehl · Accepted solution]

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

[#5]

Thanks a lot for the info DV. I'll look into using the ApplyMap function. It's something I wasn't aware of.

Dave.

[#6]

Thanks Perumal. That's interesting to know about. I will look into using that method in future.

Dave.

[#7]

Thanks Stefan. Yes, it was a vague question. I was just looking to see generally if the use of 'Ifs' in the script had a drastic effect on load time. You raise some interesting points there about the context of your application and the 'acceptable' performance. Thanks for the information and food for thought.

Dave.