All Forums Database
raghavan.tce 3 posts Joined 04/15
13 Jun 2016
Performance on having more than 30 Left outer join

Hi Team,
In one of my project, I have one stage table with 3500 columns and i need to do left outer join with a lookup table to find matching business description. This lookup is required atleast for 500 columns..
For ex:
Stage_table :  custID , attr1, attr2, attr3, attr4........ attr3500 ( record count: 10million)
                        12345, 1,10,20,2.....
                        98985, 2,5,10,1.....
Lookup_Table : attr_id, source_value,target_definition ( record count: 10K)
                          attr1,1,Simple
                          attr1,2,Medium
                          attr2,5,Low
                          attr2,10,Risk.....
Requirement is to lookup source values from lookup_table and populate target table. Target table is current kept as 200-300column of 15aux tables.
 
a. As the lookup is required for atleast 500 columns, even if i split equally the no. of lookup columns into 15aux tables, there will min 30 per SQL BTEQ. Is it fine to have 30 left outer joins on the same Lookup table instance (with different logical names) ?
b. What if i need to run multiple jobs in parallel, which in case, the same lookup table will be referenced in more than 100 times assuming 5 tables run in parallel ? will this have any impact on performance ?
c. Is there any better approach for this ?
 
Cheers,
Raghav
 
 

yuvaevergreen 93 posts Joined 07/09
14 Jun 2016

volume of the lookup tables?...

You must sign in to leave a comment.