performance - Unexpected estimated rows in query execution plan (Sql Server 2000) -

if run query

select user largetable largetable.user = 1155  

(note i'm querying user reduce simplest case)

and @ execution plan, index seek planned [largetable has index on user], , estimated rows correct 29.

but if do

select user largetable largetable.user = (select user users externalid = 100) 

[with result of sub query being single value 1155 above when hard code it]

the query optimizer estimates 117,000 rows in result. there 6,000,000 rows in largetable, 1700 rows in users. when run query of course correct 29 rows despite huge estimated rows.

i have updated stats fullscan on both tables on relevent indexes, , when @ stats, appear correct.

of note, given user, there no more 3,000 rows in largetable.

so, why estimated execution plan show such large number of estimated rows? shouldn't optimizer know, based on stats, it's looking result has 29 corresponding rows, or maximum of 3,000 rows if doesn't know user selected subquery? why huge estimate? problem is, large estimate influencing join in larger query scan instead of seek. if run larger query subquery, takes 1min 40 secs. if run 1155 hard coded takes 2 seconds. unusual me...

thanks,

chris

the optimizer best can, statistics , row count estimations go far (as you're seeing).

i'm assuming more complex query can't rewritten join without subquery. if can be, should attempt first.

failing that, it's time use additional knowledge nature of data out optimizer hints. @ forceseek option in index hints. note can bad if data changes later, aware.