Baffling Query Return Times

Simple version first:

The basic question is:

what would cause? this query takes minutes or times out:

select xx,xx,xx,xx
from view_xxx
where field in
(

select distinct xxx from table inner join table.....
)


the inner query
select distinct xxx from table inner join table..... takes less than a second

then if I take the results of that query and do this:

select xx,xx,xx,xx
from view_xxx
where field in
(

52193,23523,25233... and so on
)

this later query takes 2 seconds... What is the difference? For more details see the detailed version.






-Robert


now a more details version:



On a SQL2000 machine...

What would cause this query to take forever, or even timeout:


Select distinct VTR.Control_ID from View_ToolResults VTR

Where VTR.Control_ID in

(

------ NOTE INNER QUERY TAKES LESS THAN 1 SECOND
SELECT ORD.CONTROL_ID

FROM Orders ORD

INNER JOIN Providers PR ON PR.SUBJECT_ID = ORD.SUBJECT_ID and PR.SV_OFFICE_ID = ORD.SV_OFFICE_ID and PR.Source_ID = ORD.Source_ID

INNER JOIN VisitTypes VT ON ORD.VISIT_TYPE_ID = VT.VISIT_TYPE_ID and ORD.Source_ID = VT.Source_ID

INNER JOIN HealthPlans HP ON HP.MARKET_ID = ORD.MARKET_ID and HP.Source_ID = ORD.Source_ID

INNER JOIN HPGroups HPG ON HP.MARKET_ID = HPG.MARKET_ID AND HP.SOURCE_ID = HPG.SOURCE_ID WHERE (ORD.Status is not null AND ORD.Status <>'E')

AND HPG.GR_ID in

(

SELECT GR_ID from UserHPGroups Where User_ID= 7

)

AND ORD.RELEASED_DATE between '01/01/2001' and '02/11/2008'

and ORD.RECEIVED_DATE between '07/01/2007' and

'12/31/2007' and ORD.MARKET_ID= 2699431


)

as mentioned above the inner query takes less than 1 second. So if I take the results from that query and do a select like this:


Select distinct VTR.Control_ID from View_ToolResults VTR

Where VTR.Control_ID in
(55836
,55631
,55331
,50992
,51101
,51196
,51500
,52269
,52366
,52384
,52429
,52518
,53117
,53993
,53994
,53995
,54027
,54128
,54192
,54264
,54291
,54292
,54324
,54379
,54578
,54706
,54917
,52622
,54354
,55965
,55410
,55461)

It takes 2 seconds.....

WHAT!!!!!>???!?!?! i don't understand... what is the difference?

The field in all cases is declared as Numeric(10,0)... I've tried to convert to int. I've tried to do temp tables... it doesn't help.