Performance issue in join

Database
Teradata Employee

Performance issue in join

Hi,

 

I am trying to fix issue in on the query which is taking long time to run due to multiple access from the same table based on different joins. Multiple joins are required as different colums are selected. But due to use of two left joins on the same table the explain plan gives very long estimates and query runs for very long. Can any one explain the reason for this?.. Also is there any way i can use the same table for two left joins instead of calling it twice?.

 

ps. I have checked the stats/matching types of columns (that is ok) and have also tried defining SI's on joining column but it didn't help.

 

SEL
CAST(C.Comment_Txt AS VARCHAR(10000)) AS SCs
,CAST(C2.Comment_Txt AS VARCHAR(10000)) AS CSs

FROM
<DATABASE1>.V_EVT BASE
INNER JOIN
<DATABASE1>.V_SE_EVT SE
ON Base.Evt_Id = SE.Evt_Id
LEFT JOIN
<DATABASE1>.V_COMMENTS AS C
ON SE.Serv_S_Id = C.Comment_Id
AND C.End_Dt IS NULL
LEFT JOIN
<DATABASE1>.V_COMMENTS AS C2
ON SE.Ser_Cus_Id = C2.Comment_Id
AND C2.END_Dt IS NULL

 

  • performance
2 REPLIES
Enthusiast

Re: Performance issue in join

Any of these columns are not unique and not in primary index then perfoamnce will issue will occur. In the explain did you see any of the product joins

Teradata Employee

Re: Performance issue in join

I am guessing that V_EVT and V_SE_EVT both have Evt_ID as the PI; if not, they probably should.  On the other hand, I am not sure why you are using V_EVT in the first place, since you are not using it in the result set or any other join.  If there are rows in V_SE_EVT that are not in V_EVT, that may be a data integrity issue.

 

However most of the problem is probably in the two left joins.  The optimizer has to redistribute the join of the EVT tables twice, once on Serv_S_Id and again on Ser_Cus_Id.  This redistribution can take a long time if this is a large result set.

 

On the other hand, if the number of cases when End_Dt is Null is comparatively small, something like this might work better:

 

With C as (select Comment_Id, Comment_txt from <DATABASE1>.V_COMMENTS where C.End_Dt IS NULL)
SEL
Case when SE.Serv_S_Id = C.Comment_Id then 'S' else 'Cus' End
,CAST(C.Comment_Txt AS VARCHAR(10000)) AS SCs
FROM <DATABASE1>.V_EVT BASE
INNER JOIN <DATABASE1>.V_SE_EVT SE
ON Base.Evt_Id = SE.Evt_Id
LEFT JOIN
C
ON SE.Serv_S_Id = C.Comment_Id
OR SE.Ser_Cus_Id = C.Comment_Id

 

Try it and see how the explain looks.