Posted - 11/11/2013 : 12:37:45
| Hi, I need your suggestions in realising the following requirement.
I have a table with 1.3Million records and I need to determine column 'Contribution 2' for each of these 1.3Millions records. My data looks like this -
Material Number Sales Contribution 1 (%) Contribution 2 Class
C2 150 34.88% 34.88% A
C3 120 27.91% 62.79% A
C1 100 23.26% 86.05% B
C4 35 8.14% 94.19% B
C5 25 5.81% 100.00% C
Total 430 100.00%
The table has the following columns - Material, Sales, Contribution 1 (%) (sale of each Material expressed as a % of the Total Sales), Contribution 2 ( for each record, sum of all values less than it), Class (determined from Contribution 2)
A brief about Contribution 2 column - its the sum total of all Contribution 1 values. For example: for material C3 Contribution 2 = Contri 1 of c3 + contri 1 of c2. Similarly for material C1 Contribution 2 = Contri 1 of c3 + contri 1 of c2 + contri 1 of c1. and so on for each material.
I am currently doing this as a self-join of the table. Am really not happy with the performance - its taking ~14 mins to process this. Have also tried partitioning the table but to no avail. I do not wish to use loops here.
Would appreciate if you could advise on doing this better as i need to scale it to 50 million records