Join between two fact tables

[#1]

Hi,

I have two fact tables with a common field: "LeadsUniqueNum"

The fact1 table contains M21 records and 20 columns

The fact2 table contains M10 records and 6 columns

I would like to perform a left join between the fact1 table and the fact2 table based on the common field, and add all the columns in the fact2 table to the fact1 table,

but the performance is very slow.

Is there a faster way?

Following for example is the fact2 table:

Lead_MobilePhone	Lead_MainPhone	Lead_Center	Lead_Category	Leads_Status	LeadsUniqueNum
7896558821	5878995254	aa	x	Active	123
788662055	8796558897	bb	y	Not active	456
48745885	8796552258	cc	x	Not active	789

Example of the script:

Fact1:
load LeadsUniqueNum,* FROM

(qvd);

LEFT join(Fact1)
Fact2:
LOAD LeadsUniqueNum,
Leads_Status,
Lead_Category,
Lead_Center,
Lead_MainPhone,
Lead_MobilePhone    
FROM (QVD);

Thanks in advance,

[sunny_talwar]

You can look into creating multiple mapping loads:

Don't join - use Applymap instead

[#3]

  I tried using multiple mapping loads but the performance is much more slower

[sunny_talwar]

Hmmm really, that is strange because I have found Mapping Loads to be faster then joins. Not sure what other options you might have.

[oknotsen]

I would try this:

Load both tables into memory first, so not doing the join directly while loading the QVD.

Next, do something like this:

LEFT join(Fact1)
Fact2temp:
LOAD *

RESIDENT Fact2

;

drop table Fact2;

Might need a "noconcatenate" to work.

[#6]

One minute savings from three and a half minute loading

Thx Onno!

[muthukumar77]

Hi,

You can ApplyMap or Don't do left join (I mean Self join).

Note: If your going to use Apply map, Fact1 table should be Mapping table.