How to avoid using Peek() and Order By

[luis_pimentel]

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.

[Gysbert_Wassenaar]

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.

[luis_pimentel]

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?

[Gysbert_Wassenaar]

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.