SQL Server Performance

simple performance question

Discussion in 'T-SQL Performance Tuning for Developers' started by cmoy, Jun 4, 2004.

  1. cmoy New Member

    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!
  2. Raulie New Member

    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

Share This Page