Performance on having more than 30 Left outer join

Database

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

1 REPLY
Enthusiast

Re: Performance on having more than 30 Left outer join

volume of the lookup tables?...