SQL Server Performance Forum – Threads Archive

LEFT OUTER JOIN

Please help me to fine tune following SQL Statement SELECT [t1].[id],
[t1].[claim_id],
[stage_dscp] = CASE WHEN [t3].[is_other] = ‘Y’ THEN
[t3].[dscp] + ‘ – ‘ + isnull([t3].[stage_dscp], ”)
ELSE
[t3].[dscp]
END,
ISNULL([t1].[amt_claim],0)
FROM [t1] WITH (NOLOCK)
LEFT OUTER JOIN [t2] WITH (NOLOCK)
ON [t2].[id] = [t1].[schedule_id]
LEFT OUTER JOIN [t3] WITH (NOLOCK)
ON [t3].[cd] = [t2].[stage_cd] I observed the execution plan, Clustered Index Scan is for the tables[t1] and [t3]
for the table [t3], [cd] is the primary key, I observed that all tables has useful indexes. Can anyone help to rewrite the query?

I notice that you’re using ISNULL – is that because you cannot be certain to find a match on t2 and t3? If there is always a match on t2 and t3, then use an INNER JOIN. Anyhow, a clustered index scan is not too bad in itself. Generally speaking, the strategy selected by the SQL Server engine would depend on the number of rows in t2 and t3, and the statistics on the FK columns in t1. You might try adding hints like LEFT MERGE JOIN – see if that makes any difference. It might, or it might not.

The problem is that t2 is outer joined itself. Can you post the execution plan? I don’t have much experience with outer joining tables that are already outer joined so I’m curious to see execution plan choosed by QO.

Hi, I think is better readable in this way, SELECT [t1].[id], [t1].[claim_id], [stage_dscp] = CASE WHEN [t3].[is_other] = ‘Y’ THEN [t3].[dscp] + ‘ – ‘ + isnull([t3].[stage_dscp], ”) ELSE [t3].[dscp] END , ISNULL([t1].[amt_claim],0) FROM [t1] LEFT OUTER JOIN [t2] ON [t2].[id] = [t1].[schedule_id] LEFT OUTER JOIN [t3] ON [t3].[cd] = [t2].[stage_cd] <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> At first look, I dont see any problem in your query if all fields in the joins are indexed. With the information that you provide I cannot tell you so much <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> (I’d use COALESCE function instead of ISNULL but this is not related with ur joins) Could you give more information about index on three tables? (cluster index,, etc) and something about the execution plan.

]]>