CODEWITHSUNDEEP
sqlsql-serverjoinquery-optimization
user575219
user575219

Reputation: 2442

Multiple joins in a query

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 
      ------

Upvotes: 1

Views: 237

Answers (2)

user359040
user359040

Reputation:

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.

Upvotes: 1

Gary.S
Gary.S

Reputation: 7131

One thing you could try is moving the where conditions into the joins

join AllData ad1 on t1.xxx = ad1.ListKey AND ad1.FieldName = xxx
join AllData ad2 on b.ms = adl2.ListKey AND ad2.FieldName = 'ms'
join AllData ad3 on b.Diet = adl3.ListKey AND ad3.FieldName = 'Diet'

This would give better performance as the join size would be limited to only the records you want. To do this all in one join you could join AllData ad on (t1.xxx = ad.ListKey AND ad.FieldName = xxx) OR (b.ms = ad.ListKey AND ad.FieldName = 'ms').... The issue with this option is you no longer have distinct columns for ad1, ad2, etc.

Upvotes: 1

Related Questions