SQL Server Performance Forum – Threads Archive

simple performance question

Hi all,
I am trying to rectify a query whcih runs once a second, and every time it runs, it pounts the CPU tremendously. I have a big wide transaction table (let’s pretend it is normalized) with a status id column, primary key is a surrogate key and a secondary clustered index is on name. Let’s say that the status IDs are 1-active 2-inactive 3-in progress 4-lost, and this is in a status table. There are 1 million + rows. Only about 5 rows or so are ever ‘in progress’. There is a nonclustered index on statusid in the transaction table and a clustered index on the status lookup table. When I issue this query (which runs every second): select name
from xtrans a
join status b
on a.statusid = b.statusid
where b.statusname = ‘in progress’ why does it do a clustered index scan (according to the execution plan) and still go through just about all 1 million rows? Even if I give an index hint on StatusID it still goes through 250K rows. When I issue two separate queries i.e. select @x = statusid
from status
where statusname = ‘in progress’ and then select name
from xtrans
where statusid = @x this runs much faster, and only goes through the 5 rows that I am looking for. Why is that, and is there a way I can get exactly what I want (just do a seek on the 5 rows) by using one query with the join instead of 2 queries? Thanks in advance for any answers!

---

Try this… SELECT statusname
from xtrans a
join status b
on a.statusid = b.statusid
where a.statusid= 3 if you dont need to pull the statusname column then don’t even join the two tables just query like this…. SELECT name
from xtrans
where statusid = 3 also update stats

---

]]>