Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am facing a huge performance problem on a LOAD where I use the peek function many times, for different fields. This is a (very) simplified version of my script:
FinalTable:
LOAD
MATXPeriod_KEY,
If(IsNull([Total Stock]) AND previous(MATX_KEY) = MATX_KEY,
peek([Inventory]),If(IsNull([Total Stock]),0,[Total Stock])) As [Inventory],
If(IsNull([STPRS]) AND previous(MATX_KEY) = MATX_KEY,
peek([Standard Price]),If(IsNull([STPRS]),0,[STPRS])) As [Standard Price]
Resident SourceTable
Order by MATXPeriod_KEY;
SourceTable has about 40 million rows, and the entire LOAD takes about 2 hours to finish. I am looking for a faster solution
SourceTable looks like:
MATXPeriod_KEY | MATX_KEY | [Total Stock] | [STPRS] |
AA100|201101 | AA100 | 50 | 12,34 |
AA100|201102 | AA100 | 55 | |
AA100|201103 | AA100 | 11,21 | |
AA100|201104 | AA100 | ||
AA100|201105 | AA100 | 40 | 11,78 |
AA100|201106 | AA100 | 45 | |
AA100|201107 | AA100 | ||
AA100|201108 | AA100 | 90 | |
BB200|201204 | BB200 | 10 | 78,90 |
BB200|201205 | BB200 | 77,67 | |
BB200|201206 | BB200 | 15 | |
BB200|201207 | BB200 | 78,34 | |
BB200|201208 | BB200 | ||
BB200|201209 | BB200 |
I basically need to "fill the blanks" with the first not-null previous value of each field.
What about using a Intervalmatch? how could I implement it? This peek() function is very useful, but has a really bad performance.
I already reviewed many of the post on this community regarding peek() function, but I did not find an alternative solution to avoid it.
Thank you in advance,
Luis.
The problem is more likely that you reorder the table. You need to use a resident table for that. With 40 million rows that probably needs a lot of ram and cpu. If you get the data from an sql database you can try making that database do the ordering by adding the order by clause to the sql statement. If that's possible you won't need a resident table. Your load should be quicker then.
Thanks for your answer,
The table has 40 million rows and a total of 25 fields ( 5 GB on the qvd ), so yes, the amount of RAM needed is huge.
The table is calculated in the script after many transformations, so I can not do the ordering in the sql statment...
What about order the table on a LOAD and do the Peeks in a different load (with no ordering). Will that make any different?
Maybe. That's something you can try out. But I don't expect it will help very much. If you now use resident tables a lot, perhaps you can store the intermediate result tables to qvd files. You then use the qvd as source instead of a resident table for the next transformation. But for an order by you cannot avoid using a resident table.