SQL Server 2005 view & subquery performance issues -

i have following 2 tables:

person - approx 7000 rows

• perosnid - 9 characters
• persontype - char (one of 'f' / 'c' / 'm')

personstatuses - approx 90k rows (roughly 13 rows each person row)

• id - identity
• personid - 9 character
• statuscode - integer
• lastupdatedate - datetime

i'm using view return latest row personstatuses realting unique person row:

latestpersonstatuses

select      personid, statuscode         personstatuses ps1       lastupdatedate = (select max(lastupdatedate)                                      personstatuses ps2                                   ps2.personid = ps1.personid) 

the following query:

select distinct person.id person    person.id  in (select personid                latestpersonstatuses               statuscode = 12) , person.persontype='f' 

plan 1 http://i56.tinypic.com/n4tc9u.jpg

takes minute perfrom, timing-out, while following:

select distinct person.id person    person.id  in (select personid                       latestpersonstatuses                      statuscode = 12) 

performs instantly.

plan 2 http://i54.tinypic.com/5xjrbp.jpg

i can't figure out why addition of clause in 1st query:

person.persontype='f' 

cause such difference.

can please direct me?

it because query optimizer has decided since dealing column persontype directly, may faster use index on column (if had one), or if not direct table scan + filter. in comparison perceived/estimated cost of evaluating subquery , doing in-list comparison, without knowing metrics/statistics of list subquery.

even looking @ query, have guessed faster scan , filter on table column rather going down subquery, involves expansion of view.

you should able rewrite (for 2005+) view as

--view: latestpersonstatuses select personid, statuscode    (select *, rownum=row_number() on (                   partition personid order lastupdatedate desc)         personstatuses p) x rownum = 1 

maybe optimizer