Concatenation in INNER Join -Performance Issue...

Database
l_k
Enthusiast

Concatenation in INNER Join -Performance Issue...

Hi all,
The below query is taking 3 minutes to produce the result...The concatenation in the INNER JOIN condition is taking a long time and B0500 table is having 200 GB of data.. and S045021 is having 1gb of data inside..

SELECT

B0500.EDW_KEY As Event_Id

,S045021.M_NB_TRN||'_'||S045021.M_NB||'_'||S045021.M_REPDATE||'_'||S045021.M_ACCOUNT As Transaction_Id

FROM

DP_SEDW.S045041_MX_LBV_ACJD_REP AS S045021

INNER JOIN DP_TEDW.T0500_KEY_EVENT B0500

ON S045021.M_NB_TRN||'_'||S045021.M_NB||'_'||S045021.M_REPDATE||'_'||S045021.M_ACCOUNT=B0500.SOURCE_KEY

Any other alternative option to re-write the above code instead of directly using concatenation in the JOIN condition or best approach is highly appreciated..

Thanks
5 REPLIES
Enthusiast

Re: Concatenation in INNER Join -Performance Issue...

generally a design issue. when functions (including string concatenation) is used optimizer is not able to use statistics on table to generate plan. for your case here, there should be no difference anyway... try alter table a add colulmn jk varchar(xxx) ; update a set jk = c1||c2||c3; collect stats on jk then try join. .I doubt there will be any difference.
l_k
Enthusiast

Re: Concatenation in INNER Join -Performance Issue...

Currently,It's difficult to change the structure of DDL...

Is there any other option instead of changin the DDL's structure?

Thanks.
Enthusiast

Re: Concatenation in INNER Join -Performance Issue...

It depends on how much flexibility you have in what can happen in the code. One option, if you can issue multiple statements including DDL with this query, is to create a volatile table and insert the primary key of the S045021 table along with a column that includes the concatenated value. You could then introduce this table into the join so you're joining to the S045021 table on the PK and you're no longer joining to B0500 on the concatenated field. There will be some additional overhead to insert into the temporary table, but that should be minimal for a 1GB source table.

Make sense?
l_k
Enthusiast

Re: Concatenation in INNER Join -Performance Issue...

Yeah..Temporary table is a good idea to resolve this..
But i am taking EDW_KEY column from B0500 table.So,i need to join with B0500 table also right?

or if we do this concatenation in Mload,will it impact any performance issue in dumping the data into the staging table? is it not a good practice?

Thanks.
l_k
Enthusiast

Re: Concatenation in INNER Join -Performance Issue...

Can anyone help me on this?