Optimize this query - SQL Server Forums

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

Optimize this query

Author

Topic

zemantm
Starting Member

8 Posts

Posted - 2009-02-10 : 12:23:18

Someone helped me on thsi forum last week with the query below. However, after running against our prod database I realized how slow it was. Can anyone see ways to optimize?

select i.batch_id,s.*
from search_data s
join (select i1.batch_id,i1.initiator_id
from initiator i1
inner join (select min(initiator_id) as minid,batch_id
from initiator
group by batch_id)i2
on i1.batch_id =i2.batch_id
and i1.initiator_id =i2.minid
)i
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt >= (to_date('2009/02/08:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'))
and b.create_dt <= (to_date('2009/02/08:12:59:59PM', 'yyyy/mm/dd:hh:mi:sspm'))

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-10 : 12:29:00

Try splitting it up so you can see what's slow - then you can optimise that and put it back together again (or just leave it)

make the indexes unique if they are

select min(initiator_id) as minid,batch_id
into #i2
from initiator
group by batch_id

create clustered index ix on #i2 (batch_id, minid)

select i1.batch_id,i1.initiator_id
into #i
from initiator i1
inner join #i2 i2
on i1.batch_id =i2.batch_id
and i1.initiator_id =i2.minid

create clustered index ix on #i (initiator_id)

select i.batch_id,s.*
from search_data s
join #i i
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt >= (to_date('2009/02/08:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'))
and b.create_dt <= (to_date('2009/02/08:12:59:59PM', 'yyyy/mm/dd:hh:mi:sspm'))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources