Nested Apply Map !?

[#1]

Hi,

I have a Table with over 30 million records.

It is to be used as to crete multiple ApplyMap wherein the checking field (the first field) is always the same for those ApplyMaps.

Cannot use the Associative concept here.

Is there any way to optimize it , so that the Table is loaded only once but still maintain the ApplyMap.

Appreciate any help.

Thanks !

[Qrishna]

So your 30mil record table is a mapping table or other fact/dimension table?

[maxgro]

don't think

you can try to store the fields you need (first field + .....) in a qvd and load many times from that qvd (for the mapping tables) instead of many resident load

[Clever_Anjos]

Would you mind sharing your script?

[#5]

Hi,

Its not a complex script actually.. its just the Data that is huge !

For your assistance I have mocked it ;

TabA:

Key_ID,

Sys_Key,

Sys_Name,

Sys_Description;

GetSysKey:

Mapping Load

Key_ID,

Sys_Key

resident TabA;

GetSysName:

Mapping Load

Key_ID,

Sys_Name

resident TabA;

....So on.

These  Mapping tables will be used to fetch the respective fields... For all the Mapping Tables 'Key_ID' Is the Matching Key.

Is it helpful ?

[#6]

Hi,

It can be taken as a  Dimension Table.

[Qrishna]

Its not possible i guess.

Your mapping table should always have 2 fields and not more than that.

So if you  want different mapping fields for different applymaps from the same 30mil record table, you have to load it multiple times as multiple mapping tables..

As   massimop

suggested you can store the 30mil table in a qvd and load the required field into mapping tables.

Thanks

[Clever_Anjos]

As maxgro‌ suggested, you could retrieve them from your QVD that could be more efficient than a resident read

GetSysKey:

Mapping Load

Key_ID,

Sys_Key

from yourqvd(qvd);

GetSysName:

Mapping Load

Key_ID,

Sys_Name

from yourqvd(qvd);

and so on

[#9]

You can try with one mapping table like below  and extract the values with sub field function.

MAP_FIELDSTRING:

LOAD

     KEY_ID,

     Sys_Key & '|' & Sys_Name & '|' & Sys_Description AS ConcatedString

From source;

LOAD

     KEY_ID,

     subfield(ConcatedString,'|',1) as Sys_Key,

     subfield(ConcatedString,'|',1) as Sys_Name

.

.

.

.

.

LOAD

     KEY_ID,

     ApplyMap('MAP_FIELDSTRING',KEY_ID,null()) AS ConcatedString

From AnotherSource

;

[maxgro]

I suggested from qvd because usually is faster than resident but, after some test, it seems that creating the mapping table with a resident load is faster than creating the same tables from a qvd

The reason seems to be the mapping load from a qvd isn't optimized (without mapping 00:00:35).

this is the log with the time, 10M (not 30M) of records

12/08/2015 21:25:16

TabA << AUTOGENERATE(10000000) 10.000.000 lines fetched

12/08/2015 21:26:24

time create TabA=00:01:08

12/08/2015 21:26:24

12/08/2015 21:26:54

time create qvd=00:00:30

12/08/2015 21:26:54

GetSysKey << TabA 10.000.000 lines fetched

GetSysName << TabA 10.000.000 lines fetched

GetSysDescription << TabA 10.000.000 lines fetched

12/08/2015 21:28:41

time create mapping table resident=00:01:47

12/08/2015 23:36:08

GetSysKey << Taba 10.000.000 lines fetched

GetSysName << Taba 10.000.000 lines fetched

GetSysDescription << Taba 10.000.000 lines fetched

12/08/2015 23:38:18

time create mapping table qvd=00:02:10

and this is the script if someone else want to check on his pc (you have to comment / uncomment the script to test the 2 diffferent way)

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

////////////  make 10M records qvd

//let start=now(1); trace $(start);

//

//TabA:

//load

//rowno() as Key_ID,

//'key ' & rowno() as Sys_Key,

//Hash128(rand()*100000) as Sys_Name,

//Hash128(rand()*10000) as Sys_Description

//AutoGenerate 10000000;

//

//let end=now(1); trace $(end);

//let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

//TRACE time create TabA=$(diff);

//

//////////// store qvd

//let start=now(1); trace $(start);

//

//store TabA into TabA.qvd (qvd);

//

//let end=now(1); trace $(end);

//let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

//TRACE time create qvd=$(diff);

//

//////////// mapping from resident

//let start=now(1); trace $(start);

//

//GetSysKey:

//Mapping Load

//Key_ID,

//Sys_Key

//resident TabA;

//

//GetSysName:

//Mapping Load

//Key_ID,

//Sys_Name

//resident TabA;

//

//GetSysDescription:

//Mapping Load

//Key_ID,

//Sys_Description

//resident TabA;

//

//let end=now(1); trace $(end);

//let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

//TRACE time create mapping table resident=$(diff);

//

//drop table TabA;

//////////// mapping from qvd

let start=now(1); trace $(start);

GetSysKey:

Mapping Load

Key_ID,

Sys_Key

from TabA.qvd (qvd);

GetSysName:

Mapping Load

Key_ID,

Sys_Name

from TabA.qvd (qvd);

GetSysDescription:

Mapping Load

Key_ID,

Sys_Description

from TabA.qvd (qvd);

let end=now(1); trace $(end);

let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

TRACE time create mapping table qvd=$(diff);