Help rewrite Select Statement Long running query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help rewrite Select Statement Long running query

Author

Topic

khy2shy
Starting Member

1 Post

Posted - 2015-03-19 : 12:15:54

This query is running more than 3 minutes to return 1/2 mil records. When execution plan is turned on, all indexes are using seeks. Is there a better way to rewrite this for faster performance?

select ViewLevel = 'Delete'
, [LName] = d.Name
, CKey = rel.CKey
, ro.RKey
, ro.LName as ROffice
, mh.Name as Manager
, d.DelID
from dbo.viewRelation ro
LEFT join ( select ParCKey as RKey, CCKey as CKey FROM dbo.tblPrimeName ) as rel
on rel.RKey = ro.RKey
inner join dbo.viewMgtName mh on ro.LName = mh.SubName and mh.SubName <> mh.Name
inner join dbo.DupName d on d.OLName = mh.Name
and (d.ExpiresOnDate is null or d.ExpiresOnDate > getdate())
and d.statusMH = 1
where d.Name = 'mking'

Thanks
Khy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-19 : 12:20:53

quote:

return 1/2 mil records

It's returning too many rows. Filter the data as you can't possibly need that many rows returned in one batch. Narrow it down to say 10,000 or less rows.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 12:52:02

I agree with Tara, it probably takes 3 minutes to send the 1/3 mil records down the wire ... and perhaps the query itself just takes milliseconds ...

However, is there some reason to use this subquery:


LEFT join ( select ParCKey as RKey, CCKey as CKey FROM dbo.tblPrimeName ) as rel

rather than just LEFT JOIN dbo.tblPrimeName as rel ?

I don't know if it will make a difference to performance, but it might.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 02:42:51

quote:
Originally posted by Kristen

I agree with Tara, it probably takes 3 minutes to send the 1/3 mil records down the wire ... and perhaps the query itself just takes milliseconds ...

However, is there some reason to use this subquery:

LEFT join ( select ParCKey as RKey, CCKey as CKey FROM dbo.tblPrimeName ) as rel

rather than just LEFT JOIN dbo.tblPrimeName as rel ?

I don't know if it will make a difference to performance, but it might.

The subquery is not needed until DISTINCT is used

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources