Multiple LEFT JOINS to the same table

Question

Multiple LEFT JOINS to the same table

suresh.radhakrishnan

SSC Veteran

Points: 283
More actions
March 6, 2008 at 11:19 am

#182982

Hi
I came across the following query in a 3rd party application that left joins the same table tscheduleudfdata (1.5 million rows) 6 times in the same query. I am new to SQL Server and was wondering if this will cause some performance issues and is there a better way to represent this.
The query is as follows:
select distinct s.scheduleid
, upper(left(isnull(us.value, 'ZISO'), 12)), 'H', 'I',
, upper(left(isnull(um.value, 'BAD_DATA'), 18)),
upper(case when up.value = 'SUPP' then 'SUPP'
when up.value = 'SPIN' then 'CSPN'
when up.value = 'NSPN' then 'CNSPN'
when up.value = 'UCI' then 'UCI'
when isnull(ue.value, 'ENGY') = 'ENGY' then 'FIRM'
when ue.Value = 'Dynamic' then 'DYN'
else left(ue.Value, 5)
end)
, upper(left(uc.value, 12))
, left(case when left(ft.flowtypedesc, 1) = 'I' then s.sourceentity
else s.sinkentity end, 6)
, left(case when left(ft.flowtypedesc, 1) = 'I' then s.sourceentity
else null end, 6)
, left(case when left(ft.flowtypedesc, 1) = 'I' then null
else s.sinkentity end, 6)
, round(sp.mwh, 0)
, null
, -1
, sp.profiledate
, 'CAS'
from #scheds t
join tschedule s WITH (NOLOCK) on t.scheduleid = s.scheduleid # 313,172 rows
join tscheduleintegratedprofile sp WITH (NOLOCK) # 3,717,758 rows
on s.scheduleid = sp.scheduleid
join tflowtype ft WITH (NOLOCK) on s.flowtypeid = ft.flowtypeid
left join tscheduleudfdata us WITH (NOLOCK) on s.scheduleid = us.scheduleid
and us.udfid = @u_scid
left join tscheduleudfdata up WITH (NOLOCK) on s.scheduleid = up.scheduleid
and up.udfid = @u_product
left join tscheduleudfdata ut WITH (NOLOCK) on s.scheduleid = ut.scheduleid
and ut.udfid = @u_tiepoint
left join tscheduleudfdata um WITH (NOLOCK) on s.scheduleid = um.scheduleid
and um.udfid = @u_mres
left join tscheduleudfdata ue WITH (NOLOCK) on s.scheduleid = ue.scheduleid
and ue.udfid = @u_energy
left join tscheduleudfdata uc WITH (NOLOCK) on s.scheduleid = uc.scheduleid
and uc.udfid = @u_etc
where sp.profiledate >= @startdate
and sp.profiledate < @enddate
and s.status in ('A', 'P')
and isnull(@tiepoint, ut.value) = ut.value
Thanks
Suresh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

My knee-jerk reaction is usually to just look at the query plan; that is also what I would suggest in this case: Look at the query plan.

suresh.radhakrishnan SSC Veteran Points: 283 More actions · Answer 2

Since once of the table seems to be a memory table, I do not know how to capture the query plan for the same. Is there a trace that I can set that will generate the query plans for this process ?

Thanks

Suresh

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

You can run the query and capture the actual execution plan. There's a button on the default tool bar, look for the tool tip that says "Include Actual Execution Plan" or click on the Query menu and select "Include Actual Execution Plan" or the quick key is Ctrl+M.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

suresh.radhakrishnan SSC Veteran Points: 283 More actions · Answer 4

Thanks for the information. Let me try that. This code is embedded in a procedure. I guess I can do the same while executing the procedure also.

Suresh