Calculated Field in Load Script takes much time!!!

[dmohanty]

Hi All,

I have to create a calculated field (a3 * b1) / b2  as c1 from the below script. But the issue is that each table is having more than 10 Million records and the calculation in taking a huge time.

Any other approaches to do this?

My script is like below:

I have a scenario like this:

A:                      ////12 million records

Load

a1,

a2,

a3

from A.qvd;

Left join

Load                 //// 10 million records

a1,

b1,

b2

from B.qvd;

A_New:

Load

*,

(a3 * b1) / b2  as c1   ///// This creation takes a huge time because of millions of records.

resident A;

Drop Table A;

[Colin-Albert]

Do you need to recalculate the sum across the whole dataset each time you load?

Can you do the sum once and save a QVD with the values calculated and then just add new rows to the table.

Search for incremental load for ideas on this.

Another approach could be to use a mapping load.

From your expression (a3 * b1) / b2 is the same as a3 * (b1 / b2)

You could calculate the ratio b1 / b2 in a mapping table.

Then the script does not need any joins.

BCalcMap:

Mapping load

     a1,

     (b1 / b2) as ratio

from B.qvd;

A_New:

load

     a1, a2, a3,

     ( a3 * applymap('BCalcMap', a1) ) as c1

from A.qvd ;