Modifying or Increase efficiency the Following 'RIGHT OUTER JOIN' query into inner join?

I'm trying to increase the efficiency in some of my queries. Due to the limitation of my knowledge I have always been using Right outer join to bring in a full dataset from multiple tables then culling them down through the where statement. However as I would like to add 'clustered index' to the follow query that is feeding one of my Schema binding view, I'm just wondering if there is a way to turn the following Right outer joins into 'inner join' instead so that the index can be created to decrease load time.

As I've been banging my head on this block for the last 2-3 days, any suggestions will be greatly appreciated!!

  • Table1 has Client information
  • Table 2 is a Junction table consisting composite key from Table1 and Table3
  • Table3 has Account information

FROM dbo.Table1 
RIGHT OUTER JOIN dbo.Table2 
    ON dbo.Table1.[Client ID] = dbo.Table2.[Client ID] 
LEFT OUTER JOIN dbo.Table4 
    ON dbo.Table2.[Account Officer] = dbo.Table4.FullName 
RIGHT OUTER JOIN dbo.Table3 
    ON dbo.Table2.[Account ID] = dbo.Table3.[Account ID]
WHERE (dbo.Table2.[Client Type] LIKE '%Customer 1%') 
AND (dbo.Table3.ServiceDescription NOT LIKE '%Nil%') 
AND (dbo.Table2.[Indicative outcome] IS NULL) 
AND (dbo.Table2.[Finalised outcome] IS NULL) 
AND (dbo.Table2.Outcome IS NULL) 
AND (dbo.Table2.ReviewOutcome IS NULL) 
AND (dbo.Table3.[Account Type] = 15) 
AND (dbo.Table3.AccountReviewComplete = 1)

I have tried to rearrange it so that some of the where criteria goes into the join as I've seen on some of the articles I found (which would apparently limit the data size at the start), which is said to increase efficiency but after reviewing the 'Live Query Statistics' for before and after, there doesn't seem to be that much of a difference. Example below:


FROM dbo.Table1 
RIGHT OUTER JOIN dbo.Table2 
    ON dbo.Table1.[Client ID] = dbo.Table2.[Client ID] 
LEFT OUTER JOIN dbo.Table4 
    ON dbo.Table2.[Account Officer] = dbo.Table4.FullName 
RIGHT OUTER JOIN dbo.Table3 
    ON dbo.Table2.[Account ID] = dbo.Table3.[Account ID] 
    AND (dbo.Table3.[Account Type] = 15) 
    AND (dbo.Table3.AccountReviewComplete = 1)

WHERE (dbo.Table2.[Client Type] LIKE '%Customer 1%') 
AND (dbo.Table3.ServiceDescription NOT LIKE '%Nil%') 
AND (dbo.Table2.[Indicative outcome] IS NULL) 
AND (dbo.Table2.[Finalised outcome] IS NULL) 
AND (dbo.Table2.Outcome IS NULL) 
AND (dbo.Table2.ReviewOutcome IS NULL)

1 answer

  • answered 2018-11-08 08:32 Shnugo

    You do not provide enough information, but I think this might help. Try it out:

    SELECT * 
    FROM dbo.Table1 cd --ClientData
    INNER JOIN dbo.Table2 jd --Junction Data
        ON cd.[Client ID] = jd.[Client ID] 
    INNER JOIN dbo.Table3 ai --AccountInformation
        ON jd.[Account ID] = ai.[Account ID]
    LEFT JOIN dbo.Table4 ao --AccountOfficer 
        ON jd.[Account Officer] = ao.FullName 
    WHERE (jd.[Client Type] LIKE '%Customer 1%') 
    AND (ai.ServiceDescription NOT LIKE '%Nil%') 
    AND (jd.[Indicative outcome] IS NULL) 
    AND (jd.[Finalised outcome] IS NULL) 
    AND (jd.Outcome IS NULL) 
    AND (jd.ReviewOutcome IS NULL) 
    AND (ai.[Account Type] = 15) 
    AND (ai.AccountReviewComplete = 1)
    

    Some hints:

    • Use table aliases. This makes it much easier to read and understand
    • the inner join will return only rows with matching linked rows.
    • I used a LEFT JOIN for the officer, because the linkage on its name might be erronous (typos!) and you could miss rows otherwise.
    • The usage of LIKE with a leading % will not allow for indexes and make your query slow. If there is any chance you should replace Client Type and ServiceDescription with data in determinated sets (use catalog-tables if possible)