Multiple joins in a query

Question

I have this SP with more than 10tables involved. In the underlined lines, there is a table AllData which is being joined 3times because of the fieldname in the where clause.

Any suggestions on how to handle this complex query better will be greatly appreciated. Mostly, to avoid the multiple times I am joining AllData(with alias names ad1, adl2, adl3). This could affect the performance.

Here is the sp

ALTER PROCEDURE [dbo].[StoredProc1]
AS
select  case when pd.Show_Photo = '1,1,1'
             then i.id
             else null
        end as thumbimage,
        t1.FPId,
        'WebProfile' as profiletype,
        mmbp.Name as Name,
        t1.Age,
        t1.Height,
        adl.ListValue as AlldataValue1,
        adl2.ListValue as AlldataValue2,
        adl3.ListValue as AlldataValue3,
        c.CName,
        ed.ELevel,
        ed.EDeg,
        NEWID()
from Table2  mmbp, Table3  u
join Table1 t1 on t1.Pid = u.Pid
left join Table4 mmb on t1.Pid= mmb.Pid 
join table5 i on t1.Pid = i.Pid 
join table6 pd on t1.Pid = pd.Pid
join table7 ed on t1.Pid = ed.Pid
join table8 c on t1.xxx= c.xxx
join AllData  adl on t1.xxx = adl.ListKey 
join AllData adl2 on b.ms = adl2.ListKey
join AllData adl3 on b.Diet = adl3.ListKey
where adl.FieldName=xxx and
      adl2.FieldName='ms' and
      adl3.FieldName='Diet' and 
      ------ 

Answer 1

I note that you appear to have a cartesian join between Table2 and Table3 - unless one of these tables is very small, this is likely to drastically affect performance. I suggest explicitly joining Table2 to one of the other tables in the query, to improve performance.