Diff b/w Left Join and Apply Map?

[#1]

Hi,

Any one can i explain with Left Join and Apply Map?

which is better performance in qv while creating data model?

Sub2u              

[its_anandrjs]

Hi,

If you use Mapping load then in Mapping table needs two fields at least for mapping weather as in joins need sigle or more common fields for joins.

Regards

Anand

[jagan]

Hi,

Left join will join two tables physically like we do in normal SQL, where as by using ApplyMap() we can create a new column in the table based on the key values.

The difference is when you want to include multiple columns into the table then go for join, if you want to include only one column then use applymap().

Join Scenario:

Table1:

Columns : C1, C2, C3

Table2:

Columns: C1, C4, C5, C6

Result: C1, C2, C3, C4, C5, C6

Table1:

Columns : C1, C2, C3

Table2:

Columns: C1, C4

Result: C1, C2, C3, C4

Regards,

Jagan.

[its_anandrjs]

Hi,

For more clear vision

1. Mapping Load

MapTab:

Mapping LOAD * Inline [

ID,Desc

1,Desc1

2,Desc2

3,Desc3

4,Desc4 ];

LOAD ApplyMap('MapTab',ID) as Desc ,* Inline [

ID,Sales,Location

1,48741,L1

2,12369,L2

3,14852,L3 ];

In this you get Desc column on the base of ID

2. With joins

MapTab:

LOAD * Inline

[

ID,Desc,Details

1,Desc1,some info

2,Desc2,other info

3,Desc3,related info

4,Desc4,info given

];

Left Join

LOAD * Inline

[

ID,Sales,Location

1,48741,L1

2,12369,L2

3,14852,L3

];

Then you get 5 fileds single table

Regards

Anand

[#5]

there is one another difference between join and applymap is in granularity,

hope above image make some clarity

[Colin-Albert]

I would suggest you avoid using join as far as possible and use applymap instead, even where several applymap are required. Joins may cause data to be inadvertently duplicated and can be slow during reloads.

See the blog below  by hic for more details.

Don't join - use Applymap instead

[#7]

Hi!

In single mapping, performance is almost the same for both methods. But if there are a lot of fields to map in one table, better are ApplyMap functions.

Other differences of methods, as mentioned Colin Albert, is that in "left join" both tables affected on record level - number of records of left table may grow and/or double records both tables may be withdraw. ApplyMap do not affects records at all.

[#8]

Thank you all.

Thanks for your comments.

ApplyMap and Lookup .. which is the better one to improve performance in data model?

Thanks in advance.

Sub2u

[hic]

Applymap() is much faster than Lookup().

HIC